VBAで2次元配列に要素を追加する方法｜ReDim Preserveの制約と3つの解決策

「VBAの2次元配列に行を追加しようとしたらエラーになった」
「ReDim Preserveで2次元配列を拡張できない理由がわからない」
「データを保持したまま配列のサイズを増やしたい」

こうした悩みはVBAで2次元配列を使っていると必ずぶつかる壁です。
この記事では、2次元配列への要素追加で起こるエラーの原因と、場面別の解決策を具体的なコード付きで解説します。

---

まず知っておきたいこと：ReDim Preserveの制約

VBAで配列のサイズを変更するには ReDim を使います。
値を保持したままサイズを変更したい場合は ReDim Preserve を使いますが、2次元配列には大きな制約があります。

多次元配列で ReDim Preserve を使って変更できるのは、最後の次元（2次元目）のみです。

操作	可否
2次元目（列方向）を増やす	✅ 可能
1次元目（行方向）を増やす	❌ エラー（実行時エラー9）

Dim arr() As Variant
ReDim arr(3, 2)  ' 4行 × 3列

' ✅ 2次元目の拡張はできる
ReDim Preserve arr(3, 5)  ' 4行 × 6列に拡張OK

' ❌ 1次元目の拡張はエラーになる
ReDim Preserve arr(5, 2)  ' 実行時エラー9

Excelのシートでいうと「列は増やせるが、行は増やせない」という状態です。
この制約を理解した上で、3つの解決策を紹介します。

---

方法1：最初から列方向（2次元目）を可変にして設計する

最もシンプルな解決策は、行と列の意味を逆に設計することです。

通常「行 = データの件数、列 = 項目数」と考えがちですが、VBAの2次元配列では逆に設計すると ReDim Preserve をそのまま使えます。

「1次元目 = 項目数（固定）、2次元目 = データ件数（可変）」として設計する例：

Sub Sample_ListDesign()
    Dim arr() As Variant
    Dim i As Long

    ' 1次元目 = 項目数(0:名前, 1:点数) で固定
    ' 2次元目 = データ件数（可変）
    ReDim arr(0 To 1, 0 To 0)

    ' 1件目を追加
    arr(0, 0) = "田中"
    arr(1, 0) = 85

    ' 2件目を追加（2次元目を拡張）
    ReDim Preserve arr(0 To 1, 0 To 1)
    arr(0, 1) = "鈴木"
    arr(1, 1) = 90

    ' 3件目を追加
    ReDim Preserve arr(0 To 1, 0 To 2)
    arr(0, 2) = "佐藤"
    arr(1, 2) = 78

    ' 結果を確認
    For i = 0 To UBound(arr, 2)
        Debug.Print arr(0, i) & "：" & arr(1, i) & "点"
    Next i
End Sub

出力結果：

田中：85点
鈴木：90点
佐藤：78点

この方法が使えるのは、最初から設計できる場合に限られます。
既存の「行がデータ件数」の2次元配列に後から行を追加したい場合は、次の方法を使います。

---

方法2：別配列にコピーして行方向に拡張する（推奨）

最も汎用的で安全な方法です。
新しいサイズの配列を作成し、元の値をFor文でコピーして、元の変数に再代入します。

Sub Sample_AddRow()
    Dim arr() As Variant
    Dim newArr() As Variant
    Dim i As Long, j As Long
    Dim newRowCount As Long

    ' 初期データ（2行 × 3列）
    ReDim arr(0 To 1, 0 To 2)
    arr(0, 0) = "田中" : arr(0, 1) = 85 : arr(0, 2) = "A組"
    arr(1, 0) = "鈴木" : arr(1, 1) = 90 : arr(1, 2) = "B組"

    ' 1行追加する
    newRowCount = UBound(arr, 1) + 1  ' 新しい1次元目の最大インデックス

    ' 新しいサイズの配列を作成
    ReDim newArr(0 To newRowCount, 0 To UBound(arr, 2))

    ' 元の値をコピー
    For i = 0 To UBound(arr, 1)
        For j = 0 To UBound(arr, 2)
            newArr(i, j) = arr(i, j)
        Next j
    Next i

    ' 新しい行にデータを追加
    newArr(newRowCount, 0) = "佐藤"
    newArr(newRowCount, 1) = 78
    newArr(newRowCount, 2) = "A組"

    ' 元の変数に代入して差し替え
    arr = newArr

    ' 結果を確認
    For i = 0 To UBound(arr, 1)
        Debug.Print arr(i, 0) & "：" & arr(i, 1) & "点：" & arr(i, 2)
    Next i
