VBAの変数宣言のStaticというオプションがあるのをご存知でしょうか。
私はVBAが好きでブログまで書いているわけですが、正直最近までStaticというものを知りませんでした。
JavaとかC#とかだと、Staticはクラスをインスタンス化せずにアクセスできる変数になりますが、VBAの場合は「ブックを閉じるまで値を保持し続ける変数」になります。
Static変数は値を保持し続ける
例えば、下記のような通常の変数の場合
Sub test()
Dim i As Integer
i = i + 1
MsgBox i
End Sub
testという関数が実行されるたびに、iの値がリセットされて0になるので、MsgBoxに表示される値は常に「1」となります。
一方でStaticな変数を使用した場合
Sub test()
Static i As Integer
i = i + 1
MsgBox i
End Sub
1回目の実行結果

2回目の実行結果

関数を実行するたびに値が加算されていきます。
通常の変数はVBAの実行が完了すると値がリセットされるのですが、Staticな変数は値を保持し続けられるということです。
そして、Excelのブックを閉じると値がリセットされるという動きになります。
Staticな変数を乱用するとバグの温床になる
このStaticについて、ネットで検索を行ったのですが、どのような動きになるかを解説したサイトは多いですが、実際の使用例が殆どありませんでした。
「関数が完了しても値を保持しておきたいのであれば、セルに値を入れておけばいいじゃない」という意見が多かったです。ごもっともすぎる…。
また、Staticな変数を使用すると全く同じ条件で関数を実行しても結果が異なるということになります。
結果として、人間が結果を予測しにくくしてしまうのでバグの温床になってしまう可能性もあります。プログラマ目線では、同じ条件で実行したら毎回同じ結果になってもらわないと不安で仕方ないです。
では、Staticは使用する場面が皆無かと言うと、以下のような状況で使えるかなと思いました。
- 長いループがある関数について途中中断機能をつける
- 設定シートの値などをキャッシュ化する
具体的なコードを記載していきたいと思います。
長いループがある関数について途中中断機能をつける
Sub test()
Dim j As Long
For j = 1 To 1000000
ActiveSheet.Cells(j, 1).Value = "HOGE" & j
Next j
End Sub
上記は現在開いているワークシートの1行目から100万行目に対して「HOGE1」、「HOGE2」、「HOGE3」と入力するプログラムです。
100万回セルの操作が発生するので、長時間の実行になることが予想されます。
仮にVBAを途中で止めてしまった場合は、また最初からになってしまいますがStaticを使用すれば中断したタイミングから再開することができます。
Sub test()
Dim j As Long
Static i As Long
For j = i + 1 To 1000000
ActiveSheet.Cells(j, 1).Value = "HOGE" & j
i = j
If j Mod 10000 = 0 Then Exit Sub
Next j
End Sub
staticな変数「i」に最後に実行したループの「j」が保存されています。
次に、関数を実行した時にj = i+1から始めるようにすれば、実質続きから実行できるということになります。
「セルに値を入れておけばいいじゃない」と言われそうですが、セルの書き込みは時間がかかるデメリットがあるので、こちらのほうが高速だと思います。
とは言え、今回は単純な数値型を保存しておきたかっただけなので、セルに値を保存する方式でも問題なかったです。
テキストを読み込んでExcelに書き出すなどという機能の場合、テキストデータをStaticな変数に入れておくことで読み込み処理を省略したりも出来ると思います。
マスタ値をキャッシュ化する
VBAを動かす上でマスタになるデータが有る場合、その値をStaticな変数に格納しておくことでキャッシュのように使用することができます。
(マスタデータの取得処理を省略できるので高速化出来る)
例えば、以下のような商品マスタを持っているExcelで「りんごといちごの値段の合計を表示するプログラム」を作成してみます。

普通にプログラムを書くとこうなります。
Sub test()
Dim sh As Worksheet
Set sh = Worksheets("商品")
Dim gokei As Long
gokei = 0
Dim i As Long
For i = 1 To sh.Cells(Rows.Count, 1).End(xlUp).Row
If sh.Cells(i, 1).Value = "いちご" Or sh.Cells(i, 1).Value = "りんご" Then
gokei = gokei + sh.Cells(i, 2).Value
End If
Next
MsgBox gokei
End Sub
実行結果

この場合、関数を実行するたびに毎回マスタの値をループさせて値を取得することになります。
仮に、商品シートのマスタ値がほとんど変更されないのであれば無駄極まりないです。そこでStatic変数を使用してキャッシュ化を行います。
Sub test()
Dim sh As Worksheet
Set sh = Worksheets("商品")
Dim gokei As Long
Dim master As Object
Set master = getMaster()
gokei = gokei + master.Item("りんご")
gokei = gokei + master.Item("いちご")
MsgBox gokei
End Sub
Function getMaster() As Object
Static cache As Object
If Not cache Is Nothing Then
Set getMaster = cache
Exit Function
End If
Set cache = CreateObject("Scripting.Dictionary")
Dim sh As Worksheet
Set sh = Worksheets("商品")
Dim i As Long
For i = 1 To sh.Cells(Rows.Count, 1).End(xlUp).Row
cache.Add sh.Cells(i, 1).Value, sh.Cells(i, 2).Value
Next
End Function
実行結果

Static cache As ObjectがNothingの場合のみ、商品シートの値を読み込みます。
2回目の関数実行では既に読み込んだキャッシュの値を使うので高速で実行することができます。
↓既にキャッシュがある場合は、速攻でExit Functionするので処理を相当スキップできる

大量のマスタや設定シートが存在し、更にその値を変更しないVBAツールであれば、これはかなり使えるテクニックだと思います。
コメントを残す