【VBA】Staticな変数でマスタ値をキャッシュ化する

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ツールであれば、これはかなり使えるテクニックだと思います。


コメントを残す

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)