【実務・中級編】配列の多次元化とメモリレイアウト：効率的なデータ構造の選択 – Excel VBA解析バイブル

Excel VBAを掌握する：配列のメモリレイアウトと「データ構造」の極意

多くのVBAエンジニアが犯す最大の過ちは、「とりあえずセルの値を配列に放り込む」ことだ。そして、その配列を闇雲に回し、複雑なロジックを入れ込んで自滅する。

大規模データを扱う業務自動化において、メモリレイアウトと配列の次元設計は、処理速度と保守性を分かつ生命線である。今日は、VBAの深淵に触れる「配列の効率的設計」について、現場で生き残るための知見を授けよう。

—

1. 2次元配列 vs 1次元配列：その使い分けの境界線

VBAで `Range.Value` を取得した瞬間に生成されるのは「1ベースの2次元配列（`Variant(1 to n, 1 to m)`）」だ。これには明確な理由がある。Excelのシート構造が2次元だからだ。

しかし、この構造をそのままロジックに持ち込むのは悪手だ。

• 2次元配列が適しているケース:
• シートからのデータ一括読み込み・書き込み（I/O効率が最大化されるため）
• 行列形式の単純なデータ出力
• 1次元配列が適しているケース:
• 計算ロジックの中間データ保持
• 単一列（または単一行）の集計、ソート、検索
• 連想配列（`Scripting.Dictionary`）のキーや値として渡す場合

教訓: データの「入力/出力」は2次元で行い、データ「加工」は1次元にバラして行う。これがメモリ効率と可読性を両立させる黄金律だ。

—

2. インデックス管理の「罠」と防衛術

多次元配列を扱う際のバグの温床は、`LBound` と `UBound` の制御ミスにある。特に、動的配列 `ReDim` を繰り返す処理は、メモリの再確保（フラグメンテーション）を引き起こし、パフォーマンスを著しく低下させる。

バグをゼロにする設計のコツ

1. 必ず `Option Base 0` を前提としつつ、明示的に `(1 to N)` と宣言せよ: VBAはデフォルトのインデックスが変動する可能性がある。常に範囲を明示することで、境界エラーを未然に防ぐ。
2. 配列の「転置」を恐れるな: 2次元配列の「行」をループするのはキャッシュヒット率が低い。列アクセスが頻発する場合は、一度1次元配列に抽出（Extract）してから処理せよ。

—

3. 実践コード：保守性の高いデータ集計モデル

以下は、`Dictionary` を活用して2次元配列の重複排除と集計を行う、プロダクション現場で汎用される設計パターンだ。

‘ 【推奨】データ集計の定石：2次元入力 -> Dictionary変換 -> 1次元加工
Public Sub ProcessLargeData()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim rawData As Variant
Dim dict As Object: Set dict = CreateObject(“Scripting.Dictionary”)

‘ 1. データの高速取り込み (Range -> 2D Array)
rawData = ws.Range(“A2:B10000”).Value

‘ 2. メモリ効率を意識した集計処理
Dim i As Long
Dim key As String, val As Double

For i = LBound(rawData, 1) To UBound(rawData, 1)
key = rawData(i, 1) ‘ 集計キー
val = rawData(i, 2) ‘ 数値

‘ 集計ロジック：Dictionaryで一意性を担保
If Not dict.Exists(key) Then
dict.Add key, val
Else
dict(key) = dict(key) + val
End If
Next i

‘ 3. 出力用配列の定義と書き出し
‘ ここで初めて出力用の2次元配列を再構築する
Call WriteResult(dict)
End Sub

Private Sub WriteResult(dict As Object)
‘ 出力用にメモリを確保（ReDimによる再確保を最小化）
Dim outputArr() As Variant
ReDim outputArr(1 To dict.Count, 1 To 2)

Dim i As Long: i = 1
Dim key As Variant
For Each key In dict.Keys
outputArr(i, 1) = key
outputArr(i, 2) = dict(key)
i = i + 1
Next key

‘ 一括書き出し
Sheet2.Range(“A1”).Resize(UBound(outputArr, 1), 2).Value = outputArr
End Sub

—

4. なぜこれが「最強の設計」なのか

• メモリの局所性: `Dictionary` を挟むことで、配列のインデックスを直接操作する複雑なロジックを排除している。
• I/Oの最小化: `Range.Value` へのアクセスを最小限にし、メモリ上で演算を完結させている。
• 保守性: `WriteResult` を独立させることで、出力先がDBであれテキストファイルであれ、ロジックを改修せずに対応可能だ。

最後に：エンジニアへの提言

VBAで「動かない」と悩む時、その原因の9割はメモリ上のデータ構造が崩壊していることにある。配列を単なる「箱」と考えるな。配列はデータ処理の「パイプライン」であると意識せよ。

複雑なロジックをコードに書く前に、まず紙の上でデータの流れを整理しろ。多次元配列を愛し、そのインデックスの境界を支配した時、あなたの書くVBAコードは「ただ動くもの」から「止まらない資産」へと進化する。

さあ、次のコードでその真価を証明してほしい。