photo credit: LimpingFrog Productions Expanding our Personal Space via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
前回は以下の記事で実行時間を測定するためのクラスを作成しました。
使い回しもできますし、便利ですよね。
そういえば、前回お題となっていたプロシージャの中に「高速化」をするためのルーチンがありましたよね。
これも、「使い回し」できちゃったほうが便利なのでは…？
ということで、今回はエクセル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関数で表示してくれます。
今回は、上記の以下の部分をクラス化していきます。
- 8~12行目の高速化に関するApplicationクラスの各プロパティの設定をする処理
- 24~28行目の高速化に関するApplicationクラスの各プロパティの設定を戻す処理
各プロパティの機能については以下の記事をご参考くださいね。
ちなみに、白状しておきますと、今回の内容は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
続いて、お待ちかねの標準モジュール。
以下の通り、タイマーと高速化の部分は、２つのインスタンスの生成と、タイマーの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の実行速度を高速化するための処理をクラス化する方法をお伝えしました。
クラスモジュール化しておけば、使い回しが便利ですもんね。
ぜひご活用くださいませ。
次回は作成したクラスを「アドイン化」していきますよ。
どうぞお楽しみに！