End Sub

出力結果：

田中：85点：A組
鈴木：90点：B組
佐藤：78点：A組

この方法を関数化しておくと、繰り返し使えて便利です。

'=========================================
' 2次元配列の1次元目（行）を1つ増やす関数
' 引数1 arr  : 拡張元の配列（Variant型）
' 引数2 newRow : 追加後の1次元目最大インデックス
' 戻り値     : 行を追加した新しい配列
'=========================================
Function AddRow2DArray(ByVal arr As Variant, ByVal newRow As Long) As Variant
    Dim tmp As Variant
    Dim i As Long, j As Long

    ReDim tmp(LBound(arr, 1) To newRow, LBound(arr, 2) To UBound(arr, 2))

    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            tmp(i, j) = arr(i, j)
        Next j
    Next i

    AddRow2DArray = tmp
End Function

' 使い方
Sub Sample_UseFunction()
    Dim arr() As Variant
    ReDim arr(0 To 1, 0 To 2)
    arr(0, 0) = "田中" : arr(0, 1) = 85 : arr(0, 2) = "A組"
    arr(1, 0) = "鈴木" : arr(1, 1) = 90 : arr(1, 2) = "B組"

    ' 行を1つ追加
    arr = AddRow2DArray(arr, UBound(arr, 1) + 1)

    ' 追加した行にデータを入れる
    arr(2, 0) = "佐藤"
    arr(2, 1) = 78
    arr(2, 2) = "A組"

    Debug.Print UBound(arr, 1)  ' → 2（3行になった）
End Sub

---

方法3：WorksheetFunction.Transposeを使う（各次元のサイズが65536以下の場合）

Transpose（転置）を使って一時的に行と列を入れ替え、ReDim Preserve で拡張してから再び転置する方法です。

コードがシンプルになりますが、各次元のサイズが65536を超えるとエラーになるという制限があります（Excel旧バージョン由来の制限）。
大きなデータを扱う場合は方法2を使ってください。

'=========================================
' Transposeを使って1次元目（行）を1行増やす関数
' ※各次元のサイズが65536以下の場合のみ使用可
'=========================================
Function AddRow2DArrayByTranspose(ByVal arr As Variant) As Variant
    Dim tmp As Variant

    ' 転置して行と列を入れ替える
    ' ※WorksheetFunction.Transposeの戻り値は常に1ベース配列
    tmp = WorksheetFunction.Transpose(arr)

    ' 2次元目（元の1次元目）を1つ拡張する
    ' Transposeの戻り値は1ベースのため UBound + 1 で正しく1行分増える
    ReDim Preserve tmp(LBound(tmp, 1) To UBound(tmp, 1), _
                       LBound(tmp, 2) To UBound(tmp, 2) + 1)

    ' 転置して元の向きに戻す
    AddRow2DArrayByTranspose = WorksheetFunction.Transpose(tmp)
End Function

' 使い方
Sub Sample_UseTranspose()
    Dim arr() As Variant
    ReDim arr(0 To 1, 0 To 2)
    arr(0, 0) = "田中" : arr(0, 1) = 85 : arr(0, 2) = "A組"
    arr(1, 0) = "鈴木" : arr(1, 1) = 90 : arr(1, 2) = "B組"

    ' 行を1つ追加（引数は配列のみ。常に1行追加）
    arr = AddRow2DArrayByTranspose(arr)

    ' 追加した行にデータを入れる
    arr(2, 0) = "佐藤"
    arr(2, 1) = 78
    arr(2, 2) = "A組"

    Debug.Print UBound(arr, 1)  ' → 2（3行になった）
