何度も同じことをしない

---

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