伝説のチーフアーキテクトがお届けする、Excel VBAを掌握する極限の知見。
今回は、VBAプログラミングの奥深さを垣間見せてくれる、ちょっと特別な変数のお話だよ。
—
【VBAの深淵へ】Static変数で再帰関数を覚醒させ、処理を爆速化する秘技
こんにちは！業務自動化の最前線でコードと格闘している、あなたの先輩エンジニアです。
Excel VBAの世界へようこそ！「マクロの記録」で便利な自動化を体験した君は、きっと「もっとこうしたい」「もっとスマートにできないか」と、一歩先のVBAの世界に興味を持っていることだろうね。その探求心、素晴らしいよ！
今回は、そんな君が「VBAって奥深いな！」と感動し、さらにスキルアップできるような、ちょっと高度だけど実用的なテクニックを紹介するよ。テーマは 「静的変数（Static）を活用した再帰関数のパフォーマンス向上と状態保持の最適化」。
「再帰関数？」「Static変数？なんだか難しそう…」と感じたかもしれないね。でも大丈夫。この記事を読み終える頃には、その強力な力を手に入れ、まるで魔法のようにVBAコードを高速化できるようになっているはずさ。さあ、一緒にVBAの深淵を覗いてみよう！
—
君は知っているかい？再帰関数という名の「自己参照の魔術」
まず、今回の主役の一人である「再帰関数」について、優しく紐解いていこうか。
再帰関数って、そもそも何？
再帰関数とは、「自分自身を呼び出す関数」 のことだよ。なんだか不思議な感じがするよね？
例えるなら、ロシアのマトリョーシカ人形みたいだ。大きな人形の中に少し小さい人形、その中にまた小さい人形…と、同じ形の人形が次々と現れる。そして、一番小さな人形が出てきたら、そこで終わり。
プログラミングの世界では、「大きな問題を、それより少し小さな同じ問題に分割し、最終的に一番小さな、答えがすぐにわかる問題にたどり着くまで繰り返す」 ような処理によく使われるんだ。
具体的な例として、学校で習った「階乗」を計算する関数を考えてみよう。
5の階乗 (5!) は `5 4 3 2 1` だよね。これは `5 (4!)` とも書ける。さらに `4! = 4 (3!)` … となる。
これをVBAで表現すると、こんな感じになるよ。
‘ // 例1: 素朴な階乗計算の再帰関数
Function Factorial(ByVal n As Long) As Long
‘ // nが1になったら、これ以上分割できないので1を返す（終了条件）
If n <= 1 Then
Factorial = 1
Else
' // 自分自身を呼び出して、n-1の階乗を計算し、nを掛ける
Factorial = n Factorial(n - 1)
End If
End Function
' // 実行例
Sub TestFactorial()
Dim result As Long
result = Factorial(5) ' // 5の階乗を計算
Debug.Print "5! = " & result ' // 結果: 120
result = Factorial(10) ' // 10の階乗を計算
Debug.Print "10! = " & result ' // 結果: 3628800
End Sub
このコードでは、`Factorial`関数が `Factorial(n - 1)` という形で自分自身を呼び出しているのがわかるかな？
`n` が `1` になるまで呼び出しを繰り返し、`1` になったら `1` を返して、そこから逆算していくんだ。
再帰関数の「落とし穴」
再帰関数は、コードを簡潔に書けるというメリットがある反面、使い方を間違えると「無限ループ」に陥ったり、「スタックオーバーフロー」（呼び出しが深くなりすぎてメモリを使い果たすエラー）を起こしたりすることがあるから注意が必要だよ。必ず 「終了条件」 を設定して、いつか再帰が止まるように設計するんだ。
—
再帰関数の「困った」性能問題：フィボナッチ数列を例に
さて、再帰関数はエレガントな反面、特に「計算結果の重複」がパフォーマンスを著しく低下させる場合があるんだ。その典型的な例が フィボナッチ数列 だ。
フィボナッチ数列は、
`F(0) = 0`
`F(1) = 1`
`F(n) = F(n-1) + F(n-2)` (n >= 2)
と定義される数列のこと。つまり、前の2つの数を足し合わせると次の数になる、というものだね。
`0, 1, 1, 2, 3, 5, 8, 13, 21, …`
これを素朴に再帰関数で実装するとこうなるよ。
‘ // 例2: 素朴なフィボナッチ数列の再帰関数（非効率版）
Function Fibonacci(ByVal n As Long) As Long
‘ // 終了条件
If n <= 0 Then
Fibonacci = 0
ElseIf n = 1 Then
Fibonacci = 1
Else
' // 自分自身を2回呼び出す
Fibonacci = Fibonacci(n - 1) + Fibonacci(n - 2)
End If
End Function
' // 実行例
Sub TestFibonacci()
Dim i As Long
Dim startTime As Double
Debug.Print "素朴なフィボナッチ数列の計算:"
' // 少し大きな数で試してみよう
For i = 1 To 10
' // 処理時間を計測してみる
startTime = Timer
Debug.Print "F(" & i & ") = " & Fibonacci(i) & " (" & Format(Timer - startTime, "0.0000") & "秒)"
Next i
' // もっと大きな数だとどうなる？（処理に時間がかかるかも）
' // Debug.Print "F(35) = " & Fibonacci(35) & " (" & Format(Timer - startTime, "0.0000") & "秒)"
' // Debug.Print "F(40) = " & Fibonacci(40) & " (" & Format(Timer - startTime, "0.0000") & "秒)"
End Sub
この`Fibonacci`関数は、`F(n-1)`と`F(n-2)`を計算するために、さらに再帰的に自分自身を呼び出す。これが、特に`n`が大きくなると途方もない数の計算を生み出すんだ。
図で見てみよう。例えば`F(5)`を計算する場合、こんな風に何度も同じ計算が繰り返されているのがわかるかな？
F(5)
/ \
F(4) F(3)
/ \ / \
F(3) F(2) F(2) F(1)
/ \ / \ / \
F(2)F(1)F(1)F(0)F(1)F(0)
/ \
F(1)F(0)
見てわかる通り、`F(3)`や`F(2)`、`F(1)`、`F(0)`といった同じ計算が何度も何度も実行されているよね。これは非常に無駄が多い。特に`F(30)`や`F(40)`といった数を計算させようとすると、処理が重くて使い物にならないレベルになってしまうんだ。
「これ、なんとかしたいよね？」そう思った君は、もう次のステップへ進む準備ができているよ！
---
救世主現る！静的変数（Static）の魔法
この無駄な再計算をなくすための強力な武器が 静的変数（Static） だ。
Static変数って、どんな変数？
VBAで変数を宣言するとき、多くの場合 `Dim` を使うよね。
Sub MyProc()
Dim myNumber As Long ‘ // myNumberはプロシージャが終了すると消える
myNumber = 10
End Sub
`Dim` で宣言された変数は、それが宣言されたプロシージャ（SubやFunction）が終了すると、その値は消えてしまうんだ。つまり、次に同じプロシージャを呼び出しても、`myNumber` は初期値（`Long`型なら`0`）に戻っている。これは「ローカル変数」と呼ばれる、ごく一般的な変数の振る舞いだね。
でも `Static` 変数はちょっと違う。
Sub MyStaticProc()
Static callCount As Long ‘ // callCountはプロシージャが終了しても値が保持される
callCount = callCount + 1
Debug.Print “このプロシージャは ” & callCount & ” 回呼び出されました。”
End Sub
‘ // 実行例
Sub TestMyStaticProc()
MyStaticProc ‘ // 1回目: 1
MyStaticProc ‘ // 2回目: 2
MyStaticProc ‘ // 3回目: 3
End Sub
この例を見てほしい。`MyStaticProc` を何度も呼び出しているのに、`callCount` の値がリセットされず、増え続けているのがわかるかな？
そう、`Static` で宣言された変数は、プロシージャの実行が終わっても、その値がメモリ上に保持され続ける という特徴を持っているんだ。まるで、そのプロシージャだけが持つ、秘密の記憶装置のようなものだね。そして、次に同じプロシージャが呼び出されたときには、前回の値がそのまま残っている状態で処理が始まるんだ。
これは、オブジェクトのライフサイクルとスコープの観点から見ると、非常に特別な存在だ。`Dim` で宣言されたローカル変数が「一時的な作業スペース」なのに対し、`Static` 変数は「プロシージャ専用の、永続的なキャッシュ領域」と考えるといいだろう。
なぜこれが再帰関数のパフォーマンス向上に繋がるの？
勘の良い君ならもう気づいたかもしれないね。
フィボナッチ数列の例で見たように、再帰関数は何度も同じ計算を繰り返して無駄を生み出すことがあった。
もし、一度計算した結果をどこかに「記憶」しておいて、次に同じ計算が必要になったときに、その「記憶」から答えを取り出せたら、どうなるだろう？
そう、計算をスキップして、爆速で答えを返せるようになるんだ！
この「一度計算した結果を記憶しておく」というテクニックを 「メモ化 (Memoization)」 と呼ぶよ。
そして、この「記憶装置」として、プロシージャの呼び出しを超えて値を保持できる `Static` 変数が、まさにうってつけなんだ。
—
実践！Static変数とDictionaryで再帰関数を超高速化する
さあ、いよいよ本丸だ！先ほどの遅かったフィボナッチ数列の再帰関数を、`Static`変数と、ちょっと便利なオブジェクト `Scripting.Dictionary` を使って高速化してみよう。
Scripting.Dictionaryとは？
`Scripting.Dictionary`は、Excel VBAで使えるとても便利なオブジェクトだよ。これは「キー」と「値」のペアでデータを管理する、いわゆる「連想配列」とか「ハッシュマップ」と呼ばれるものなんだ。
イメージとしては、辞書（Dictionary）のように、単語（キー）から意味（値）を素早く見つけ出すことができるようなものだね。
この`Dictionary`を`Static`変数として宣言し、フィボナッチ数列の計算結果をキャッシュする「記憶装置」として使ってみるよ。
‘ // 例3: Static変数とDictionaryで高速化したフィボナッチ数列の再帰関数
‘ // 参照設定: Microsoft Scripting Runtime (もしくはCreateObject)
Function Fibonacci_Optimized(ByVal n As Long) As Long
‘ // Staticで宣言することで、このDictionaryオブジェクトは関数の呼び出しを超えて保持される
‘ // 初回呼び出し時にのみ初期化される
Static cache As Object ‘ // Scripting.Dictionary を保持するためのオブジェクト変数
‘ // Dictionaryオブジェクトがまだ生成されていなければ初期化する
If cache Is Nothing Then
Set cache = CreateObject(“Scripting.Dictionary”)
‘ // 初期値のキャッシュ（F(0)とF(1)は再帰のベースなので、先に登録しておく）
cache.Add 0, 0 ‘ // F(0) = 0
cache.Add 1, 1 ‘ // F(1) = 1
End If
‘ // —————————————————-
‘ // ★メモ化の核心: キャッシュに結果があるかチェック！
‘ // —————————————————-
If cache.Exists(n) Then
‘ // あった！計算せずにキャッシュされた値を返す
Fibonacci_Optimized = cache.Item(n)
Exit Function ‘ // ここで関数を終了する
End If
‘ // —————————————————-
‘ // ★ここから先は、キャッシュにない場合のみ実行される
‘ // —————————————————-
‘ // 終了条件（理論上は上のキャッシュで処理されるが、念のため）
If n <= 1 Then
' // F(0)またはF(1)は既にキャッシュされているので、この分岐は主にテスト用
Fibonacci_Optimized = IIf(n = 0, 0, 1)
Else
' // キャッシュにない場合は、再帰的に計算する
' // ただし、この再帰呼び出しの中でもキャッシュチェックが行われる
Fibonacci_Optimized = Fibonacci_Optimized(n - 1) + Fibonacci_Optimized(n - 2)
End If
' // ----------------------------------------------------
' // ★計算結果をキャッシュに保存する
' // ----------------------------------------------------
' // 計算した結果を、次回の呼び出しのためにDictionaryに保存しておく
If Not cache.Exists(n) Then ' // 念のため重複チェック
cache.Add n, Fibonacci_Optimized
End If
End Function
' // 実行例とパフォーマンス比較
Sub TestFibonacci_Optimized()
Dim i As Long
Dim startTime As Double
Dim endTime As Double
Debug.Print "--- 高速化版フィボナッチ数列の計算 ---"
' // F(0)からF(10)までを高速化版で計算
For i = 0 To 10
startTime = Timer
Debug.Print "F(" & i & ") = " & Fibonacci_Optimized(i) & " (" & Format(Timer - startTime, "0.0000") & "秒)"
Next i
Debug.Print "--- パフォーマンス比較 (F(35)) ---"
' // 素朴な再帰関数のF(35)を測定
' // ※非常に時間がかかる可能性があるため、コメントアウトで提供。
' // 実際に実行する際は、十分注意してください。
' startTime = Timer
' Debug.Print "素朴版 F(35) = " & Fibonacci(35) & " (実行時間: " & Format(Timer - startTime, "0.0000") & "秒)"
' // 高速化版のF(35)を測定
startTime = Timer
Debug.Print "高速化版 F(35) = " & Fibonacci_Optimized(35) & " (実行時間: " & Format(Timer - startTime, "0.0000") & "秒)"
' // 高速化版のF(40)を測定
' // F(40)くらいになると、素朴版では数秒〜数十秒かかる。
' // 高速化版ではミリ秒単位で結果が出る。
startTime = Timer
Debug.Print "高速化版 F(40) = " & Fibonacci_Optimized(40) & " (実行時間: " & Format(Timer - startTime, "0.0000") & "秒)"
' // 高速化版のF(50)を測定
startTime = Timer
Debug.Print "高速化版 F(50) = " & Fibonacci_Optimized(50) & " (実行時間: " & Format(Timer - startTime, "0.0000") & "秒)"
End Sub
コード解説とパフォーマンス比較
この`Fibonacci_Optimized`関数の中身を詳しく見てみよう。
1. `Static cache As Object`:
- `cache`という変数を`Static`として宣言しているね。`Object`型にすることで、どんなオブジェクトでも格納できるようにしているよ。
- この`cache`は、`Fibonacci_Optimized`関数が最初に呼び出されたときに一度だけ初期化され、それ以降は関数の呼び出しが終わっても破棄されずに値（`Scripting.Dictionary`オブジェクト）を保持し続けるんだ。
2. `If cache Is Nothing Then Set cache = CreateObject(“Scripting.Dictionary”)`:
- `cache`がまだ初期化されていない（`Nothing`の状態）場合にだけ、新しく`Scripting.Dictionary`オブジェクトを作成しているよ。
- `CreateObject`を使うことで、事前に「参照設定」をしなくても`Dictionary`オブジェクトを使えるようになるんだ。（`Tools` -> `References`から`Microsoft Scripting Runtime`にチェックを入れることもできるけど、`CreateObject`はより手軽だね。）
- `cache.Add 0, 0` と `cache.Add 1, 1` で、フィボナッチ数列の初期値 `F(0)=0` と `F(1)=1` をあらかじめキャッシュに登録しているよ。これは、再帰の終了条件となるベースケースだね。
3. `If cache.Exists(n) Then … Exit Function`:
- ここがメモ化の最重要ポイント！`cache.Exists(n)`で、引数`n`に対する計算結果が既にキャッシュに存在するかどうかをチェックしているんだ。
- もし存在すれば、`cache.Item(n)`でその値を即座に取り出し、`Fibonacci_Optimized = …` で関数の戻り値として設定し、`Exit Function` でその場ですぐに処理を終了する。
- これにより、同じ`n`に対する計算が二度と行われない んだ！
4. 通常の再帰計算と結果のキャッシュ:
- キャッシュに`n`の結果がなかった場合のみ、`Fibonacci_Optimized = Fibonacci_Optimized(n – 1) + Fibonacci_Optimized(n – 2)` のように、通常の再帰計算を行うよ。
- そして、計算が終わったら、`cache.Add n, Fibonacci_Optimized` で、その計算結果を新しいキー`n`と値`Fibonacci_Optimized`のペアとしてキャッシュに保存している。次に同じ`n`が必要になったときのために、しっかり記憶しておくんだ。
パフォーマンス比較の結果
`TestFibonacci_Optimized`を実行してみると、その差は歴然だ。
素朴な`Fibonacci(35)`は数秒から数十秒かかる（環境による）のに対し、`Fibonacci_Optimized(35)`や`F(40)`、`F(50)`は、わずかミリ秒単位で結果が返ってくる はずだよ。
これは、`Static`変数と`Dictionary`によるメモ化のおかげで、一度計算した結果は二度と計算しない、という効率的な処理が実現できたからなんだ。まさに魔法のように処理が爆速化したのが体感できるだろう。
—
Static変数のもう一つの顔：状態保持の力
`Static`変数の力は、再帰関数のパフォーマンス向上だけにとどまらないよ。プロシージャの状態を保持するという特性は、様々な場面で役立つんだ。
例えば、
- 関数が呼び出された回数をカウントする: 上の`MyStaticProc`の例のように、その関数が何回実行されたかを記録しておきたい場合に使えるね。
- 特定の処理が初回だけ実行されるようにする: 「この処理は、このシートが開かれてから一度だけ実行すればいい」というような場合に、`Static Dim isInitialized As Boolean`のようなフラグを使って制御できる。
- 複雑なオブジェクトのインスタンスを使い回す: `Dictionary`の例のように、初期化に時間のかかるオブジェクトや、複数の呼び出しで共有したいオブジェクトを`Static`で保持し、都度`CreateObject`や`New`で生成する手間と時間を省くことができる。
このように、`Static`変数は「プロシージャが記憶を持つ」ことを可能にし、より柔軟で効率的なコードを書くための強力なツールとなるんだ。
—
Static変数を使う上での注意点と落とし穴
強力なツールには、適切な理解と注意が必要だ。`Static`変数を使う上でのポイントも押さえておこう。
1. スコープの限定性:
- `Static`変数は、宣言されたプロシージャの中だけで有効だよ。他のプロシージャから直接アクセスすることはできないんだ。これは`Dim`で宣言されたローカル変数と同じだね。
- モジュールレベルで値を保持したい場合は、`Private`または`Public`なモジュールレベル変数を使う必要があるよ。
2. メモリ使用量への影響:
- `Static`変数は、プロシージャの実行が終わってもメモリ上に残り続ける。もし、非常に大きなデータ（例えば、巨大な配列やオブジェクト）を`Static`で保持すると、メモリを大量に消費し続ける可能性があるから注意が必要だ。
- 今回の`Dictionary`の例でも、あまりに膨大なキーと値のペアをキャッシュすると、メモリを圧迫する可能性がある。
3. いつ値がリセットされるのか？:
- `Static`変数の値は、通常、VBAプロジェクトがリセットされるときに初期化されるよ。具体的には、
- VBAエディタでコードを変更し、保存したとき（自動コンパイルが発生）。
- `End`ステートメントで実行が強制終了したとき。
- Excelアプリケーション自体を閉じるとき。
- これらの状況以外では、基本的に値は保持され続けるので、デバッグの際にはその挙動を理解しておくことが重要だ。
4. デバッグの難しさ:
- `Static`変数が状態を保持するため、異なるタイミングでプロシージャを呼び出したときに、予期せぬ挙動をすることがある。特に複雑なロジックの中で使う場合は、変数の値を追跡するのが難しくなる場合もあるから、慎重に設計し、テストを行う必要があるよ。
これらの注意点を理解し、適切に`Static`変数を活用することで、君のVBAコードはさらに洗練され、パフォーマンスの高いものになるはずだ。
—
まとめ：Static変数をマスターし、VBAの基本をバッチリにしよう！
今回は、再帰関数のパフォーマンス問題という、少し高度なテーマに挑戦してみたね。
- 再帰関数 は、自分自身を呼び出すことで、問題を小さな問題に分割して解決するエレガントな手法だった。
- しかし、素朴な再帰関数は 重複した計算 を生み出し、パフォーマンスを低下させるという弱点があったね。
- そこで登場したのが `Static`変数 だ！プロシージャの呼び出しを超えて値を保持し続けるその特性を活かし、`Scripting.Dictionary`と組み合わせることで、一度計算した結果を「メモ化」し、再計算を劇的に減らすことができた。
- その結果、フィボナッチ数列のような計算が、まるで魔法のように爆速化 するのを目の当たりにしたはずだ。
- `Static`変数は、再帰関数以外にも、プロシージャの 状態保持 など、様々な場面で活用できる強力なツールであることも学んだね。
`Static`変数は、VBAの奥深さを知る上で非常に重要な概念の一つだよ。オブジェクトのライフサイクルとメモリの挙動を意識しながら使うことで、君のVBAプログラミングの幅は大きく広がるはずだ。
この記事で学んだことを活かして、ぜひ君自身のプロジェクトで`Static`変数を使った高速化や、よりスマートなコードの実現に挑戦してみてほしい。
ここをクリアすれば、Excel VBAの基本はバッチリですよ！さあ、さらなる高みを目指して、VBAの世界を楽しんでいこう！
—
