配列の動的再定義（ReDim Preserve）の深淵：パフォーマンスの限界とメモリ最適化の真髄
Excel VBA。それは、多くのビジネスプロセスを支え、時にレガシーシステムとして息づく、強力な自動化ツールである。しかし、その利便性の裏には、知られざる落とし穴が潜んでいる。特に、動的なデータ構造を扱う上で避けては通れないのが、配列の動的再定義、すなわち `ReDim Preserve` の存在だ。
私は長年、このVBAという言語を深く掘り下げ、その限界と可能性を幾度となく試してきた。Windows API の呼び出し、メモリ管理の最適化、そして複雑なシステム間連携。その全てにおいて、配列の挙動を正確に理解することは、システムの安定性とパフォーマンスを左右する最重要課題であった。
この記事では、単なるリファレンスの解説に留まらない。`ReDim Preserve` の裏側で何が起こっているのか、そのパフォーマンスへの影響、そして、いかにしてメモリ効率を最大化し、レガシー環境でも陳腐化しない堅牢なコードを書き上げるのか。その極限の知見を、技術至上主義の視点から、淡々と、しかし圧倒的な正確さで解き明かしていく。
1. `ReDim Preserve` の真実：コピーの連鎖が招くコスト
多くの開発者が `ReDim Preserve` を、配列のサイズを変更する際の便利機能と捉えているだろう。しかし、その実態は、しばしば想像以上に重い処理である。
`ReDim Preserve` が実行されるとき、VBAは以下のステップを実行する:
1. 新しいサイズの配列をメモリ上に確保する。
2. 元の配列の要素を、新しい配列にコピーする。 `Preserve` キーワードが付いているため、既存の要素はそのまま引き継がれる。
3. 元の配列が占有していたメモリ領域を解放する。
この「コピー」のプロセスこそが、パフォーマンスのボトルネックとなる。特に、配列のサイズが大きくなるにつれて、コピーにかかる時間は指数関数的に増加する。ループ内で `ReDim Preserve` を頻繁に実行することは、「コピーの連鎖」 を発生させ、システム全体の応答性を著しく低下させる原因となる。
1.1. パフォーマンスへの影響を可視化する
簡単な例で、そのコストを体感してみよう。
Sub ReDimPreservePerformanceTest()
Dim arr() As Long
Dim startTime As Double
Dim endTime As Double
Dim i As Long
Dim maxElements As Long
‘ テストする最大要素数
maxElements = 10000
Debug.Print “ReDim Preserve パフォーマンステスト開始 (最大要素数: ” & maxElements & “)”
startTime = Timer
‘ 初期化
ReDim arr(0)
‘ ループ内で配列を拡張
For i = 1 To maxElements
ReDim Preserve arr(i)
‘ ここで配列へのアクセスや処理を行うと、さらにコストが増大する
‘ arr(i) = i 2 ‘ 例: 要素への代入
Next i
endTime = Timer
Debug.Print “完了。経過時間: ” & Format(endTime – startTime, “0.000”) & ” 秒”
‘ 比較のため、あらかじめ十分なサイズを確保した場合の処理時間も計測
Dim arrFixed() As Long
ReDim arrFixed(maxElements) ‘ あらかじめ最大サイズを確保
startTime = Timer
For i = 0 To maxElements
arrFixed(i) = i 2 ‘ 要素への代入
Next i
endTime = Timer
Debug.Print “固定サイズ配列の処理時間: ” & Format(endTime – startTime, “0.000”) & ” 秒”
‘ メモリ解放のためにオブジェクト変数をNothingにする（明示的な解放）
Set arr = Nothing
Set arrFixed = Nothing
End Sub
このコードを実行すると、`maxElements` が大きくなるにつれて、`ReDim Preserve` を繰り返す処理の時間が、固定サイズで宣言した配列に比べて著しく長くなることが確認できるはずだ。
1.2. Windows API によるメモリ管理の洞察
VBAは、COM（Component Object Model）オブジェクトモデルに基づいて構築されている。配列も、内部的にはCOMオブジェクトとして扱われ、メモリ管理もCOMの仕組みに依存している。
`ReDim Preserve` は、内部的に新しいメモリブロックを確保し、データをコピーした後、古いメモリブロックを解放する。このプロセスは、C++ や VB.NET で `new` 演算子や `malloc` 関数でメモリを確保し、`delete` や `free` で解放するのと似ているが、VBAでは開発者が直接メモリを管理する権限は限定的だ。
しかし、VBAのオブジェクトモデルを深く理解することで、メモリ管理の効率化を図ることは可能だ。例えば、不要になったオブジェクト変数は、明示的に `Nothing` を代入することで、早期にメモリ解放を促すことができる。これは、特に大量のオブジェクトを扱う場合や、メモリリークが懸念されるレガシーシステム保守において、極めて重要なプラクティスとなる。
‘ オブジェクト変数の明示的な解放
Dim obj As Object
Set obj = CreateObject(“Scripting.FileSystemObject”)
‘ … obj を使用する処理 …
Set obj = Nothing ‘ 不要になったらすぐに解放
2. メモリ効率を最大化するための配列操作テクニック
`ReDim Preserve` のコストを最小限に抑えるためには、その使用頻度とタイミングを戦略的に管理する必要がある。
2.1. 事前確保とバッファリング
最も効果的な方法は、可能な限り配列を事前に十分なサイズで確保しておくことだ。データ件数が事前に予測できる場合は、それを基に配列サイズを決定する。
もしデータ件数が不明な場合でも、ある程度の「バッファ」を設けておくことで、`ReDim Preserve` の実行回数を大幅に削減できる。例えば、100件単位でバッファを確保する、といった戦略だ。
Sub PreAllocateArray()
Dim arr() As String
Dim currentSize As Long
Dim bufferIncrement As Long
Dim newDataCount As Long
Dim i As Long
‘ 初期バッファサイズと増分
currentSize = 10
bufferIncrement = 100
ReDim arr(currentSize – 1) ‘ 初期サイズで確保
newDataCount = 0
‘ 新しいデータを追加する処理
For i = 1 To 550 ‘ 仮に550件のデータが追加されるとする
newDataCount = newDataCount + 1
‘ 現在の配列サイズを超える場合
If newDataCount > currentSize Then
‘ 次のバッファサイズを計算
currentSize = currentSize + bufferIncrement
‘ 配列を拡張（ただし、前回の拡張から一定期間経過している、あるいは一定件数追加された場合のみ）
‘ ここで単純に拡張するのではなく、ある程度まとまってから拡張するロジックを検討する
ReDim Preserve arr(currentSize – 1)
Debug.Print “配列拡張実行。新サイズ: ” & currentSize
End If
‘ 配列にデータを格納
arr(newDataCount – 1) = “Data_” & i
Next i
‘ 最終的なデータ数に合わせて配列サイズを調整（オプション）
If newDataCount < currentSize Then
ReDim Preserve arr(newDataCount - 1)
Debug.Print "最終調整。新サイズ: " & newDataCount
End If
' メモリ解放
Set arr = Nothing
End Sub
この例では、`newDataCount` が `currentSize` を超えた場合にのみ `ReDim Preserve` を実行している。`bufferIncrement` の値を調整することで、拡張の頻度とメモリ使用量のバランスを取ることができる。
2.2. 一時配列の活用
大量のデータを一時的に処理し、最終的な結果を配列に格納する場合、一時的な配列を操作し、最後に一度だけ結果を転送する、という手法も有効だ。
Sub UseTemporaryArray()
Dim finalArray() As Variant
Dim tempArray() As Variant
Dim currentRow As Long
Dim tempRow As Long
Dim i As Long
‘ 最終的に格納したいデータ件数（仮）
Dim totalDataCount As Long
totalDataCount = 20000
‘ 一時配列を十分なサイズで確保
‘ 処理の性質上、最初からある程度大きなサイズを確保できる場合
ReDim tempArray(totalDataCount – 1)
tempRow = 0
‘ データを一時配列に格納
For i = 1 To totalDataCount
tempArray(tempRow) = “Item_” & i & “_” & Rnd()
tempRow = tempRow + 1
Next i
‘ 最終的な配列に結果をコピー（または必要に応じて整形）
‘ ここで tempArray の未使用部分を切り捨てる
If tempRow < totalDataCount Then
ReDim Preserve tempArray(tempRow - 1)
End If
' 最終配列への代入（もし tempArray が最終配列として使われるなら、このステップは不要）
' finalArray = tempArray ' 配列の代入は参照渡しではないので注意
Set finalArray = tempArray ' Variant型配列への代入は参照渡しになる場合があるが、基本は値渡し
' より安全なのは、ループでコピーすること
' ReDim finalArray(tempRow - 1)
' For i = 0 To tempRow - 1
' finalArray(i) = tempArray(i)
' Next i
' メモリ解放
Set tempArray = Nothing
' Set finalArray = Nothing ' 最終的に使用するなら解放しない
Debug.Print "一時配列処理完了。最終配列要素数: " & UBound(finalArray) + 1
End Sub
このテクニックは、特に複雑なデータ処理を伴う場合に効果を発揮する。一時配列で自由な操作を行い、最終的な整形が完了した時点で、目的の配列にコピーすることで、`ReDim Preserve` の頻度を最小限に抑えることができる。
3. コレクションオブジェクトとの使い分け基準
配列の動的な性質を補うものとして、VBAには コレクションオブジェクト が存在する。しかし、両者は明確な得意・不得意があり、混同して使用するとパフォーマンスの低下を招く。
コレクションオブジェクト (`Collection`) の特徴:
- 動的: 要素の追加・削除が容易。`ReDim Preserve` のような処理は不要。
- キーによるアクセス: 要素に文字列のキーを割り当て、高速にアクセスできる。
- オブジェクトの格納: 様々なデータ型（オブジェクトを含む）を格納できる。
- パフォーマンス: 要素数が増加すると、配列に比べてアクセス速度が低下する傾向がある。特に、多数の要素をループで処理する場合、その差は顕著になる。
配列 (`Array`) の特徴:
- 静的（宣言時）/動的（ReDim）: サイズ変更にコストがかかる。
- インデックスによるアクセス: 数値インデックスによる高速アクセスが可能。
- データ型: 特定のデータ型（Variant を含む）に限定される。
- パフォーマンス: 要素数が増加しても、インデックスアクセスであれば比較的安定したパフォーマンスを維持する。
使い分けの基準:
1. データ件数の変動性:
- 件数が頻繁に、かつ大幅に変動し、かつアクセス頻度が低い: コレクションオブジェクトが適している。
- 件数が予測可能、またはある程度まとまった単位で増減し、かつ高速なインデックスアクセスが必要: 配列（事前のサイズ確保、またはバッファリング戦略）が適している。
2. アクセスパターン:
- キーによる検索・取得が頻繁: コレクションオブジェクト。
- 数値インデックスによる高速な順次アクセス: 配列。
3. データ型:
- 異なるデータ型やオブジェクトを混在させたい: コレクションオブジェクト。
- 単一のデータ型で統一できる: 配列の方が一般的にメモリ効率が良い。
4. パフォーマンス要求:
- 極めて高いパフォーマンスが要求され、要素数が数万〜数百万に達する: 事前にサイズを確保した配列、または、VB.NET などのより高度な言語機能（ジェネリックコレクションなど）の検討が必要。
- 一般的な自動化処理で、そこそこのパフォーマンスで十分: コレクションオブジェクトでも問題ない場合が多い。
VB.NET におけるジェネリックコレクションとの比較:
VB.NET では、`List(Of T)` のようなジェネリックコレクションが提供されており、これは VBA の配列とコレクションの利点を併せ持ち、さらに型安全性を高めている。VBA で実現しようとすると、`Collection` にオブジェクトを格納し、各オブジェクトにメソッドを持たせるなどの工夫が必要になるが、パフォーマンスやコードの簡潔さの点では、VB.NET に軍配が上がる。
レガシー環境の保守という観点では、VBA で書かれた既存のコードベースの理解が最優先される。しかし、新規開発や大規模改修においては、VB.NET への移行や、API を介した .NET クラスの利用も視野に入れるべきだろう。
4. レガシー環境とシステム間連携の極限
長年稼働するレガシーシステムでは、VBA のパフォーマンス限界に直面することが少なくない。そのような状況下で、システム全体の安定性を維持し、さらなる機能追加や連携を実現するためには、VBA の internals を深く理解し、リソースを極限まで活用する技術が求められる。
4.1. Windows API を駆使したメモリ管理
VBA は、Windows API を呼び出すことで、より低レベルなシステムリソースにアクセスできる。例えば、メモリの確保や解放、オブジェクトの参照カウント管理などを API 経由で行うことで、VBA の自動ガベージコレクションだけでは達成できないレベルの最適化が可能になる場合がある。
これは非常に高度なテクニックであり、誤ったAPI呼び出しはシステムクラッシュを招く危険性もある。しかし、極限のパフォーマンスチューニングや、VBA の標準機能では対応できないメモリリークの解消など、最終手段として有効な場合がある。
4.2. パフォーマンスプロファイリングとボトルネック特定
複雑なVBAシステムでは、どこがパフォーマンスのボトルネックになっているのかを特定することが困難な場合がある。`Timer` 関数を用いた単純な計測だけでなく、より詳細なプロファイリングツール（VBAには標準で高度なものは少ないが、外部ツールやAPI呼び出しを組み合わせることで実現可能）を用いて、各処理の実行時間、メモリ使用量、API呼び出し回数などを計測し、改善の優先順位を決定する必要がある。
`Debug.Print` を活用し、ループの開始・終了、`ReDim Preserve` の実行タイミングなどで計測値を出力するだけでも、大まかなボトルネックの特定に役立つ。
4.3. システム間連携における配列の役割
Excel VBA が他のシステム（データベース、外部アプリケーション、Webサービスなど）と連携する際、データ交換のフォーマットとして配列が用いられることは多い。
- CSV/テキストファイル: 配列の内容をCSV形式でファイルに出力し、他のシステムで読み込む。
- データベース: SQL の IN句などで、配列の要素をまとめて渡す。
- Web API: JSON や XML の配列形式でデータを送受信する。
これらの連携において、配列のサイズや構造は、通信量や処理時間に直結する。`ReDim Preserve` の多用は、データ送信前の配列構築に無駄な時間をかけ、結果としてシステム全体の処理遅延を引き起こす可能性がある。そのため、連携処理を設計する段階から、配列の効率的な構築方法を検討することが不可欠である。
結論：配列と向き合う真摯な姿勢
`ReDim Preserve` は、VBA における動的なデータ構造の構築を可能にする強力な機能である。しかし、その裏に潜むパフォーマンスコストを理解し、適切に管理することが、真に堅牢で効率的なVBAシステムを構築するための鍵となる。
配列の事前確保、バッファリング戦略、コレクションオブジェクトとの賢明な使い分け。そして、必要であれば Windows API を駆使した低レベルな最適化。これらは、単なるテクニックではない。長年、Excel VBA という言語と真摯に向き合い、その深淵を覗き込んできた者だけが到達できる、技術の本質である。
レガシーシステムは、過去の資産であり、未来への橋渡しでもある。その保守と進化は、我々エンジニアの責務だ。この記事が、読者の皆様にとって、Excel VBA の世界をより深く理解し、より洗練されたコードを書くための一助となれば幸いである。