End Sub

---

方法の使い分けまとめ

状況	推奨する方法
設計段階から列方向（2次元目）を可変にできる	方法1：列方向設計
既存の行方向配列に行を追加したい（大きなデータも含む）	方法2：別配列コピー（関数化推奨）
各次元のサイズが65536以下でコードを短くしたい	方法3：Transpose利用

---

よくある失敗と対処法

失敗①：静的配列に ReDim を使おうとした

Dim arr(5, 3) As Variant のように要素数を固定して宣言した配列（静的配列）は、ReDim でサイズ変更できません。
動的配列として宣言するには Dim arr() As Variant と要素数を省略します。

' ❌ 静的配列：サイズ変更不可
Dim arr(5, 3) As Variant

' ✅ 動的配列：ReDimで変更可
Dim arr() As Variant
ReDim arr(5, 3)

失敗②：インデックスの開始番号に注意

LBound で開始インデックスを確認してから操作するのが安全です。
Option Base 1 をモジュールの先頭に書いていると、既定の開始インデックスが0ではなく1になります。

Debug.Print LBound(arr, 1)  ' 1次元目の開始インデックス
Debug.Print UBound(arr, 1)  ' 1次元目の終了インデックス
Debug.Print LBound(arr, 2)  ' 2次元目の開始インデックス
Debug.Print UBound(arr, 2)  ' 2次元目の終了インデックス

失敗③：ループ中に毎回 ReDim Preserve するとパフォーマンスが低下する

1件追加のたびに配列を再確保するのはメモリ効率が悪く、処理が遅くなります。
先に最大件数で配列を確保しておき、処理後に必要な分だけ削るのがベストプラクティスです。

Sub Sample_Performance()
    Dim arr() As Variant
    Dim cnt As Long

    ' 最大100件分を先に確保
    ReDim arr(0 To 99, 0 To 2)

    cnt = 0
    ' データを格納（ここではサンプルとしてループ）
    Dim i As Long
    For i = 1 To 50  ' 実際には条件付きで格納
        arr(cnt, 0) = "名前" & i
        arr(cnt, 1) = i * 10
        arr(cnt, 2) = "グループ"
        cnt = cnt + 1
    Next i

    ' 実際に使った分だけに縮小（2次元目は変更しない）
    ' ※1次元目の縮小はReDim Preserveでは不可のため、別配列コピーが必要
    ' ここでは2次元目（列）のみの例
    ' ReDim Preserve arr(0 To cnt - 1, 0 To 2)  ← 1次元目変更のためエラー

    Debug.Print "格納件数: " & cnt
End Sub

---

まとめ

VBAの2次元配列に要素を追加する際のポイントをまとめます。

• ReDim Preserve で変更できるのは最終次元（2次元目）のみ。1次元目はエラーになる
• 列方向を可変にして設計するのが最もシンプルな解決策（方法1）
• 行方向に追加したい場合は別配列を作成してコピーする（方法2）。関数化しておくと再利用しやすい
• 各次元のサイズが65536以下ならTransposeを使う方法もある（方法3）
• ループ内で毎回 ReDim するのは避け、最大サイズを先に確保しておくのがパフォーマンス上の鉄則

VBAのマクロ全般についてはExcelマクロとVBAの基本も参考にしてください。
マクロの編集方法についてはExcelのマクロを編集するには？で詳しく解説しています。

---

参考情報源:

• Microsoft Learn「ReDim ステートメント（VBA）」（learn.microsoft.com）
• Microsoft Learn「WorksheetFunction.Transpose メソッド（Excel）」（learn.microsoft.com）