何度も同じことをしない


内容にもよりますが、10行のマクロと1000行のマクロでは、普通10行のマクロの方が高速です。それは、行数が少ないからではなく、Excelが実行するコマンドの数が少ないからです。ちょっと乱暴な言い方をすれば、使用するプロパティやメソッドの数を少なくすればマクロの実行速度を上げられます。もちろん動作に必要なコードを削ることはできませんが、同じ動作を実現するにしてもコードを簡素化することは可能です。

ここでは、次のコードを検証します。

Sub Test1()
    Dim i As Long
    For i = 1 To 5000
        Workbooks("Sample.xlsx").Sheets("Sheet1").Cells(i, 1) = _
            Workbooks("Sample.xlsx").Sheets("Sheet1").Cells(i, 2) + _
            Workbooks("Sample.xlsx").Sheets("Sheet1").Cells(i, 3)
    Next i
End Sub
Sub Test2()
    Dim i As Long
    With Workbooks("Sample.xlsx").Sheets("Sheet1")
        For i = 1 To 5000
            .Cells(i, 1) = .Cells(i, 2) + .Cells(i, 3)
        Next i
    End With
End Sub
test1 test2 %
1 2.890 2.329 80.6%
2 2.907 2.312 79.5%
3 2.922 2.297 78.6%
4 2.906 2.656 91.4%
5 3.094 3.359 108.6%
6 2.875 2.313 80.5%
7 2.875 2.281 79.3%
8 2.875 2.328 81.0%
9 2.875 2.297 79.9%
10 3.172 2.328 73.4%
平均 2.939 2.450 83.4%

あれ?あんまし速くなりませんね。むかし古い(スペックの低い)PCで試したときは、もう少し差が出たような気がするんですけど。ちなみに、Withステートメントで対象(オブジェクト)をくくると、VBAはコンパイルするとき内部でオブジェクト変数に入れます。念のため、オブジェクト変数に入れるコードも試してみましょう。

Sub Test3()
    Dim i As Long, S As Worksheet
    Set S = Workbooks("Sample.xlsx").Sheets("Sheet1")
    For i = 1 To 5000
        S.Cells(i, 1) = S.Cells(i, 2) + S.Cells(i, 3)
    Next i
End Sub
test1 test3 %
1 2.890 1.875 64.9%
2 2.907 1.859 63.9%
3 2.922 1.875 64.2%
4 2.906 1.875 64.5%
5 3.094 1.860 60.1%
6 2.875 2.156 75.0%
7 2.875 1.875 65.2%
8 2.875 1.906 66.3%
9 2.875 1.859 64.7%
10 3.172 1.875 59.1%
平均 2.939 1.902 64.7%

おお、Withステートメントよりもオブジェクト変数の方が、じゃっかんですけど速いですね。両者を比べてみましょう。

Withステートメント オブジェクト変数 %
1 2.329 1.875 80.5%
2 2.312 1.859 80.4%
3 2.297 1.875 81.6%
4 2.656 1.875 70.6%
5 3.359 1.860 55.4%
6 2.313 2.156 93.2%
7 2.281 1.875 82.2%
8 2.328 1.906 81.9%
9 2.297 1.859 80.9%
10 2.328 1.875 80.5%
平均 2.450 1.902 77.6%

確かに、Withステートメントよりも、オブジェクト変数の方が少し速いですけど、ここは一概には言えませんね。両者の違いは、マクロの速度だけでなく、可読性にも大きく影響します。速度差はそれほど大きくありませんから、読みやすさやメンテナンスのしやすさを優先すべきでしょう。