VBAの動的配列について

質問

いつもお世話になっております。
エクセルVBAを学習中の者です。
動的配列についてお伺いします。

添付資料を見て頂きたいのですが、
シート名1～4に同一レイアウトの表があります。
これらの表をを2次元配列に格納し、その後、同一レイアウトのシートに一括転記したいと考えています。
転記の事を考えて、条件としては、
シート1から2行目以降のデータを配列『data』に格納、変数『dataCnt』が転記先の行番号と同じになるように考えています。

当初は、配列の定義を『Dim data(100,3) As Variant』と、多めに要素数を定義して、コードを記述していました。
正直、凄く気持ちが悪い感じでした・・・
最近、動的配列を学習しまして、
シートごとにデータの行数を変数『lastRow』に格納して、配列を再定義して【データ数＝要素数】とならないか？
と思い、下記のようなコードを書いてみました。
が、『ReDim　Preserve～』で実行エラーが発生してしまいます。
原因がなぜかわかりません！
そもそも、動的配列はこのような使い方は出来ないのでしょうか？

Sub テスト()
Dim data() As Variant
Dim x As Long
Dim i As Long
Dim ii As Long
Dim lastRow As Long
Dim dataCnt As Long

dataCnt = 2

For x = 2 To 5
Worksheets(x).Activate
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
If x = 2 Then
ReDim data(2 To lastRow, 3)
Else
ReDim Preserve data(2 To dataCnt + lastRow - 1, 3)
End If

For i = 2 To lastRow
For ii = 1 To 3
data(dataCnt, ii) = Cells(i, ii)
Next ii
dataCnt = dataCnt + 1
Next i
Next x
End Sub

どなたかご指導をよろしくお願いいたします。

回答No.2

nishi6

基本的には動的配列の使い方（ReDim や Preserve）はあっています。

一つ残念なのは、要素数を変えることができるのは最後の変数です。

従って、変数をひっくり返します。

ReDim data(2 To lastRow, 3)
　　↓
ReDim data(3, 2 To lastRow)

ReDim Preserve data(2 To dataCnt + lastRow - 1, 3)
　　↓
ReDim Preserve data(3, 2 To dataCnt + lastRow - 1)

data(dataCnt, ii) = Cells(i, ii)
　　↓
data(ii-1, dataCnt) = Cells(i, ii)
インデックスはゼロからでしょう。

最後にシートに吐き出す時に、行列が列行の順になっているので、
　　Range(～:～) = Application.Transpose(data)
のようにする必要があるでしょう。

確認する環境がなく、机上デバッグです。スペルミス等あればご容赦を。
基本的にはあっています。動かす変数の位置の問題でしょう。

回答No.1

chie65536（@chie65535）

＞『ReDim　Preserve～』で実行エラーが発生してしまいます。
＞原因がなぜかわかりません！

原因は「仕様だから」です。

＞そもそも、動的配列はこのような使い方は出来ないのでしょうか？

仕様で「Reserveは静的配列にしか使えない。動的配列ではエラーになる」とハッキリと決まっています。

つまり「それは許されない」って事です。

お礼

早速の回答ありがとうございます。

良く分からないので教えていただきたいのですが、

「動的配列の既存の値を消さないで要素数を変更するには、ReDim命令にPreserveキーワードを付ける」

という事だと思うのですが・・・

仕様で「Reserveは静的配列にしか使えない。動的配列ではエラーになる」とハッキリと決まっています。

とはどういう事でしょうか？