【Excel VBA極限の知見】なぜループ内の ReDim は許されないのか？メモリ再確保コストを殺す「バッファ戦略」の全技術
業務自動化の現場において、Excel VBAのパフォーマンス低下の元凶をたどると、その多くが「ループ内での不適切な配列の動的再定義（`ReDim`）」に行き着く。
「レコード数が変動するから、とりあえず `ReDim Preserve` で1行ずつ拡張していこう」
――このコードを書いた瞬間、あなたのマクロは時限爆弾を抱えたことになる。数千行程度なら体感できなくとも、数万行を超えるCSV取り込みやデータベース連携、あるいは複雑なオブジェクトの走査において、このアンチパターンは致命的なボトルネックを生む。
今回は、VBAのメモリ管理の裏側を暴き、プロのエンジニアが実践する「メモリ再確保のコストを最小化するバッファ戦略」を完全解説する。実務でそのまま使える堅牢なプロダクションコードと共に、ロジカルかつシャープに伝授しよう。
—
1. なぜ `ReDim Preserve` をループ内で使うと遅いのか？
メモリの「再確保とコピー」という隠れた重労働
まず、VBA（というよりWindowsのヒープマネージャ）がメモリをどのように扱っているかを理解する必要がある。
配列を `ReDim Preserve arr(UBound(arr) + 1)` として拡張する時、VBAは魔法のように既存の配列をその場で広げているわけではない。内部では以下の地獄のようなプロセスが毎回実行されている。
1. 新しいサイズの連続したメモリ領域をOSに要求する
2. 既存の配列が持っているすべての要素を、新しいメモリ領域にひとつずつ「値コピー」する
3. 古いメモリ領域を破棄（解放）する
これを要素数 $N$ のループ内で毎回行うとどうなるか。
コピーの計算量は $O(1 + 2 + 3 + \dots + N) = O(N^2)$ となり、データ数が倍増すれば、処理時間は4倍ではなく、幾何級数的に跳ね上がる。これが「ループ内 `ReDim` が遅い」真の理由である。
—
2. プロが実践する「バッファ戦略（チャンク確保）」とは
この問題を解決するアプローチはシンプルだ。
「毎回拡張するのではなく、あらかじめ大きめの器（バッファ）を確保し、溢れそうになったら倍のサイズで再確保する」。
これが「バッファ戦略（動的配列のチャンク管理）」である。これにより、メモリの再確保・コピーが発生する回数を、線形（$O(N)$）どころか対数オーダー（$O(\log N)$）にまで劇的に削減できる。
バッファ戦略のアルゴリズムイメージ
- 初期サイズを適当な大きさ（例: 1,000要素）で確保する。
- データを詰めていき、容量がいっぱいになったら、配列のサイズを「倍（2倍）」にして `ReDim Preserve` する。
- 最終的に、実際に使用したデータ数（有効件数）まで配列を切り詰める（`ReDim` でファイナライズ）。
この設計により、10万回のループであっても、メモリ再確保の回数はわずか数回～十数回に抑えられる。
—
3. 【プロダクションコード】堅牢かつ高速なデータローダー実装
実務の現場では、単に速いだけでなく、エラーハンドリングやメモリリークの防止、そして保守性が担保されていなければならない。以下のコードは、数万件規模の外部データ（CSVやDB）を読み込むシーンを想定した、実戦投入可能なバッファ戦略の実装例である。
Option Explicit
‘ =========================================================================
‘ 模範的データローダー：バッファ戦略による高速配列拡張
‘ =========================================================================
Public Sub ExecuteOptimizedDataLoading()
Dim wsSource As Worksheet
Set wsSource = ThisWorkbook.Sheets(“SourceData”)
Dim lastRow As Long
lastRow = wsSource.Cells(wsSource.Rows.Count, “A”).End(xlUp).Row
If lastRow < 2 Then MsgBox "処理対象のデータが存在しません。", vbExclamation Exit Sub End If Dim rawData As Variant rawData = wsSource.Range("A2:D" & lastRow).Value ' ワークシートからのバルク読み込み Dim targetData() As String Dim actualCount As Long actualCount = 0 ' 初期バッファサイズの設定（予想サイズ、あるいは固定で1000など） Dim bufferSize As Long bufferSize = 1000 ReDbimBuffer targetData, bufferSize Dim i As Long Dim startTime As Double startTime = Timer On Error GoTo ErrorHandler For i = 1 To UBound(rawData, 1) ' データのフィルタリング条件（例: A列が空でない場合のみ採用） If Len(CStr(rawData(i, 1))) > 0 Then
‘ バッファの容量チェックと動的拡張
If actualCount >= bufferSize Then
bufferSize = bufferSize 2 ‘ サイズを倍増（2倍ルール）
ReDim Preserve targetData(1, 1 To bufferSize) ‘ ※多次元配列の拡張は最終次元のみ可能
End If
actualCount = actualCount + 1
targetData(1, actualCount) = rawData(i, 1)
targetData(2, actualCount) = rawData(i, 2)
‘ 必要に応じて他列も格納
End If
Next i
‘ 【重要】余分なバッファを切り詰める（ファイナライズ）
If actualCount > 0 Then
ReDim Preserve targetData(1, 1 To actualCount)
Else
Erase targetData
End If
Debug.Print “処理完了: ” & actualCount & “件 / 処理時間: ” & (Timer – startTime) & “秒”
‘ 後続処理（ワークシートへの書き戻しなど）へ渡す
‘ Call ProcessData(targetData)
Exit Sub
ErrorHandler:
MsgBox “予期せぬエラーが発生しました: ” & Err.Description, vbCritical
End Sub
‘ ヘルパープロシージャ：2次元配列の初期化
Private Sub ReDbimBuffer(ByRef arr() As String, ByVal size As Long)
‘ VBAのReDim Preserveの仕様上、2次元配列の拡張は「第2次元（列方向）」しかできない点に注意
ReDim arr(1 To 2, 1 To size)
End Sub
コードの解説と設計上のポイント
1. 一括読み込み（バルクリード）の併用: ワークシートのセルにループでアクセスするのを避け、一度 `Range.Value` でVariant配列に落とし込んでいる。これも高速化の鉄則である。
2. 多次元配列の `ReDim Preserve` の制約への配慮: VBAの仕様上、`ReDim Preserve` で拡張できるのは「多次元配列の最後の次元のみ」である。そのため、コード例では行方向と列方向の持ち方を工夫し、拡張する次元を制御している。
3. ファイナライズ（切り詰め）: ループを抜けた後、実際に使用した `actualCount` のサイズに正確に `ReDim Preserve` し直すことで、後続の処理で余計な空データ（空白や初期値）を誤認するバグを防ぐ。
—
4. ファイル・データベース連携における注意点
このバッファ戦略は、Excelのシート操作だけでなく、以下のような「サイズが事前に予測できない外部リソース連携」において真価を発揮する。
- ADODB.Recordset からのデータ抽出:
レコードセットの `.RecordCount` が `-1`（前方すべりカーソルなど）を返す場合、全件数が事前にわからない。この場合も、今回紹介したバッファ戦略を用いてデータを配列にフェッチするのが最も安全かつ高速である。
- TextStream（CSV/テキストファイル）の行単位読み込み:
ファイルの行数が不明な場合、`Line Input #` で読み込みながらバッファ配列に蓄積していくことで、I/Oのオーバーヘッドを最小限に抑えた爆速CSVパーサが構築できる。
—
5. まとめ：コードの品格はメモリ管理に宿る
「動くからいいや」と書かれた `ReDim Preserve arr(i)` の一行は、データ量が増えた瞬間にシステムを麻痺させる時限爆弾となる。
プロフェッショナルなVBA開発者とそうでない者の違いは、「ハードウェアの制約（メモリの挙動）を意識してコードを書いているか」、ただそれだけだ。
今回解説した「バッファ戦略」をあなたの標準装備に加え、明日からの業務自動化ツールのパフォーマンスを劇的に引き上げてほしい。妥協のない設計こそが、保守性の高い最高峰のプロダクションコードを生み出すのだ。
