t-hom’s diary

主にVBAネタを扱っているブログです。

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

結果は以下のようになった。
ループ1: 3.914063
ループ2: 0.5
ループ3: 0.359375

つまりこの場合、全体の足を引っ張っているのはループ1であることが分った。

このように、どこで時間が取られているのかを細かく調べておくと、最小の労力で劇的に速度改善することも可能である。

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。