photo credit: Melinda * Young The Old Books via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAで実行時間を測定するクラス、処理の高速化をするクラスを作っておりました。
前回は、以下の記事でそれらのクラスをアドイン化して便利に使いまわそう！という内容でお送りしました。
アドインファイルを作るところまでお伝えしたのですが、まだそれを活用する方法をお伝えできていなかったので、本記事はその続編となります。
ということで、エクセルVBAで自作アドインの便利クラスを活用する方法です。
では、行ってみましょう！
前回のおさらい
まずは、お題となる標準モジュールのプロシージャからご覧ください。
こちらです。
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
処理自体はあまり意味がありませんが、２つの便利なクラスの使用例となっています。
まず、実行速度を測定するTimerObjectクラス。
Public Start As Date
Public Finish As Date
Private Sub Class_Initialize()
Start = Time
End Sub
Public Sub ReportTimer()
Finish = Time
MsgBox "実行時間は " & Format(Finish - Start, "nn分ss秒") & " でした", vbInformation + vbOKOnly
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
この２つのクラス…あちこちで使い回しそうなので、アドイン化して使い回しをしやすくしよう！というのが前回からの内容です。
アドインブックの作成や設定などは、前回の以下記事をご覧くださいね。
そして、そのアドインブックにインスタンス生成用のパブリックなFunctionプロシージャを作成する必要がありまして、それはコチラです。
Public Function CreateTimer()
Dim t As TimerObject: Set t = New TimerObject
Set CreateTimer = t
End Function
Public Function CreateBooster()
Dim b As PerformanceBooster: Set b = New PerformanceBooster
Set CreateBooster = b
End Function
…と、ここまでがおさらいです（長）。
今回はこの作成したアドインを活用していく方法を見ていきましょう。
アドインを他のプロジェクトから活用する
では、元のブック「実行速度.xlsm」からアドインを活用できるようにしていきましょう。
クラスモジュールを開放する
まず、「実行速度.xlsm」には、まだ以下のクラスモジュールが存在しています。
- TimerObject
- PerformanceBooster
これら、２つのクラスモジュールは、アドインブックに移設しました。
後ほど参照設定して使えるようになりますので、このブックには存在している必要がありません。
プロジェクトエクスプローラー上で右クリックして「解放」してあげましょう。
アドインを参照設定する
続いて、アドインブックを参照設定していきます。
参照設定の手順は、実はライブラリの参照設定と同じです。
まず、VBEの「ツール」メニューの「参照設定」を選択します。
続いて、以下のように進めますよ。
- 参照設定ダイアログで「参照」ボタンをクリック
- ファイルの参照ダイアログでユーザーアドインフォルダを選択
- ファイル形式を「Microsoft Excel Files」を選択
- 作成したアドインブックを選択
- 「開く」ボタンをクリック
おさらいですが、ユーザーアドインフォルダは以下のようなパスでしたね。
すると、以下のように参照設定ダイアログには「Performance」にチェックが入りますね。
また、プロジェクトエクスプローラーでも「Performance(実行速度.xlam)」の存在が確認（非表示ではありながらもアドインブックが開いている）でき、かつVBAProjectからの参照先として設定されていることがわかります。
アドインのクラスを活用する
では、コードをアドインを活用するものに変更していきましょう。
他のプロジェクトのクラスは「PublicNotCreatable」ですから、Newキーワードでインスタンスを生成できませんでした。
なので、インスタンス生成用のFunctionプロシージャCreateTimerおよびCreateBoosterを使ってあげるんでしたね。
それらに修正したコードがこちらです。
Sub MySub()
Dim timerObj As TimerObject: Set timerObj = CreateTimer
Dim booster As PerformanceBooster: Set booster = CreateBooster
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
3,4行目をちょっと変更するだけでいけますね。
では、実行して動作確認をしてみましょう。
無事に動作し、変わらず5秒という記録を叩き出しましたね。
まとめ
以上、エクセルVBAで自作アドインの便利クラスを活用する方法をお伝えしました。
ちょっと手順はいりますが、いつも使うクラスがあれば今回紹介した２つのクラスに限らずアドインブックに入れておくと良さそうですね。
どうぞご活用ください！
