【Excel VBA】再帰関数の限界を超える：Static変数キャッシュによる計算量劇的削減の極意
開発現場でよく見かける光景がある。
「階乗の計算」「組織ツリーの展開」「複雑な依存関係の解決」——こうしたアルゴリズムを実装する際、美しさを優先して素朴な再帰関数（Recursive Function）を書いたはいいものの、データ数が数千件を超えた途端にVBEがフリーズし、タスクマネージャーから強制終了するハメになる。
君は、なぜそのコードが重いのかをロジカルに説明できるか？
そして、「再帰の美しさ」を保ったまま、O(2^n) の爆発的な計算量を O(n) へと叩き落とす最適化手法を知っているか？
今回は、Excel VBAのメモリ構造とスコープの挙動を知り尽くしたアーキテクトだけが知る、「Static変数を用いたキャッシュ戦略（メモ化）」の実務的な実装パターンを伝授する。
—
1. なぜ「素朴な再帰関数」は実務で使い物にならないのか？
まずは、業務システムでよくある「フィボナッチ数列（あるいはそれに類する依存関係の計算）」を例に取ろう。
‘ 【アンチパターン】素朴な再帰関数
Function GetFibonacci_Bad(ByVal n As Long) As Double
If n <= 1 Then
GetFibonacci_Bad = n
Exit Function
End If
' ここで同じ計算が何万回も重複して発生する（指数関数的爆発）
GetFibonacci_Bad = GetFibonacci_Bad(n - 1) + GetFibonacci_Bad(n - 2)
End Function
このコードの何が問題か。
`n = 40` を指定した瞬間、VBAの実行エンジンは数億回の関数呼び出しと、全く同じ引数に対する重複計算を延々と繰り返す。スタック領域は圧迫され、CPU使用率は跳ね上がり、ユーザーは「Excelが固まった」と嘆くことになる。
再帰処理はコードの記述量が減り、複雑な階層構造を表現する際には強力だが、「過去に計算した結果を一切覚えていない」という致命的な欠点（無駄の重複）を抱えている。
—
2. 解決策：Static変数による「メモ化（Memoization）」
この無駄を根絶するのが、「メモ化（Memoization）」というテクニックだ。
そして、VBAにおいてグローバル汚染（Moduleレベル変数によるカプセル化の破壊）を避けて、関数内部に安全かつ高速なキャッシュ機構を持たせる唯一にして最良の手段が、`Static` キーワードである。
Static変数の真価
VBAの通常のローカル変数は、プロシージャ（関数）が終了した瞬間にメモリから消去される。
しかし、`Static` で宣言された変数は、VBAプロジェクトがリセット（エンドボタンや致命的なエラー）されるまで、その値を保持し続ける。
これを利用し、「過去に計算した結果をDictionaryなどのハッシュマップ、あるいは配列に蓄積し、2回目以降の同じ計算要求には瞬時にそれを返す」という構造を作る。
—
3. 【プロダクションコード】コピペで使える堅牢なメモ化再帰
実務の現場では、エラーハンドリング、型の安全性、そして無限ループやメモリリークを防ぐための設計が不可欠である。以下のコードは、数万件規模のデータ処理でも一瞬で完了する、実戦投入仕様のキャッシュ付き再帰関数だ。
今回は、業務データやツリー構造の探索で頻出する「重い処理のシミュレーション」を兼ねた計算を例にする。
Option Explicit
‘ =================================================================================
‘ 模範コード：Static変数とDictionaryを用いたキャッシュ付き再帰関数
‘ =================================================================================
Public Sub RunOptimizedRecursiveDemo()
‘ 処理時間計測用
Dim startTime As Double
startTime = Timer
‘ テストとして第35項を計算（素朴な方法なら数秒〜フリーズする領域）
Dim targetN As Long
targetN = 35
‘ キャッシュをクリアするため、初回実行前にリセット処理を挟む
‘ （※Static変数を初期化するトリガーを設けるのがプロの設計）
Call GetFibonacci_Optimized(-1)
Dim result As Double
result = GetFibonacci_Optimized(targetN)
Debug.Print “計算結果 (n=” & targetN & “): ” & result
Debug.Print “処理時間: ” & Format(Timer – startTime, “0.000秒”)
End Sub
Private Function GetFibonacci_Optimized(ByVal n As Long) As Double
‘ Static宣言により、関数スコープを維持したまま「メモリ（キャッシュ）」を保持する
Static cache As Object
‘ — 1. キャッシュの初期化（初回呼び出し時、またはリセット時） —
If cache Is Nothing Then
Set cache = CreateObject(“Scripting.Dictionary”)
End If
‘ リセットシグナル（n = -1 が渡されたらキャッシュをクリアして終了）
If n = -1 Then
cache.RemoveAll
Exit Function
End If
‘ — 2. 終了条件（基底ケース） —
If n <= 1 Then
GetFibonacci_Optimized = CDbl(n)
Exit Function
End If
' --- 3. キャッシュヒットの確認 ---
If cache.Exists(n) Then
GetFibonacci_Optimized = cache(n)
Exit Function
End If
' --- 4. 再帰計算とキャッシュへの保存（メモ化） ---
Dim calcResult As Double
calcResult = GetFibonacci_Optimized(n - 1) + GetFibonacci_Optimized(n - 2)
' 次回のために結果を保存
cache.Add n, calcResult
GetFibonacci_Optimized = calcResult
End Function
---
4. チーフアーキテクトが教える、実務投入時の重要な注意点
この手法は極めて強力だが、VBAという言語特性上、以下の「罠」に注意しなければならない。
① Static変数のライフサイクルと「VBAの強制リセット」
Static変数は非常に便利だが、ユーザーがVBAコード内で未処理のエラーを起こして「デバッグ -> リセット（■ボタン）」を押したり、コードを修正してコンパイルし直したりすると、保持していたキャッシュはすべて消失する。
そのため、上記コードのように `-1` などの特殊な引数を渡すか、あるいは明示的な初期化トリガーを用意し、バッチ処理の開始時には必ずキャッシュをクリアする設計にすること。
② 大規模データにおけるメモリ消費（メモリリークの防止）
`Scripting.Dictionary` は非常に高速だが、キャッシュしすぎるとExcelのメモリを圧迫する。
もし数百万件レベルの計算を行う場合は、Dictionaryの肥大化を防ぐために、処理が終わった段階で確実にキャッシュを解放する（あるいはモジュールレベルの破棄タイミングを制御する）こと。
③ ファイルやデータベース連携時のアプローチ
このメモ化の思想は、Excel上の計算だけに留まらない。
- CSVや外部データベースからのマスタ参照
- 重いREST APIのレスポンス
これらを何度もVBA側から取得し直すループ処理を書いているエンジニアが散見されるが、これも全く同じだ。関数内Static変数（あるいはDictionary）に一度取得したデータをキャッシュしておけば、ネットワークI/OやディスクI/Oのボトルネックを一撃で解消できる。
—
5. まとめ
業務自動化ツールを作る上で、最も避けるべきは「ユーザーを待たせること」だ。
「再帰処理だから遅いのは仕方ない」というのは、アルゴリズムの設計を放棄したプログラマの言い訳に過ぎない。
- 再帰の構造でロジックを美しく簡潔に記述する。
- `Static` 変数と `Scripting.Dictionary` を組み合わせて、計算結果を二度と逃さない。
この2つをマスターした君のコードは、明日から見違えるほどのパフォーマンスを発揮するだろう。現場のエンジニアとして、圧倒的な処理速度を誇るロバストなツールを作り上げてほしい。
