エクセルVBAで実行時間を手軽に測定するTimerObjectクラスを作る

Excel・VBA活用術

photo credit: Curtis Gregory Perry Push To Set via photopin (license)

2019.05.042019.05.09

photo credit: Curtis Gregory Perry Push To Set via photopin (license)

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

エクセルVBAで便利なクラスを作るのにハマっておりまして、ここしばらくIEを操作するクラスを作っていたんですね。

以下の記事から10記事くらいに渡ってお伝えしています。

エクセルVBAでIEを操作するもっとも簡単なクラスを作成する方法

エクセルVBAでInternetExplorerを操作してWebスクレイピング…けっこういつも同じ処理を作ります。そんなときには、クラスで部品化が有効です。まずは、IEを操作するもっとも簡単なクラスを作成します。

今回は、ちょっと別の機能として、実行時間を測定するタイマーをクラスで作ってみようと思います。

実行時間を測定するの…よくやりますもんね。

ということで、今回はエクセルVBAで実行時間を測定するのに便利なタイマークラスの作り方をお伝えしていきます。

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

課題とするプログラム
クラスTimerObjectの作成とパブリック変数
コンストラクタでスタート時間をセットする
実行時間をダイアログで表示するメソッド
動作確認とまとめのコード
まとめ
連載目次：エクセルVBAのタイマー＆高速化クラスとアドインを作る

課題とするプログラム

課題とするプログラム、高速化するためのアプローチは以下の記事でも紹介しているものです。

遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法

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

これを、クラス化して使い回しをしやすくしよう！というものです。

そのコードを以下に再掲しますね。

Sub MySub()
                      
    Dim start As Date: start = Time
    
    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
        
    Dim finish As Date: finish = Time
    MsgBox "実行時間は " & Format(finish - start, "nn分ss秒") & " でした", vbInformation + vbOKOnly
    
End Sub

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

では、ちょぴっとずつ作っていきましょう。

まず、クラスモジュールを追加して、その名称をTimerObjectとしましょう。

それで、実行時間を測定するためにスタートとフィニッシュの日時が必要なので、それらをDate型のパブリック変数として宣言してあげます。

Public Start As Date
Public Finish As Date

まずは持つべきデータから定義してあげると、クラスは作りやすいですよね。

いい感じです。

すると、標準モジュールはこうなりますかね。

Sub MySub()
                      
    Dim timerObj As TimerObject: Set timerObj = New TimerObject
    timerObj.Start = Time
    
    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.Finish = Time
    MsgBox "実行時間は " & Format(timerObj.Finish - timerObj.Start, "nn分ss秒") & " でした", vbInformation + vbOKOnly
    
End Sub

むしろ若干コードが増えちゃいましたね。

クラスなのでインスタンス化が必要ですので、3行目はそのインスタンス化と、オブジェクト変数へのセットを行っています。

コンストラクタでスタート時間をセットする

クラスを使用するメリットの一つは、コンストラクタですね。

インスタンス化と同時にデータを投入するなどの処理を行えます。

今回は、スタート時間を取得する処理を、以下のようなコンストラクタに入れてあげます。

Private Sub Class_Initialize()
    Start = Time
End Sub

これで、前述の標準モジュールの4行目はそっくりカットできますね。

実行時間をダイアログで表示するメソッド

続いて、フィニッシュ時間を取得して、実行時間をダイアログで表示する処理をReportTimerメソッドとして作成しました。

Public Sub ReportTimer()
    Finish = Time
    MsgBox "実行時間は " & Format(Finish - Start, "nn分ss秒") & " でした", vbInformation + vbOKOnly
End Sub

これで、標準モジュールの31行目と32行目は

timerObj.ReportTimer

という1行にまとめることができます。

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

まとめのコードはこちらです。

まずは、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

標準モジュールはこちらです。

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

まあ、少し…はスッキリしましたかね。

実行すると、以下のように実行時間が表示されます。