EXCEL VBA 配列のIndex番号書きかわり

質問

こんばんは。

EXCEL VBA　で困っております。

シートから配列にコピーさせると、ReDimで設定してあったIndex番号が
なぜか替ってしまうらしいのです。
いろいろと検索してみましたが、どうしても見つからず、ここで質問させていただくことにしました。

下記のコードで確認してみてください。

Sub test()

Dim A As Variant
ReDim A(1 To 1, 0 To 4)

For j = 0 To 4
A(1, j) = j
Next j

Range("A1:E1") = A

MsgBox A(1, 0) 'ちゃんと機能して(1, 0)の位置で[0]と表示
'MsgBox A(1, 5) '←インデックスが有効範囲にありません

A = Range("A1:E1")

Range("A2:E2") = A

'MsgBox A(1, 0) 'さっき機能したのに今度はエラー
MsgBox A(1, 1) '(1, 1)はセルでは２列目の[2]が取得されない
MsgBox A(1, 5) 'さっきエラーになった(1, 5)は今度はエラーにならない

End Sub

なぜ、A(1 to 1, 0 to 4) が A(1 to 1, 1 to 5) と替ってしまうのか？
シート範囲全体をいっぺんに配列に格納するとこういうことが起こるのが仕様なのか？？
自分の推測では、配列は[0]から始めることができますが、
シート上では[0]という開始位置を認識できないため、
Variant変数型のなかで自動的に書き換えちゃってくれてるのかな？
なんて思ったりしました。
理由がわかる方、おりましたらご教授くださいませ。

また、どうしても、この２次元配列で、
てっとり早いこのコピーコードを使いセル範囲から配列へコピーさせて、
列方向へのIndexを (0 to 4)とさせたいのですが、
何かよい方法はありますでしょうか？？
For文やEach文でひとつずつセルから配列へ格納という方法もあるかもしれませんが、
実際に打ち込んでいるコードは、行列ともかなり範囲が広いために、
処理時間を気にしてしまいます。

そこそこ使い慣れてきたのに、よもやこんなところで、躓くの！？といった心境です。
ご指南のほど、どうぞよろしくお願いいたします。

回答No.5

ap_2

>この (1 to 5) を再び (0 to 4) へ復活
残念ながら、VBAは配列操作がしょぼいのでForループするしか。
なるべく、セルと直結した配列は(1 to n)で統一したいです。

◆処理速度＠Forループに向けて
配列はアクセスが早いから誤差！…らしいよ。試した↓

　'処理1: 10000×100のセル範囲取得＠セルには英字100字
　a1 = Range("A1:CV10000")　　'->　約1500ms
　'処理2: Forループで配列複製
　a2(r, c) = a1(r + 1, c + 1)　　　'->　約450ms

450ms！30%増？思ったよりかかったΣ(゜□゜；)
…けど、ちょっと処理を変えるだけで大きく変わるんです。

　処理1'　1×100のセル取得、を10000回　-> 250ms
　処理2'　1×100の配列複製、を10000回　-> 100ms

…なんででしょうね(苦笑)
これなら、高速化は、他の処理を見直した方が大きいかも。
特に、後者（2'）の使い方なら、100msは許容範囲では？
お試しあれ

◆補足説明
◇配列について
A = Rangeでは、以下と同じことが起きてます。
　ReDim a1(0 to 10): ReDim a2(1 to 20)
　a1 = a2　'a1はa2の複製、つまり(1 to 20)になる

ちなみに、Array関数などは必ず(0 to n)の配列を返します。
"Option Base 1"なんて設定しても、ソレは変わりません。
セル絡みは1から、他は0からで慣れてクダサイ。

◇Rangeについて
Rangeはオブジェクトなので、仕様ですね。
・Range.Valueの返却値は、範囲の場合、(1 to n)の配列です。
・Range.Valueへの代入は、範囲の場合…こんな感じ↓
　　ReDim A(101 to 103)
　　Range("A1:C1") = A　'A1セルにはA(101)が入る

お礼

みなさま、ご回答ありがとうございました。

プログラム制作の最初の段階で、配列範囲を0から始める構造にしてしまったため、なんとかIndexの設定を維持したかったのですが、
仕様ならしょうがありませんでした…。
打開策として、大きな範囲を配列に一変に格納したあと、別の配列にFor文で、データを一個一個、再配置してみました。
シートとのやり取りではなく、配列同士のやり取りなので、そんなに時間がかからないことがわかりました。

ありがとうございました。

回答No.4

Musaffah

