【Excel VBA】ReDimの呪縛から脱却せよ：配列の動的再定義コストを最小化する「バッファ戦略」の極意
こんにちは、チーフアーキテクトの私だ。
日々の業務自動化、ご苦労様。数万行のExcelデータ、外部データベースからの膨大なレコードセット、あるいはREST APIから返ってくるJSONのパース……。現場のエンジニアなら誰もが「VBAの処理速度の壁」に直面したことがあるはずだ。
「なんだか最近、このマクロの動きがやけに重い」
「データ件数が増えた途端、固まったようになる」
そのボトルネック、もしかしてループ内での無謀な `ReDim Preserve`ではないかね？
今回は、VBAにおける配列のメモリ管理の裏側を暴き、パフォーマンスを極限まで引き上げる「バッファ戦略（事前確保アルゴリズム）」を伝授する。明日から君の書くコードは見違えるほど軽快になるはずだ。
—
1. なぜ「ループ内の ReDim」は悪なのか？
まず、VBAが背負うメモリ管理の宿命を理解しよう。
多くの初学者は、以下のようなコードを書く。
‘ 【アンチパターン】絶対にやってはいけないコード
Dim i As Long
Dim arr() As String
For i = 1 to 100000
ReDim Preserve arr(1 To i) ‘ 毎回ReDimを呼んでいる
arr(i) = Cells(i, 1).Value
Next i
一見、何の問題もないように見える。しかし、このコードの裏で何が起きているか？
`ReDim Preserve` を実行するたびに、VBA（厳密には背後のCOM/OSメモリマネージャー）は以下の重労働を強いられている。
1. 指定されたサイズ（旧サイズ ＋ 1）の新しいメモリ領域をOSに要求し、確保する。
2. 既存の配列データを、一言一句漏らさず新しいメモリ領域へ「全コピー」する。
3. 古いメモリ領域を解放する。
これを10万回繰り返す。つまり、1回あたり数バイトの拡張のために、累計で何GBものデータコピーとメモリ確保・解放の往復をCPUに強要しているのだ。これが「ReDimのコスト」の正体であり、VBAが「遅い」と揶揄される最大の原因である。
—
2. 解決策：メモリの「まとめ取り（バッファ戦略）」とは
この無駄を排除するアプローチはシンプルだ。
「足りなくなったら1つずつ拡張するのではなく、最初から多めに確保し、足りなくなったら倍々にドカンと広げる」。
これが、OSやC/C++の世界でも常識となっているバッファ戦略（チャンク拡張／幾何学的拡張）だ。
イメージしてほしい。君が買い物をするとき、1個買うたびにレジに並び直すだろうか？ 違うはずだ。大きめのカート（バッファ）に商品を次々と放り込み、カートがいっぱいになった時だけ、少し大きめのカートに買い換えるか、あるいは最初から大きすぎるほどのカートを使えばいい。
VBAの配列においても、この思想を実装するだけで、実行時間を数分から数ミリ秒へ短縮することが可能になる。
—
3. 実装：堅牢で再利用可能な「動的配列バッファ」クラス
それでは、実際のプロダクションコードを公開しよう。
実務でそのままコピペして、今すぐプロジェクトに組み込めるクオリティに仕上げてある。
今回は、Variant型のデータを高速に蓄積し、最終的にクリーンな配列として取り出すための標準モジュールベースの設計だ。
Option Explicit
‘ =====================================================================
‘ モジュール名: M_ArrayBuffer
‘ 概要: ReDimのコストを最小化し、大量データの蓄積を高速化するバッファマネージャー
‘ =====================================================================
Private Const INITIAL_CAPACITY As Long = 1000 ‘ 初期バッファサイズ
Type DynamicBuffer
Data() As Variant 実データ格納用配列
Capacity As Long ‘ 現在確保しているメモリの最大要素数
Count As Long ‘ 実際に格納された有効データ数
End Type
‘ ———————————————————————
‘ バッファの初期化
‘ ———————————————————————
Public Sub InitBuffer(ByRef buf As DynamicBuffer, Optional ByVal initialSize As Long = INITIAL_CAPACITY)
buf.Capacity = initialSize
buf.Count = 0
ReDim buf.Data(1 To buf.Capacity)
End Sub
‘ ———————————————————————
‘ バッファへの要素追加（限界を超えたら倍加拡張）
‘ ———————————————————————
Public Sub AppendBuffer(ByRef buf As DynamicBuffer, ByVal val As Variant)
‘ カウントが容量を超えた場合、容量を2倍に拡張（几何級数的拡張）
If buf.Count >= buf.Capacity Then
buf.Capacity = buf.Capacity 2
ReDim Preserve buf.Data(1 To buf.Capacity)
End If
buf.Count = buf.Count + 1
‘ Object型やArray型の場合は適切に代入
If IsObject(val) Then
Set buf.Data(buf.Count) = val
Else
buf.Data(buf.Count) = val
End If
End Sub
‘ ———————————————————————
‘ 確定した有効データのみの配列を取得する
‘ ———————————————————————
Public Function GetResultArray(ByRef buf As DynamicBuffer) As Variant
If buf.Count = 0 Then
GetResultArray = Array() ‘ 空の配列を返す
Exit Function
End If
‘ 最終的なデータ数にぴったり切り詰める（最後の1回だけReDim）
Dim result() As Variant
ReDim result(1 To buf.Count)
Dim i As Long
For i = 1 To buf.Count
If IsObject(buf.Data(i)) Then
Set result(i) = buf.Data(i)
Else
result(i) = buf.Data(i)
End If
Next i
GetResultArray = result
End Function
このコードの設計ポイント
1. 幾何学的拡張（Geometric Growth）: 容量が不足した際、`Capacity = Capacity 2` と倍々に広げている。これにより、拡張の回数が劇的に減り（10万件でもたったの7〜8回）、メモリー再割り当てのオーバーヘッドがほぼ消滅する。
2. 最終的なトリミング: 最後に必要な分だけ `ReDim` でサイズを合わせるため、呼び出し側には「無駄な余白のない綺麗な配列」を返すことができる。
—
4. 実務での活用例：数万行のDB/ファイル連携を爆速化する
では、このバッファ戦略を実際の業務ツールにどう組み込むか。
例えば、シートから条件に合致するデータを抽出して処理するシナリオを考えてみよう。
Public Sub ProcessLargeData()
Dim wsSource As Worksheet
Set wsSource = ThisWorkbook.Sheets(“Data”)
Dim lastRow As Long
lastRow = wsSource.Cells(wsSource.Rows.Count, “A”).End(xlUp).Row
‘ 1. ソースデータを一括読み込み（Range -> Variant配列で高速化）
Dim rawData As Variant
rawData = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, 5)).Value
‘ 2. バッファの初期化
Dim buf As DynamicBuffer
InitBuffer buf, 5000 ‘ 予測されるサイズや初期値を設定
Dim i As Long
For i = 2 To lastRow ‘ ヘッダー除外
‘ 条件判定（例: 3列目の値が “Active” のものだけを抽出）
If rawData(i, 3) = “Active” Then
‘ 抽出データを加工してまとめる（1行分の1次元配列）
Dim rowItem(1 To 2) As Variant
rowItem(1) = rawData(i, 1) ‘ ID
rowItem(2) = rawData(i, 2) ‘ Name
‘ バッファに追加（ループ内ReDimなし！）
AppendBuffer buf, rowItem
End If
Next i
‘ 3. 最終結果の取得
Dim finalData As Variant
finalData = GetResultArray(buf)
‘ 4. まとめて出力（シートへの書き込みは1回に絞る）
If buf.Count > 0 Then
Dim wsDest As Worksheet
Set wsDest = ThisWorkbook.Sheets(“Result”)
wsDest.Cells.Clear
wsDest.Cells(1, 1).Resize(UBound(finalData, 1), 2).Value = finalData
MsgBox “処理完了。抽出件数: ” & buf.Count, vbInformation
Else
MsgBox “条件に合致するデータはありませんでした。”, vbExclamation
End If
End Sub
ファイルやデータベース連携における極意
- I/Oの回数を最小化する: シートへの書き込み（`.Cells` や `.Value` の代入）や、データベースへのクエリ発行は、VBAにおいて最も重い処理（I/Oバウンド）だ。配列バッファでデータをメモリ上で完全に組み立ててから、「一括で書き込む（Bulk Insert / Bulk Write）」のが鉄則である。
—
5. チーフアーキテクトからの提言
「動くコードを書くこと」と「スケールするコードを書くこと」は全く次元が違う。
日常の小さなツールであれば、ループ内の `ReDim Preserve` でも動くように見えるかもしれない。しかし、データ量が10倍、100倍になった途端に音を上げるシステムは、プロの成果物とは言えない。
今回紹介した「バッファ戦略」は、VBAに限らず、C#やJava、TypeScriptなど、あらゆる言語のデータ構造設計に通じる普遍的な知見だ。
メモリのライフサイクルを意識し、OSやランタイムに無駄な負荷をかけない優美なコードを書くこと。それこそが、現場を救う真のエンジニアの流儀である。
今日から君のVBAコードの書き方を、アップデートしてくれたまえ。
