Excel VBAを掌握する極限の知見：動的配列の真実 —— `ReDim` と `Preserve` のコストを知り尽くせ
開発現場でよく見かける光景がある。行数が変動するCSVやデータベースからの読み込みに対し、最初から `Dim data(100000) As String` のように巨大な固定長配列を確保してしまうコードだ。あるいは、安全のためにと、ループのたびに `ReDim Preserve` を実行する愚行。
断言しよう。その設計は、大規模データ処理においてメモリとパフォーマンスを確実に殺す。
VBAの動的配列は、正しく使えば最強の武器になるが、そのメカニズムを理解せずに触れば、たちまちメモリリークや爆発的な処理遅延というバグの温床となる。
今回は、実務の現場で「絶対に破綻しない」動.的配列の制御手法を、アーキテクトの視点からロジカルかつシャープに伝授する。
—
1. なぜ「毎回の `ReDim Preserve`」は罪なのか？
動的配列のサイズを拡張する `ReDim Preserve`。既存のデータを保持したまま配列を拡張できるため非常に便利だが、ここにVBAのメモリ管理の罠がある。
VBA（COMのメモリ管理基盤）において、`ReDim Preserve` を実行すると、裏側では以下の重処理が走っている。
1. 新規メモリ領域の確保: 指定された新しいサイズ分のメモリブロックをOSに要求する。
2. 全データのコピー: 既存の配列に格納されていた全ての要素を、新しいメモリ領域へバイト単位で丸ごとコピーする。
3. 旧メモリ領域の解放: 古いメモリ領域を破棄する。
これをループの1回ごと（例えば1行読み込むごと）に実行したらどうなるか？
データ数が $N$ のとき、コピーの計算量は $O(N^2)$ となる。数万件のデータを読み込むだけで、PCのファンが唸りを上げ、エクセルがフリーズする原因はこれだ。
解決策：チャンク（塊）方式によるメモリ割り当ての最適化
プロの現場では、1件ごとに拡張するのではなく、「あらかじめ大きめの塊（チャンク）でメモリを確保し、足りなくなったら倍々に拡張する」手法をとる。これにより、コピー発生の頻度を劇的に抑え、計算量を $O(N)$ に近づけることができる。
—
2. 実務で耐えうる「堅牢な動的配列ラッパー」パターン
実際の業務システムでは、単に `ReDim` を書くだけでなく、現在の有効データ数（カウンタ）と実際の配列サイズを管理する構造が必要になる。
以下に、CSVやDBから可変長データを高速かつ安全に取得するためのプロダクションコードを提示する。そのままコピペしてプロジェクトに組み込んでほしい。
Option Explicit
‘ =========================================================================
‘ 業務システム向け：高速動取り込み・動的配列制御モジュール
‘ =========================================================================
Public Sub ExecuteDataProcessing()
Dim rawData() As String
Dim recordCount As Long
‘ 1. データの読み込み（ダミーとして外部データ取得をシミュレート）
rawData = LoadVariableDataFromSource(recordCount)
If recordCount = 0 Then
MsgBox “処理対象のデータが存在しません。”, vbExclamation
Exit Sub
End If
‘ 2. 実データ数に配列を切り詰める（トリミング）
‘ ※末尾の余分なメモリを解放し、後続の処理へクリーンな状態で渡す
ReDim Preserve rawData(recordCount – 1)
‘ 3. ビジネスロジックの実行
MsgBox “正常に ” & recordCount & ” 件のデータを読み込みました。”, vbInformation
‘ （ここにシートへの一括出力などの処理が続く）
End Sub
‘ ————————————————————————-
‘ チャンク方式による高速データローダー
‘ ————————————————————————-
Private Function LoadVariableDataFromSource(ByRef outCount As Long) As String()
Const CHUNK_SIZE As Long = 1000 ‘ 初期および拡張の単位ブロック
Dim buffer() As String
Dim capacity As Long
Dim i As Long
‘ 初期容量の確保
capacity = CHUNK_SIZE
ReDim buffer(capacity – 1)
outCount = 0
‘ 【シミュレーション】実際はここでADOによるDB接続や、Line Inputによるファイル読み込みを行う
For i = 1 to 15500 ‘ 例として15,500件のデータを想定
‘ バッファの容量を超過した場合の動的拡張（倍々方式：Geometric Growth）
If outCount >= capacity Then
capacity = capacity 2
ReDim Preserve buffer(capacity – 1)
End If
‘ データの格納
buffer(outCount) = “Data_Record_” & i
outCount = outCount + 1
Next i
‘ 参照渡しされた配列を返す
LoadVariableDataFromSource = buffer
End Function
—
3. データベース・ファイル連携における致命的な罠と回避策
動的配列を扱う際、外部リソース（DAO/ADODBによるデータベース、あるいはTextStreamによるファイル読み込み）との連携では、以下の2点に細心の注意を払う必要がある。
① Variant型の多用によるメモリフットプリントの肥大化
「型が分からないから」という理由で `Dim arr() As Variant` を多用していないか？
Variant型は非常に柔軟だが、内部で型の判定処理（Tag-Value構造）を持つため、StringやLong型に比べてメモリ消費量が数倍に膨れ上がり、キャッシュ効率が落ちる。
原則：扱うデータの型が明確な場合は、必ず強烈な型付配列（`String()`, `Long()` 等）を使用せよ。
② 配列の方向（次元数）のミスマッチ
Excelのワークシート（Range）に配列を書き戻す際、動的配列の次元数設計を誤ると、意図しない転記エラー（型が一致しません、またはインデックスが有効範囲にありません）を引き起こす。
- 1次元配列 `arr(n)`: 縦方向（列方向）に一括出力される。
- 2次元配列 `arr(m, n)`: 表形式（行×列）でワークシートへ一瞬でバースト転記（高速化の王道）できる。
データベースからレコードセットを一括して配列に落とし込む場合（`GetRows` メソッドなど）は、強制的に2次元配列として返されるため、自前で `ReDim` を組む必要すらないケースが多い。外部連携時は、まずネイティブな一括取得メソッドが使えないかを疑うこと。それが真のエンジニアリングだ。
—
4. チーフアーキテクトからの最終提言
VBAにおける `ReDim` と `Preserve` は、言うなれば「諸刃の剣」だ。
メモリの仕組みを知る者にとっては柔軟なデータ構造を構築する頼もしい相棒だが、無知な者にとってはパフォーマンスクラッシャーでしかない。
1. ループ内の毎回の `ReDim Preserve` は絶対悪。
2. チャンク（塊）確保と倍々拡張によるコスト削減を行え。
3. 処理の最後には必ずジャストサイズに `ReDim Preserve` でトリミングせよ。
この原則をコードに落とし込んだ瞬間から、あなたの作るツールは「おもちゃのマクロ」から「堅牢な業務システム」へと昇華する。現場の信頼を勝ち取るクリーンで高速なコードを、明日から実装してほしい。
