「ReDim Preserve」の深淵：Excel VBA配列操作、そのパフォーマンスの真実と最適解
諸君、業務自動化の現場で日々奮闘している開発者の皆さんに、私は今日、Excel VBAにおける「配列の動的再定義（ReDim Preserve）」という、一見便利だが、その裏に潜むパフォーマンスの「深淵」について語りたい。
多くの開発者が、データ件数が不定の場合に安易に`ReDim Preserve`をループ内で使用し、知らず知らずのうちにアプリケーションのパフォーマンスを著しく劣化させている。これは「動くコード」と「プロダクションレベルの堅牢で高速なコード」の間に横たわる、決して見過ごしてはならない本質的な問題だ。
私はチーフアーキテクトとして、この根本原因を解き明かし、メモリ効率を最大化するための配列操作の極限の知見、そしてコレクションオブジェクトとの使い分け基準を提示する。単なるテクニックの羅列ではない。なぜその書き方が非効率なのか、どう設計すべきかをロジカルかつシャープに伝授しよう。
1. ReDim Preserveのメカニズムを解剖する：なぜ「遅い」のか？
まず、`ReDim Preserve`が内部で何を行っているのかを理解することから始めよう。ここがパフォーマンス問題の本質である。
Excel VBAにおける配列は、メモリ上の連続した領域にデータを格納するデータ構造だ。これにより、インデックスを用いた高速なデータアクセスが可能となる。
- `Dim myArray()`: この宣言だけでは、配列自体はメモリ上に確保されない。ただのポインタ（メモリ上のアドレスを指す変数）が準備されるだけだ。
- `ReDim myArray(n)`: ここで初めて、サイズ`n`の連続したメモリブロックが確保され、`myArray`のポインタがそのブロックの先頭を指すようになる。もし以前に`myArray`が別のメモリブロックを指していた場合、そのブロックは解放される。
- `ReDim Preserve myArray(m)`: これが問題の核心だ。`Preserve`キーワードは「既存のデータを維持する」ことを意味する。しかし、メモリの仕組み上、既に確保されている連続した領域の「直後」に、都合よく追加の連続した空き領域が存在するとは限らない。現実には、ほとんどの場合、そんな都合の良い空きはない。
したがって、`ReDim Preserve`が実行されると、システムは以下の非効率なステップを踏むことになる。
1. 新しいメモリブロックの探索と確保: 現在の配列の要素数（`UBound – LBound + 1`）と、新しいサイズ`m`の両方を収容できる、全く新しい、より大きな連続したメモリブロックをメモリ上のどこかに探し、確保する。
2. 既存データのコピー: 古いメモリブロックに格納されていたデータを、先頭から順に1つずつ、新しく確保されたメモリブロックへ全てコピーする。
3. ポインタの更新と古い領域の解放: `myArray`のポインタを、新しく確保されたメモリブロックの先頭アドレスに切り替え、古いメモリブロックはシステムに解放される。
この一連の処理、特に「新しいメモリブロックの探索」「既存データのコピー」「古い領域の解放」は、いずれもCPUサイクルとメモリ帯域を消費する高コストな操作だ。
2. パフォーマンス問題の具体的な症状と発生シナリオ
このメカニズムを理解すれば、なぜ`ReDim Preserve`が「遅い」のか、そしてどのような状況で問題が顕在化するのかが明確になる。
2.1. ループ内でのReDim Preserve：最悪のパターン（O(N^2)の悲劇）
例えば、ファイルから1行ずつデータを読み込み、それを配列に追加していくようなシナリオを考えてみよう。
‘ // NG例: ループ内ReDim Preserveによるパフォーマンス劣化の典型
Sub ProcessData_Inefficient()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, “A”).End(xlUp).Row
Dim dataArray() As String
Dim i As Long
Dim arrayIndex As Long
arrayIndex = 0
‘ データが1行もない場合はエラーになるため、初回のみ特別なReDim
If lastRow > 1 Then ‘ ヘッダー行を除く
ReDim dataArray(0) ‘ 最初の要素を確保
For i = 2 To lastRow ‘ 2行目から最終行までループ
‘ ループ内でReDim Preserveを繰り返すのは最悪のパターン
‘ 配列サイズがNの場合、N回のコピーが発生する
ReDim Preserve dataArray(arrayIndex)
dataArray(arrayIndex) = ws.Cells(i, “A”).Value & “,” & ws.Cells(i, “B”).Value
arrayIndex = arrayIndex + 1
Next i
Else
MsgBox “処理対象データがありません。”, vbInformation
Exit Sub
End If
MsgBox “処理完了。配列サイズ: ” & UBound(dataArray) + 1, vbInformation
‘ Debug.Print Join(dataArray, vbCrLf) ‘ 必要に応じて内容を確認
End Sub
このコードでは、ループが1回実行されるたびに`ReDim Preserve`が呼び出される。
配列の要素数が`k`の場合、`k`個のデータを新しい領域にコピーするコストが発生する。
これを`N`回繰り返すと、総コピー回数は `1 + 2 + … + (N-1)` となり、これは `N (N-1) / 2`、つまりO(N^2) の計算量となる。
`N`が1000件程度であれば体感できる遅延だが、1万件、10万件となると、数秒どころか数十秒、数分という単位で処理時間が跳ね上がる。これは、業務効率化ツールとしては致命的な欠陥だ。
2.2. メモリ使用量の増大とOutOfMemoryエラー
`ReDim Preserve`は、新しいメモリブロックを確保してから既存データをコピーし、その後古いブロックを解放する。この間、一時的に古いブロックと新しいブロックの両方がメモリ上に存在するため、必要なメモリ量が一時的に倍近くになる可能性がある。
大量のデータを扱う場合、この一時的なメモリ消費の増大が原因で、システムリソースを圧迫し、Excelの応答停止や、最悪の場合「メモリ不足」エラー（OutOfMemory）を発生させ、アプリケーションがクラッシュするリスクがある。
3. ReDim Preserveの最適化戦略：代替手法と使い分け
それでは、このパフォーマンスの深淵を回避し、堅牢で高速なコードを記述するためにはどうすればよいのか。以下の戦略を使いこなすことが、真のプロフェッショナルへの道だ。
3.1. 事前サイズ決定：最も理想的なアプローチ
最も効率的なのは、配列が必要とする最大サイズを事前に把握し、一度だけ`ReDim`（Preserveなし）で確保することだ。これならば、メモリ探索もコピーも一度で済む。
- 適用シナリオ:
- 処理対象の行数（Excelシート、CSVファイル）やレコード数（データベース）が事前にカウントできる場合。
- 固定件数のデータ処理。
‘ // OK例1: 事前サイズ決定による最適化
Sub ProcessData_Efficient_PreAllocate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, “A”).End(xlUp).Row
‘ データが1行もない場合やヘッダー行のみの場合は処理しない
If lastRow <= 1 Then
MsgBox "処理対象データがありません。", vbInformation
Exit Sub
End If
Dim dataArray() As String
' ヘッダー行を除くデータ行数で配列サイズを決定
' UBoundは0から始まるため、(lastRow - 2)で0ベースのインデックスを確保
ReDim dataArray(lastRow - 2)
Dim i As Long
Dim arrayIndex As Long
arrayIndex = 0
For i = 2 To lastRow ' 2行目から最終行までループ
' 事前に確保した配列に直接データを格納
dataArray(arrayIndex) = ws.Cells(i, "A").Value & "," & ws.Cells(i, "B").Value
arrayIndex = arrayIndex + 1
Next i
MsgBox "処理完了。配列サイズ: " & UBound(dataArray) + 1, vbInformation
' Debug.Print Join(dataArray, vbCrLf)
End Sub
データベース連携の場合：
‘ // DB連携例: 事前サイズ決定とレコードセットからの高速読み込み
Sub ProcessDataFromDB_Efficient()
Dim conn As Object ‘ ADODB.Connection
Dim rs As Object ‘ ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim dataArray() As Variant ‘ Recordset.GetRowsはVariant型を返すことが多い
‘ データベース接続文字列 (例: Accessデータベース)
strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\MyDatabase.accdb;”
strSQL = “SELECT Field1, Field2 FROM MyTable WHERE Status = ‘Active’;”
Set conn = CreateObject(“ADODB.Connection”)
Set rs = CreateObject(“ADODB.Recordset”)
On Error GoTo ErrorHandler
conn.Open strConn
rs.Open strSQL, conn, 3, 1 ‘ adOpenStatic, adLockReadOnly
If Not rs.EOF Then
‘ レコード数を事前に取得し、配列のサイズを一度に決定
‘ GetRowsは2次元配列を返すため、配列のReDimは不要
‘ GetRowsの戻り値は (フィールド数, レコード数) となる
dataArray = rs.GetRows()
MsgBox “DBデータ処理完了。取得レコード数: ” & UBound(dataArray, 2) + 1, vbInformation
‘ Debug.Print dataArray(0, 0) ‘ 1列目1行目のデータ
‘ Debug.Print dataArray(1, 0) ‘ 2列目1行目のデータ
Else
MsgBox “該当するデータがありません。”, vbInformation
End If
Exit_Sub:
If Not rs Is Nothing Then If rs.State = 1 Then rs.Close
If Not conn Is Nothing Then If conn.State = 1 Then conn.Close
Set rs = Nothing
Set conn = Nothing
Exit Sub
ErrorHandler:
MsgBox “エラーが発生しました: ” & Err.Description, vbCritical
GoTo Exit_Sub
End Sub
`Recordset.GetRows`は、レコードセット全体または指定された件数を一度にVariant型の2次元配列として返すため、`ReDim`が不要となる最も効率的なDB連携手法の一つだ。
3.2. Collectionオブジェクトとの使い分け：不定件数への対応
対象のデータ件数が全く予測できない、あるいは事前にカウントすることが非効率な場合、VBAの`Collection`オブジェクトが有効な選択肢となる。
- `Collection`の特性:
- 要素を追加するたびに内部的にメモリを再割り当てするが、そのメカニズムは`ReDim Preserve`よりも効率的（通常、必要以上に大きなブロックを確保しておくなど）。
- キーによるアクセスも可能だが、ここではリストとしての利用を想定。
- VBAには`Collection.ToArray()`のような便利なメソッドがないため、最終的に配列に変換するには手動での詰め替えが必要となる。
- 適用シナリオ:
- ユーザー入力によって追加されるデータ。
- 複雑な条件分岐により、最終的なデータ件数が予測しにくい場合。
- APIからのストリームデータなど、逐次処理が必要な場合。
‘ // OK例2: Collectionを使った効率的なデータ収集と配列への変換
Sub ProcessData_Efficient_UsingCollection()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, “A”).End(xlUp).Row
If lastRow <= 1 Then
MsgBox "処理対象データがありません。", vbInformation
Exit Sub
End If
Dim dataCollection As Collection
Set dataCollection = New Collection ' Collectionオブジェクトを初期化
Dim i As Long
For i = 2 To lastRow ' 2行目から最終行までループ
' Collectionにデータを追加 (内部的に効率的なメモリ管理が行われる)
dataCollection.Add ws.Cells(i, "A").Value & "," & ws.Cells(i, "B").Value
Next i
' Collectionから配列への変換
Dim dataArray() As String
If dataCollection.Count > 0 Then
ReDim dataArray(dataCollection.Count – 1) ‘ Collectionのサイズで一度だけ配列をReDim
For i = 0 To dataCollection.Count – 1
dataArray(i) = dataCollection.Item(i + 1) ‘ Collectionは1ベースインデックス
Next i
MsgBox “処理完了。配列サイズ: ” & UBound(dataArray) + 1, vbInformation
‘ Debug.Print Join(dataArray, vbCrLf)
Else
MsgBox “Collectionにデータがありませんでした。”, vbInformation
End If
Set dataCollection = Nothing ‘ オブジェクトの解放
End Sub
このアプローチでは、Collectionへの追加は比較的低コストであり、最終的な配列への変換は一度の`ReDim`と線形的なコピー（O(N)）で済むため、ループ内`ReDim Preserve`に比べて格段に高速だ。
3.3. バッチ処理（チャンク処理）：ReDim Preserveの回数を限定する
事前サイズ決定が難しく、`Collection`のオーバーヘッドも避けたい（またはVBAの`Collection`から配列への手動変換が煩雑と感じる）場合、`ReDim Preserve`の呼び出し回数を意図的に減らす「バッチ処理」や「チャンク処理」が有効だ。
- メカニズム:
1. 最初は少なめのサイズで配列を`ReDim`する。
2. 配列がいっぱいになりそうになったら、現在のサイズよりも大きなサイズ（例：現在の倍、または固定数`+N`）で`ReDim Preserve`を実行し、領域を拡張する。
3. これを繰り返すことで、`ReDim Preserve`の回数を大幅に削減する。
- 適用シナリオ:
- データ件数がおおよそは予測できるが、厳密ではない場合。
- メモリ消費をある程度抑えつつ、パフォーマンスを向上させたい場合。
‘ // OK例3: バッチ処理（チャンク処理）によるReDim Preserveの限定使用
Sub ProcessData_Efficient_ChunkedReDim()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(Rows.Count, “A”).End(xlUp).Row
If lastRow <= 1 Then
MsgBox "処理対象データがありません。", vbInformation
Exit Sub
End If
Dim dataArray() As String
Dim initialSize As Long: initialSize = 100 ' 初期サイズ
Dim expandFactor As Long: expandFactor = 50 ' 拡張する固定サイズ（または倍率）
Dim i As Long
Dim arrayIndex As Long
arrayIndex = 0
' まずは初期サイズで配列を確保
ReDim dataArray(initialSize - 1)
For i = 2 To lastRow ' 2行目から最終行までループ
' 配列の現在のサイズを超えそうになったら拡張
If arrayIndex > UBound(dataArray) Then
‘ 現在のサイズにexpandFactorを追加して拡張
‘ ReDim Preserveの回数を減らすのが目的
ReDim Preserve dataArray(UBound(dataArray) + expandFactor)
End If
dataArray(arrayIndex) = ws.Cells(i, “A”).Value & “,” & ws.Cells(i, “B”).Value
arrayIndex = arrayIndex + 1
Next i
‘ 最後に、実際に使用した要素数に合わせて配列を最適化（無駄な領域を解放）
If arrayIndex > 0 Then
ReDim Preserve dataArray(arrayIndex – 1)
MsgBox “処理完了。最終配列サイズ: ” & UBound(dataArray) + 1, vbInformation
Else
ReDim dataArray(0) ‘ データがない場合は空の配列にする
MsgBox “データは処理されませんでした。”, vbInformation
End If
‘ Debug.Print Join(dataArray, vbCrLf)
End Sub
この手法は、完全に不定な件数に対しても、`ReDim Preserve`の頻度をコントロールすることで、良好なパフォーマンスとメモリ効率のバランスを取ることができる。ただし、最後に使用済み領域に合わせて`ReDim Preserve`を再度行うことで、無駄なメモリ消費を抑制することも忘れてはならない。
4. 設計思想と堅牢性：プロダクションコードの品質を保証する
いくら高速なコードを書けても、バグだらけでは意味がない。業務効率化ツールは、現場で安定して稼働し続けることが求められる。以下の設計思想を徹底せよ。
4.1. バグの起きない堅牢な設計
- `Option Explicit`の徹底: モジュールの先頭に必ず記述し、変数の明示的な宣言を強制する。これにより、タイプミスによるバグや予期せぬ挙動を未然に防ぐ。
- エラーハンドリング: `On Error GoTo` を適切に使用し、予期せぬエラー発生時にユーザーに状況を伝え、安全に処理を終了させる。特にファイル操作、データベース接続、API連携など、外部リソースとのやり取りでは必須だ。
- 境界値のチェック: 配列のインデックスが`LBound`や`UBound`を超えないか、ループの範囲は適切か、常に意識してコードを記述する。空のデータセットや単一要素のデータセットでも正しく機能するか確認する。
- オブジェクトの解放: `Set obj = Nothing` を明示的に行い、使用済みオブジェクトのメモリを解放する。特にADO `Connection`や`Recordset`、`FileSystemObject`など、システムリソースを消費するオブジェクトは、確実に解放すること。
4.2. ファイルやデータベース連携の注意点
- ファイルパスの動的な取得: `ThisWorkbook.Path`や`Environ(“UserProfile”)`などを利用し、環境依存しないパス指定を心がける。
- 排他制御: 複数ユーザーが同時にアクセスする可能性のあるファイルやDBテーブルに対しては、排他制御のメカニズム（ロックなど）を理解し、競合を避ける設計を検討する。VBAでは難しい場合でも、少なくともエラーハンドリングで対応する。
- DB接続のライフサイクル: データベース接続は高コストな処理だ。必要な時に開き、処理が終わり次第すぐに閉じること。オープンしたまま放置すると、リソース枯渇やロックの原因となる。
4.3. 保守性の高いプロダクションコード
- 適切なコメント: コードの意図、複雑なロジックの説明、設計上の選択理由などをコメントに残す。未来の自分、そして後任の開発者のためだ。
- 意味のある変数名・プロシージャ名: `i`, `j`, `tmp`のような汎用的な名前だけでなく、`lastRow`, `dataCollection`, `processedRecordsCount`のように、その変数が何を表すのかが一目でわかる名前をつける。
- プロシージャの分割: 一つのプロシージャに多くの機能を詰め込みすぎない。単一責任の原則に基づき、機能を小さなプロシージャに分割し、それぞれが特定のタスクのみを実行するようにする。これにより、テストが容易になり、再利用性も向上する。
5. まとめと提言：最高のパフォーマンスと堅牢性を持つコードへ
諸君、今日、私は`ReDim Preserve`が持つ「動的な柔軟性」の裏に隠された「パフォーマンスの真実」を解き明かし、その深淵を回避するための具体的な戦略を示した。
- `ReDim Preserve`は、極力使用回数を減らすべきである。 ループ内での安易な使用は、アプリケーションを破滅に導く。
- 最も理想的なのは、事前にサイズを決定し、一度だけ`ReDim`することだ。
- 件数が不定な場合は、`Collection`オブジェクトを賢く利用し、最終的に配列に変換する手法が有効だ。
- どうしても`ReDim Preserve`が必要な場合は、バッチ処理によって呼び出し頻度を限定する。
「動けばいい」という段階はもう終わりだ。皆さんが目指すべきは、目の前の業務をただ自動化するだけでなく、将来にわたって安定稼働し、効率性、堅牢性、保守性の全てを兼ね備えた「プロダクションレベルの業務自動化ツール」を構築することだ。
今日の知見を胸に刻み、常に最適なデータ構造とアルゴリズムを選択する意識を持つこと。それが、真の業務自動化エンジニア、そして開発プロジェクトのリーダーとしての責務であると、私は断言する。
