VBA マクロの高速化のためのApplication設定をクラスモジュールにまとめる

今回はちょっと変わったクラスモジュールのテクニック案。
異端扱いされそうな気がするので、思いついた私自身、採用には慎重なのだけれど、アイデアとしては面白いと思ったので備忘録として公開してしまうことにした。

さて、過去にPropertyプロシージャを使った高速化テクニックというのをやった。
thom.hateblo.jp

今回も基本的にはApplication設定を変更するだけなのだが、クラスモジュールを使って更に怠慢にやろうという話。

作り方

クラスモジュールを挿入し、オブジェクト名を「OneTimeSpeedBooster」に変更する。
このクラスの名前付けは超重要！！
そもそも馴染みのない異端テクニックなので、何がしたいのか名前で示さないと訳が分からなくなる。

クラスに書くコードはこちら。

Private Sub Class_Initialize()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .PrintCommunication = False
    End With
End Sub

Private Sub Class_Terminate()
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .PrintCommunication = True
    End With
End Sub

見ての通り、コンストラクタとデストラクタしかない。
コンストラクタで高速化設定を行い、デストラクタで標準設定にもどしている。

使い方

OneTimeSpeedBoosterを使う前に、まずは時間のかかるマクロを作ってみる。
計算が必要かつ、画面更新が発生するものが良い。

九九を100×100まで計算させつつ、セルの色をランダムで塗りつぶすようにしてみよう。

コードはこのとおり。

Sub hoge()
    Dim t As Double: t = Timer
    For i = 1 To 100
        For j = 1 To 100
            With Sheet1.Cells(i, j)
                .Value = "=" & i & "*" & j
                .Interior.Color = RandomColor
            End With
        Next
    Next
    Debug.Print Timer - t
End Sub

Function RandomColor()
    RandomColor = RGB( _
        Int(256 * Rnd), _
        Int(256 * Rnd), _
        Int(256 * Rnd))
End Function

完成！
f:id:t-hom:20170319111224p:plain

タイムは、3.7秒。はやっ！

では、ズームで10%まで小さくして、画面に入る描画範囲を増やしてみよう。
f:id:t-hom:20170319111649p:plain

タイムは、9秒。

ではこれがどれだけ早くなるか。
コードは次のとおり。

Sub hoge()
    Dim t As Double: t = Timer
    Dim booster: Set booster = New OneTimeSpeedBooster
    For i = 1 To 100
        For j = 1 To 100
            With Sheet1.Cells(i, j)
                .Value = "=" & i & "*" & j
                .Interior.Color = RandomColor
            End With
        Next
    Next
    Debug.Print Timer - t
End Sub

Function RandomColor()
    RandomColor = RGB( _
        Int(256 * Rnd), _
        Int(256 * Rnd), _
        Int(256 * Rnd))
End Function

タイムは、1.16秒！

9秒から1秒なので実に90%も高速化したことに、、、、なる、、のか。。
っ！計算に自信がない！！

で、肝心のコードだけれど、足したのは以下の1行のみ。
Dim booster: Set booster = New OneTimeSpeedBooster

名前付けが超重要と書いたのは、上手い名前を付けていても変数宣言してセットしたはいいけど何も使われていないアホなコードに見えるのに、まして下手な名前なんてつけたら。。ということ。

OneTimeSpeedBoosterのインスタンスが作られるとコンストラクタによって各種アプリケーション設定が変更され、プロシージャの終了とともにこのbooster変数は破棄されるので、デストラクタによってアプリケーション設定がもとに戻る仕組み。

あるいは、Withを使ってこの区間ブーストするみたいな表記もアリか。

Sub hoge()
    Dim t As Double: t = Timer
    With New OneTimeSpeedBooster
        For i = 1 To 100
            For j = 1 To 100
                With Sheet1.Cells(i, j)
                    .Value = "=" & i & "*" & j
                    .Interior.Color = RandomColor
                End With
            Next
        Next
    End With
    Debug.Print Timer - t
End Sub