VBA 遅いマクロの原因を調査する方法

マクロが遅い原因は、だいたいループにある。

以下のようなマクロを実行すると、私の環境ではだいたい5秒くらいかかった。

Sub サンプルマクロ()
    For i = 1 To 9999
        Sheets(1).Cells(1, 1).Value = i
    Next
    
    For j = 1 To 9999
        合計 = 合計 + j
        Sheets(1).Cells(1, 2).Value = 合計
    Next
    
    For k = 1 To 9999
        DoEvents
    Next
End Sub

さて、なぜ遅いのかを調査する前に、実際に何秒かかっているのかを計測してみよう。

Timer関数は、0時0分0秒からの経過時間を正確に返すので、マクロを開始する際にこの値を変数に入れておき、終了時に差分を求めればよい。

Sub サンプルマクロを計測()
    開始時刻 = Timer
    
    For i = 1 To 9999
        Sheets(1).Cells(1, 1).Value = i
    Next
    
    For j = 1 To 9999
        合計 = 合計 + j
        Sheets(1).Cells(1, 2).Value = 合計
    Next
    
    For k = 1 To 9999
        DoEvents
    Next
    
    Debug.Print "経過時間："; Timer - 開始時刻
End Sub

イミディエイトウインドウに「経過時間： 4.90625」と表示された。
だいたい5秒ということで当たっているらしい。

さて、マクロが遅い原因を調べるには、それぞれのループに対して計測をつければよい。

Sub サンプルマクロが遅い原因を調査()
    Debug.Print "ループ1：";
    開始時刻 = Timer
    For i = 1 To 9999
        Sheets(1).Cells(1, 1).Value = i
    Next
    Debug.Print Timer - 開始時刻
    
    
    Debug.Print "ループ2：";
    開始時刻 = Timer
    For j = 1 To 9999
        合計 = 合計 + j
        Sheets(1).Cells(1, 2).Value = 合計
    Next
    Debug.Print Timer - 開始時刻
    
    Debug.Print "ループ3：";
    開始時刻 = Timer
    For k = 1 To 9999
        DoEvents
    Next
    Debug.Print Timer - 開始時刻
    
End Sub