こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

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

投稿日時 - 2014-07-30 16:36:18

QNo.8698162

困ってます

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

基本的には動的配列の使い方(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)
のようにする必要があるでしょう。


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

投稿日時 - 2014-07-30 18:14:23

ANo.1

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

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

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

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

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

投稿日時 - 2014-07-30 16:48:40

お礼

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

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

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

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

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

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

投稿日時 - 2014-07-30 17:16:07

あなたにオススメの質問