ＥＸＣＥＬ ＶＢＡ 配列データの部分コピー

質問

EXCEL　VBAで配列データの一部分のみ、シートへコピーする方法を教えて下さい。
なるべく処理時間が短い方法がいいです。

（例）
Dim varTable() as variant
varTable(0,0)="AAA"
varTable(0,1)="BBB"
varTable(1,0)="CCC"
varTable(1,1)="DDD"

varTable(0,0)とvarTable(0,1)だけをEXCELのシートへコピーする。

実際には、7データ×400レコードの配列のうち、100レコードずつを4シートに分けてコピーしようとしています。

よろしくお願いします。

回答No.3

KenKen_SP

> Index()では、データ数の制限等があるのでしょうか。

Excel2000 以前では Index 関数は要素数が 5461 個までの配列にしか
使えません。Excel2002 以降ではこの制限が改善されてますが。

　# こういうこともあるのでバージョンは明記して下さい

Excel2000 をお使いなら、For ループで回して下さい。シートへの出力
に配列が使える場合なら、20データ×10000 程度でも瞬時です。

Sub Sample()

　　Dim buf(1 To 10000, 1 To 20) As Variant
　　Dim i　 As Long
　　Dim j　 As Long

　　' // 2次元配列サンプルデータを作成
　　For i = 1 To 10000
　　　　For j = 1 To 20
　　　　　　buf(i, j) = "R" & CStr(i) & "C" & CStr(j)
　　　　Next j
　　Next i
　　MsgBox "サンプルデータをセット。配列から5400,6000,8000番目の" & _
　　　　　 "レコードを取り出します"

　　' // 配列から5400、6000、8000レコード目を取り出す
　　' // 取り出すデータ数は既知なのだから、いちいち Preserve
　　' // しないで、配列要素数を決め打ちした配列にコピーしていく

　　Dim cnt As Long
　　Dim v
　　Dim ret(1 To 3, 1 To 20) ' // 3レコード*20フィールド

　　cnt = 1
　　For Each v In Array(5400, 6000, 8000)
　　　　For i = 1 To 20
　　　　　　ret(cnt, i) = buf(v, i)
　　　　Next
　　　　cnt = cnt + 1
　　Next

　　' // セル出力
　　Range("A1").Resize(3, 20).Value = ret

End Sub

または、自由自在に配列からデータを取り出したいなら、ADO などでデータ
ベースに接続し、sql を使う方法も検討して下さい。

回答No.2

KenKen_SP

補足。

#1 は横（列方向）にセルに書き込みました。縦方向なら、

Range("A1").Resize(UBound(varReturn)).Value = _
Application.Transpose(varReturn)

です。というわけで、TRANSPOSE も押さえて下さい。

お礼

ご回答ありがとうございます。
Application.Index()を使用し、部分コピーをすることが出来るようになりました。

しかし、配列のデータ数を増やすと
　varReturn=Application.Index(varTable,1)
の部分で、『型が一致しません。』とエラーが発生することがわかりました。

具体的には、7データ×400レコードでは問題ないのですが、20データ×1000レコードに増やすとエラー発生します。
配列データの違いとしては数が多くなっただけで、データ型等は同じです。

Index()では、データ数の制限等があるのでしょうか。

回答No.1

KenKen_SP

こんばんは。

ワークシート関数の INDEX を使うとか。

Sub SampleProc()

　　Dim varTable(1, 1) As Variant
　　Dim varReturn　　　As Variant

　　varTable(0, 0) = "AAA"
　　varTable(0, 1) = "BBB"
　　varTable(1, 0) = "CCC"
　　varTable(1, 1) = "DDD"

　　varReturn = Application.Index(varTable, 1)
　　Range("A1").Resize(, UBound(varReturn)).Value = varReturn

End Sub

INDEX に与えるパラメータによってワークシートで言うところの行、列
どのようにも切り分けられます。
でも、7×400 程度の配列なら For ループで回しても瞬時だと思いますよ。

あとは、Application.Transpose なんかも押さえておくと良いでしょう。