【VBA】多次元配列の落とし穴

VBAの多次元配列は2種類ある？

ExcelVBAを書いてたら避けては通れない配列。
セルへの出力を高速化するにも、配列を使うのはメジャーな手段です。
複数行×1列、または1行×複数列であれば1次元配列でもセルへの出力が可能ですが、複数行×複数列となると1元配列ではデータが保持できません。
そんな時に出てくるのが多次元配列。
元々Java屋なので、多次元配列＝配列の入れ子として理解していたのですが、VBAではそうではないパターンがあったので、まとめておきます。

配列操作全般に関しては、この辺にもまとめてあります。ArrayUtils的に使える関数群です。
→**【VBA】使い回せる配列操作用関数**

サンプルコード

CSVファイルを読み込んで、ワークシートに出力します。
CSVの各行のデータに対して出力対象かどうか判定し、対象である行だけを出力することにします。
今回は、ヘッダ行とCSVの1項目目が「1」である行のみを出力します。

Option Explicit

Sub LoadCsv()
    ' ファイル指定
    Call ChDir(ThisWorkbook.path)
    Dim path As String: path = Application.GetOpenFilename(FileFilter:="CSV ファイル,*.csv")
    If path = "False" Then
        Exit Sub
    End If

    ' CSV全行一括読み込み
    With CreateObject("Scripting.FileSystemObject").GetFile(path).OpenAsTextStream
        Dim csvData As String: csvData = .ReadAll
        .Close
    End With

    ' 出力条件判定
    Dim result As Variant: result = Empty
    Dim cnt As Long: cnt = 0
    Dim csvRow As Variant: For Each csvRow In Split(csvData, vbCrLf)
        cnt = cnt + 1
        Dim rowData As Variant: rowData = Split(Replace(csvRow, """", ""), ",")
        If UBound(rowData) = 1 Then
            ' 改行のみの行で終了
            Exit For
        End If

        If cnt = 1 Then
            ' ヘッダ行は必ず出力
            ReDim result(0)
            result(UBound(result)) = rowData
        Else
            If rowData(0) = "1" Then
                ' 抽出条件一致
                ReDim Preserve result(UBound(result) + 1)
                result(UBound(result)) = rowData
            End If
        End If
    Next

    ' アクティブシートに出力
    With ActiveSheet
        Call .Cells.Clear
        .Cells.NumberFormatLocal = "@"
        ' セルに代入
        .Range(Range("A1"), Cells(UBound(result) + 1, UBound(result(0)) + 1)).Value = result
        Call .Range("A1").Select
    End With
End Sub

サンプルデータ(CSVファイル)

ダブルクオート括りの普通のCSV。ファイル終端の改行はあってもなくても大丈夫。
1項目目が「1」の行だけ出力するので、ヘッダ行と合わせて3行×5列出力される想定です。

"Flag",Item1","Item2","Item3","Item4"
"1",111","aaa","222","bbb"
"0",333","ccc","444","ddd"
"1",555","eee","666","fff"

実行結果

ｱｲｴｴｴｴ!ﾆﾝｼﾞｬ!?ﾆﾝｼﾞｬﾅﾝﾃﾞ!?
いや、ほんと何で？？二次元配列ってそのままセルに代入できるんじゃないの？？？

ローカルウィンドウで確認しても、result変数の中身は、意図通りに作成されています。
エラー値になるようなデータはありません。

VBAの多次元配列は、配列の入れ子ではない

Microsoftリファレンスでは、多次元配列について、以下のように説明されています。
配列を使用する (VBA) | Microsoft Docs

Visual Basic では、最大で 60 次元の配列を宣言できます。 たとえば、次の例では 5 × 10 の 2 次元配列を宣言しています。
VB
Dim sngMulti(1 To 5, 1 To 10) As Single
配列を行列と考えると、最初の引数は行を表し、2 番目の引数は列を表します。

そう、VBAの多次元配列は、配列の入れ子ではないのです。
**arr(i)(j)ではなく、arr(i, j)**なのです！！どちらも同じように使えると思ってたけど、そうじゃない！
VBAで可変長配列扱うの本当に面倒くさいな！？

解決策

パッと思いつく限りでは、2パターンあります。

1. 行数分ループして行単位で出力

真っ先に思い付いたのはこれ。
数百行×70項目程度だと処理時間もほぼ変わらなかったです。
列数に関しては、Rangeでセルを指定するときに添え字が1始まりなので+1しています。

Dim i As Long: i = 1
For Each rowData In result
    Range(Cells(i, 1), Cells(i, UBound(result(0)) + 1)).value = rowData
    i = i + 1
Next

2. Microsoftの指す多次元配列を使う

入れ子ではない多次元配列でデータを作成します。
1行のデータを設定するのに項目数分のループ処理が必要になりますが。
そうなるとRedim Preserve result(UBound(result, 1) + 1, UBound(result, 2))のようになるのかな…
と思ったんですが、違いました。これだとエラーになります。

Preserveは多次元配列において最終次元しか要素数を変更できないのです。
ReDim ステートメント (VBA) | Microsoft Docs

Preserveキーワードを使用した場合、最後の配列の次元のサイズのみを変更でき、次元の数を変更することはできません。 たとえば、配列にディメンションが1つしかない場合は、ディメンションのサイズを変更することができます。 ただし、配列に2つ以上の次元がある場合は、最後の次元のサイズのみを変更して、配列の内容を保持することができます。

今回は、元々のCSVの行数はわかりますが、その中で実際に出力対象となる行数が何行になるのかは、実際に処理を行ってみないとわかりません。
入れ子の配列でデータを作った後に多次元配列に入れ直すことになります。

Dim pBgnIdx As Long: pBgnIdx = LBound(result)
Dim cArr As Variant: cArr = result(pBgnIdx)
Dim tmpData As Variant: ReDim tmpData(pBgnIdx To UBound(result), LBound(cArr) To UBound(cArr))
Dim i As Long: For i = pBgnIdx To UBound(result)
    Dim j As Long: For j = LBound(cArr) To UBound(cArr)
        tmpData(i, j) = srcArr(i)(j)
    Next
Next
.Range(Range("A1"), Cells(UBound(tmpData, 1), UBound(tmpData, 2))).Value = tmpData

多次元配列に変換すると、データ構造がarr(i, j)になっていることが確認できます。

別解として、WorksheetFunction.Transpose関数を使うことで多次元配列の最終次元以外の添え字をどうにかすることはできなくもないようです。
が、添え字が1始まりに勝手に変更されます。
参考：[VBA]2次元配列の1次元目をRedim Preserveする – エンジニ屋

その他

他の手段としてはVB.NetのArrayListやCollectionオブジェクト等の可変長配列を使って、Redim Preserveを使わない方法なんかが考えられます。
これだとNewかCreateObjectしなきゃいけないので、大量データなら早さを優先してRedimのがいいこともあるかもしれません。
コードの可読性は上がりやすそうですけども。可変長配列だと後ろに追加するだけなので、添え字の指定でミスったりしないし。
実際業務で使うなら性能検証したほうがいいかもです。

もしくは同等の処理を実現するとして、Excelの機能をフルに使う手もあります。
作業シートにWorkSheet.QueryTables.AddでCSV読み込んで、Range.AdvancedFilterで抽出したデータを目的のシートに出力、最後に作業シートを削除する、とか。
この方法だと多次元配列は使わないので、上記の問題は発生しないです。