エクセルVBAの実行速度を高速化するための処理をクラス化する方法


booster

photo credit: LimpingFrog Productions Expanding our Personal Space via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

前回は以下の記事で実行時間を測定するためのクラスを作成しました。

エクセルVBAで実行時間を手軽に測定するTimerObjectクラスを作る
エクセルVBAでは使い回しをするような機能をクラス化しておくと便利ですよね。今回は、使用頻度が高い機能ということで、エクセルVBAで実行時間を測定するのに便利なタイマークラスの作り方をお伝えしていきます。

使い回しもできますし、便利ですよね。

そういえば、前回お題となっていたプロシージャの中に「高速化」をするためのルーチンがありましたよね。

これも、「使い回し」できちゃったほうが便利なのでは…?

ということで、今回はエクセルVBAの実行速度を高速化するための処理をクラス化する方法をお伝えしますよ。

では、行ってみましょう!

スポンサーリンク

課題とするプログラム

では、課題とするプログラムから確認していきましょう。

前回、標準モジュールに作成したプロシージャで、こちらになります。

Sub MySub()
                      
    Dim timerObj As TimerObject: Set timerObj = New TimerObject
    
    Sheet1.Cells.Clear
    Sheet2.Cells.Clear
    
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    With Sheet1
        Dim i As Long
        For i = 1 To 300
            .Cells(i, 1).Value = i
            .Cells(i, 2).FormulaLocal = "=SUM(A1:A" & i & ")"
            .Rows(i).Copy
            Sheet2.Cells(i, 1).PasteSpecial
        Next i
    End With
        
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
            
    timerObj.ReportTimer
    
End Sub

TimerObjというのが、実行時間を測定する用のクラスですね。

インスタンスを生成した時点から測定を開始して、ReportTimerを実行した時点までの実行時間をMsgBox関数で表示してくれます。

今回は、上記の以下の部分をクラス化していきます。

  1. 8~12行目の高速化に関するApplicationクラスの各プロパティの設定をする処理
  2. 24~28行目の高速化に関するApplicationクラスの各プロパティの設定を戻す処理

各プロパティの機能については以下の記事をご参考くださいね。

遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法
エクセルVBAの実行速度が遅い・重いって時ありませんか?そんな時にエクセルVBAのプログラムの処理速度を速くするテクニックを紹介します。プログラムの実行時間を測定する方法も合わせてお伝えします。

ちなみに、白状しておきますと、今回の内容はthomさんの以下の記事をたいへん参考にさせていただいております。

クラスをはじめ、VBAについてとても深くてためになる記事を、たくさん提供いただいていますので、この記事以外にもぜひご覧いただければと思います。

クラスPerformanceBoosterの作成とパブリック変数

では、作っていきます。

まずクラスモジュールを挿入して、名称をPerformanceBoosterとします。

持つべきデータを定義しておきたいですが、今回は実行前のCalculationプロパティの値を保存しておくための、initCalculationValue_というプライベート変数だけ用意しておきましょう。

Private initCalculationValue_ As XlCalculation

Calculationプロパティの値は、列挙型XlCalculationのメンバーになりますので、それを型に設定してあげてます。

その他のプロパティも初期値を保存しておかなくて良いのか?って話なのですが、他のプロパティはFalseのまま使用することはあんまりないかな~ということで割愛してます。

コンストラクタで各プロパティを設定する

クラスPerformanceBoosterですが、使用するにはインスタンスを生成する必要があります。

コンストラクタを使えば、生成したと同時に、Calculationプロパティの初期値を保存すること、また各プロパティを高速モードに設定することができますね。

以下のように作成してあげます。

Private Sub Class_Initialize()

    With Application
        initCalculationValue_ = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

End Sub

いい感じです。

デストラクタでプロパティを元に戻す

前回作成したタイマークラスの場合は、欲しいタイミングでレポートしたかったので、それ用のメソッドを作りました。

ただ、今回の場合は、設定を戻すのはいつでも良いので、プロシージャの実行終了時、クラスPerformanceBoosterが破棄されるときに実行してあげれば良いかも…

そうです、デストラクタですね。

Private Sub Class_Terminate()

    With Application
        .Calculation = initCalculationValue_
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

設定を戻すだけなので、中身は簡単です。

動作確認とまとめのコード

まとめのコードは掲載しておきますね。

まずは、クラスPerformanceBoosterのコードです。

Private initCalculationValue_ As XlCalculation

Private Sub Class_Initialize()

    With Application
        initCalculationValue_ = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

End Sub

Private Sub Class_Terminate()

    With Application
        .Calculation = initCalculationValue_
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

続いて、お待ちかねの標準モジュール。

以下の通り、タイマーと高速化の部分は、2つのインスタンスの生成と、タイマーのReportTimerメソッドのたったの3行にまとめることができています。

Sub MySub()
                      
    Dim timerObj As TimerObject: Set timerObj = New TimerObject
    Dim booster As PerformanceBooster: Set booster = New PerformanceBooster
       
    Sheet1.Cells.Clear
    Sheet2.Cells.Clear
            
    With Sheet1
        Dim i As Long
        For i = 1 To 300
            .Cells(i, 1).Value = i
            .Cells(i, 2).FormulaLocal = "=SUM(A1:A" & i & ")"
            .Rows(i).Copy
            Sheet2.Cells(i, 1).PasteSpecial
        Next i
    End With
                    
    timerObj.ReportTimer
    
End Sub

超すっきりですね。

実行すると、以前と同様5秒という記録を叩きました。問題なさそうですね。

エクセルVBAで実行時間を高速化した結果

まとめ

以上、エクセルVBAの実行速度を高速化するための処理をクラス化する方法をお伝えしました。

クラスモジュール化しておけば、使い回しが便利ですもんね。

ぜひご活用くださいませ。

次回は作成したクラスを「アドイン化」していきますよ。

便利なクラスを使い回す!エクセルVBAで自作クラスをアドイン化する方法
便利なクラスはあちこちで使い回ししたいですよね。「アドイン」という機能を使うと、参照設定のみで使えるようになります。今回は便利なクラスを使い回す!エクセルVBAで自作クラスをアドイン化する方法をお伝えします。

どうぞお楽しみに!

連載目次:エクセルVBAのタイマー&高速化クラスとアドインを作る

エクセルVBAで実行時間を測定するタイマークラス、また処理速度を高速化するブースタークラスを作成し、それを使い回ししやすいようにアドイン化する方法をお伝えします。
  1. エクセルVBAで実行時間を手軽に測定するTimerObjectクラスを作る
  2. エクセルVBAの実行速度を高速化するための処理をクラス化する方法
  3. 便利なクラスを使い回す!エクセルVBAで自作クラスをアドイン化する方法
  4. エクセルVBAで自作アドインの便利クラスを活用する方法

タイトルとURLをコピーしました