LBOUND(A)やUBOUND(A)とかを使えばどうでしょう？
これを使えば、０始まりとか１始まりとかを気にしないでコーディングできますが・・・。

これだと都合悪いですか？

回答No.3

MARU4812

そもそも、単純な Range 指定は Excel.Range オブジェクトを取得します。
　Dim rng As Excel.Range
　Set rng = Range("A1:E1")
　Debug.Print rng.Address
VB が勝手にデフォルトプロパティを利用して配列に変換してるようですが、
そもそも、そこに省略されている処理があるので、プログラムとして
あまり良くないと思います。

配列で処理したいなら、Value プロパティなどで取得・設定します。
　Range("A1:E1").Value = A

上記プロパティを利用するとプロパティ内部で「セルの範囲に応じた
新しい配列」を作成して返すので、別の配列になります。
無視されたというより、入れ物自体が違います。新しく作られます。

そして、Excel では配列の要素は 1 始まりになります。これは仕様です。
配列自体は、VBA 固有のものではなく、VB の文法に応じて利用できるので
プログラムコードのみの利用では 0 の要素も使えますが、Excel の
セルを操作した時点で Excel の都合の良い形に解釈されます。

　Range("A1:E1").Value = A
設定する時は A が
　ReDim A(1 To 1, 0 To 4)　であっても
　ReDim A(1 To 1, 1 To 5)　であっても
指定した Range 範囲の先頭からデータが入ってしまいます。
　ReDim A(1 To 1, 0 To 4)
　For j = 0 To 4
　　A(1, j) = j
　Next j
　ReDim Preserve A(1 To 1, 0 To 4)
　Range("A1:E1").Value = A
　ReDim Preserve A(1 To 1, 1 To 5)
　Range("A2:E1").Value = A

バグのもとですので、配列の Index の下限は 1 で統一する事を
お勧めします。

回答No.2

imogasi

私は習得出来てないため、いつも色々説明をＮＥＴなどで読んでも、何か歯切れが悪いですが
http://officetanaka.net/excel/vba/variable/09.htm
などをじっくり読んで見てください。
とくに＞たとえ事前に要素数がわかっていても、次のように要素数が固定されている配列に代入することはできません。のあたり。
なぜか知りたいが、解説本には記事は無く、ＷＥＢでも出あわさない。
別の記事に＞配列を複写するときは、あくまで「値渡し」をしているということです。「参照渡し」はしていないので注意が必要です。あるが、
何か関係があるのかな。
ーー
参考になるかどうか
Sub test03()
Dim MyStr1 As Variant
MyStr1 = Range("A1:e1")
Range("A5:E5") = MyStr1
MsgBox IsArray(MyStr1)
End Sub
はＴＲＵＥ、Ａ１だけだとＦａｌｓｅ
ーー
Sub test02()
Dim A As Variant
A = Range("A1:E1")
Range("A4:E4") = A
'以上はＯＫ
A = Array(Range("A1"), Range("B1"), Range("C1"), Range("E1"))
For i = 0 To UBound(A)
MsgBox A(i)
Next
End Sub
こうするとＯＫ

回答No.1

nag0720

＞なぜ、A(1 to 1, 0 to 4) が A(1 to 1, 1 to 5) と替ってしまうのか？

A = Range("A1:E1")
としているからです。
これは、配列要素への代入ではなく、配列変数への代入です。
配列変数へ代入すると、その前の定義していたReDimは無視され、新しく配列の範囲が定義されます。

新しい配列は、行の範囲は1からRangeの行数、列の範囲は1からRangeの列数になります。

A = Range("A1:E1")
と代入しても、
A = Range("C5:G5")
と代入しても、配列の範囲は同じです。

また、
A = Range("C5:G7")
とすると、Aは(1 To 3, 1 To 5)の配列になります。

お礼

さっそくのご回答ありがとうございます。

ReDimが無視されてしまうんですね。。。

なんとなく、シート間と配列間では、そういったわだかまりみたいなものに、
いままでも、何度となく振り回されてきたような気がしますｗ。

これは仕様みたいなものですよね？
それであれば、(0 to 4) から (1 to 5)への変更は致し方ないとして、
この (1 to 5) を再び (0 to 4) へ復活させることは可能なのでしょうか？

シート範囲と配列領域のやり取りで、For文やEach文を使うのは、懸念してしまうのですが、配列領域と配列領域でのFor文のやり取りなら、まだ我慢できそうです。

やはり、新しく配列を用意して、新しい配列にデータ転送する他には、
うまい方法はないのでしょうか？？

みなさまの知恵をお借りできたら幸いです。
どうぞ、よろしくお願いいたします。