Excel VBAを掌握する極限の知見：Static変数を用いた「再帰関数」の最適化とキャッシュ戦略
レガシーシステムの保全、あるいはExcelをフロントエンドとした超高速バッチ処理の構築において、VBAの性能限界に直面したエンジニアは数知れない。
特に、階層構造の解析、BOM（部品表）の展開、あるいは複雑な組み合わせ最適化問題において、「再帰関数（Recursive Function）」は強力な武器となる。しかし、無防備な再帰は指数関数的な計算量の爆発（Combinatorial Explosion）を引き起こし、VBAの実行エンジンを容易に沈黙させる。
今回は、VBAのスコープとメモリ管理の裏側を熟知したシニアエンジニア向けに、`Static`変数を用いた再帰のメモ化（Memoization / キャッシュ最適化）の本質を解説する。教科書的なアルゴリズム解説にとどまらず、VBAランタイムの挙動、メモリフットプリント、そして実務で即座に使える極限の最適化コードを提示する。
—
1. なぜ通常の再帰は遅いのか？ —— 計算量の罠とVBAのコスト
再帰関数の典型例として、フィボナッチ数列の計算を考えてみる。
素朴な実装を行うと、以下のようなコードになる。
‘ 【アンチパターン】素朴な再帰関数
Public Function Fib_Naive(ByVal n As Long) As Double
If n <= 1 Then
Fib_Naive = n
Exit Function
End If
' 同じ計算が二重に発生する
Fib_Naive = Fib_Naive(n - 1) + Fib_Naive(n - 2)
End Function
このコードを $N = 40$ で実行すると、処理は数秒〜数十秒でフリーズしたようになる。理由は明確だ。`Fib(38)` と `Fib(37)` を計算するために、その下層で全く同じ `Fib(35)` や `Fib(34)` が何万回も重複して再計算されているからである。計算量は $O(2^N)$ となり、Nの増加に対して爆発的に処理時間が延びる。
VBAにおいて、関数呼び出し（スタックフレームの生成、ローカル変数の領域確保、ポインタの退避）は、C/C++等のネイティブ言語に比べて決して軽量ではない。このオーバーヘッドの大きいVBA環境で再帰を行う場合、「一度計算した結果は二度と計算しない（メモ化）」というアプローチが絶対命題となる。
—
2. `Static`変数を活用したメモ化（キャッシュ）のメカニズム
ここで登場するのが `Static` キーワードである。
VBAの `Static` 変数は、プロシージャの終了後もその値を保持し続ける。これはC言語の `static` ローカル変数や、クラスのプライベートフィールドに近い挙動を示すが、プロシージャスコープに閉じ込められるため、カプセル化の観点からも非常に優れている。
グローバル変数（`Public` や `Private` モジュール変数）を使う手法もあるが、グローバル変数は名前空間を汚染し、意図しない書き換えやマルチインスタンス（アドインの複数読み込み等）におけるバグの温床となる。`Static` 変数であれば、関数のスコープを保ったまま、ライフサイクルをアプリケーション実行中にまで引き延ばすことができる。
これを利用し、計算結果をキャッシュする再帰関数の骨組みは以下のようになる。
Public Function Fib_Optimized(ByVal n As Long) As Double
‘ Staticによるキャッシュストレージ（最大N=100を想定した固定長配列）
Static cache(0 & To 100) As Double
Static initialized As Boolean
‘ 初回実行時の初期化（VBAの数値配列はデフォルトで0初期化されるが明示的に管理）
Dim i As Long
If Not initialized Then
For i = LBound(cache) To UBound(cache)
cache(i) = -1 ‘ 未計算状態を -1 で表現
And Next i
cache(0) = 0
cache(1) = 1
initialized = True
End If
‘ キャッシュヒットした場合は即座に返す（O(1)）
If n >= 0 And n <= 100 Then
If cache(n) <> -1 Then
Fib_Optimized = cache(n)
Exit Function
End If
End If
‘ キャッシュミス：再帰計算を行い、結果をキャッシュに格納してから返す
If n <= 1 Then
Fib_Optimized = n
Else
Fib_Optimized = Fib_Optimized(n - 1) + Fib_Optimized(n - 2)
End If
' 結果をキャッシュに書き込む
If n >= 0 And n <= 100 Then
cache(n) = Fib_Optimized
End If
End Function
この実装により、計算量は $O(N)$ へと劇的に削減される。$N = 40$ での実行時間は、数秒から数ミリ秒以下へと短縮される。
—
3. 実務応用：動的配列とDictionaryを組み合わせた汎用キャッシュ
前述の固定長配列は高速だが、入力値の最大値が事前に分からない場合や、文字列をキーとする複雑なツリー構造（組織図の展開、ファイルパスの走査など）では対応できない。
シニアエンジニアの実装として、「可変長配列によるハッシュ風バケット」あるいは 「Scripting.Dictionary」 を `Static` 変数として保持するパターンを提示する。ここでは、オーバーヘッドを最小限に抑えつつ柔軟性を担保する `Dictionary` 型のキャッシュ戦略を解説する。
> 実務上の注意（Late Binding vs Early Binding）:
> `Scripting.Dictionary` を使用する場合、`CreateObject(“Scripting.Dictionary”)` による遅いバインディング（Late Binding）は、数百万回の再帰ループ内で呼び出すと致命的なパフォーマンス低下を招く。必ず `Early Binding`（参照設定: Microsoft Scripting Runtime）または、どうしても遅延バインディングが避けられない場合はキーのハッシュ化などの工夫が必要となる。
以下は、任意の文字列キーや長整数キーをとる再帰関数における、`Static` + `Dictionary` の極限最適化パターンである。
‘ —————————————————————–
‘ 組織ツリーや依存関係解決を想定した、Dictionaryキャッシュ付き再帰関数
‘ —————————————————————–
Public Function ResolveDependency(ByVal targetKey As String) As String
‘ Static宣言によるDictionaryの永続化
Static dictCache As Object
‘ 遅延初期化（初回のみオブジェクト生成のコストを払う）
If dictCache Is Nothing Then
Set dictCache = CreateObject(“Scripting.Dictionary”)
dictCache.CompareMode = 1 ‘ 0:バイナリ比較, 1:テキスト比較(大文字小文字区別なし)
End If
‘ 1. キャッシュヒットの確認
If dictCache.Exists(targetKey) Then
ResolveDependency = dictCache(targetKey)
Exit Function
End If
‘ ————————————————————-
‘ 2. 実際の重い処理・再帰的探索ロジック（例としてのモック処理）
‘ ————————————————————-
Dim computedResult As String
If targetKey = “ROOT” Then
computedResult = “Base_System”
Else
‘ 例: 依存先を再帰的に解決する架空の関数呼び出し
‘ computedResult = ResolveDependency(GetSubKey(targetKey)) & “_processed”
computedResult = “Resolved_” & targetKey ‘ 簡易表現
End If
‘ 3. キャッシュへの登録
dictCache.Add targetKey, computedResult
ResolveDependency = computedResult
End Function
—
4. チーフアーキテクトが警鐘を鳴らす「メモリリークと状態汚染」の罠
`Static` 変数やモジュールレベル変数をキャッシュとして用いる手法は強力無比であるが、「状態を持つ（Stateful）」が故の重大なリスクが伴う。これを理解していないエンジニアは、現場で不可解なバグ（前回の実行結果が残る、データが混ざるなど）を引き起こす。
① 状態の汚染（State Pollution）
VBAのプロジェクトが実行モードから停止モード（■ボタンやエラーによる中断）に移行しても、モジュールレベルの変数や `Static` 変数のメモリは保持され続ける。もしプログラムが途中で異常終了した場合、キャッシュが中途半端に残った状態で次の処理が走り、データ整合性が破壊される。
対策:
キャッシュをリセットするためのパブリックなクリアプロシージャを必ず用意し、バッチ処理のイニシャライズ時に明示的に呼び出す設計にすること。
Public Sub ClearCalculationCache()
‘ Static変数をリセットするためのフック（※VBAの仕様上、Static変数を外部から直接リセットできないため、
‘ キャッシュ実体をオブジェクトやリセットフラグ経由で管理する設計にするのがプロの作法）
End Sub
より堅牢な設計としては、`Static` 変数に直接データを詰め込むのではなく、「キャッシュ管理用のクラス（Class Module）」のインスタンスをプロシージャ内の `Static` 変数として保持し、クラスのメソッド経由でキャッシュを制御することだ。
‘ — 改善されたカプセル化設計 —
Public Function AdvancedRecursiveFunction(ByVal param As Long) As Double
Static cacheManager As clsMemoizeCache
If cacheManager Is Nothing Then
Set cacheManager = New clsMemoizeCache
End If
If cacheManager.HasKey(param) Then
AdvancedRecursiveFunction = cacheManager.GetVal(param)
Exit Function
End If
‘ 計算処理…
Dim res As Double
res = param 2 ‘ 簡略化
cacheManager.SetVal param, res
AdvancedRecursiveFunction = res
End Function
このようにクラスオブジェクトを `Static` で保持させれば、オブジェクトのライフサイクル管理（`Class_Initialize` / `Class_Terminate`）やキャッシュのクリア処理（`cacheManager.Clear`）を綺麗にカプセル化できる。
—
5. まとめ：VBAの限界を突破するエンジニアリング
VBAは、しばしば「おもちゃの言語」と揶揄される。しかし、それは言語の仕様を表面的なリファレンス程度にしか理解せず、メモリ管理やコールスタックのコスト、アルゴリズムの計算量を意識していないプログラマの言い訳に過ぎない。
今回解説した `Static` 変数による再帰の最適化（メモ化）は、以下の極限的メリットをもたらす。
1. 計算量の劇的改善: $O(2^N)$ や $O(N^2)$ の指数・多項式オーダーを $O(N)$ へ抑制。
2. スコープの安全性: グローバル変数による名前空間の汚染を防ぎ、関数内にキャッシュをカプセル化。
3. 実用的な速度の獲得: Excel VBAであっても、C#やPythonの素朴な実装に匹敵する高速なデータ処理・ツリー走査を実現。
現場のシステム管理者やシニアエンジニアに求められるのは、制約だらけの環境で言い訳をすることではなく、アーキテクチャの知見を総動員して限界を突破することだ。この知見をあなたのVBAコードベースに組み込み、圧倒的なパフォーマンスを体感してほしい。
