何度も同じこと言わせるな！

内容にもよりますが、10行のマクロと1000行のマクロでは、普通10行のマクロの方が高速です。それは、行数が少ないからではなく、Excelが実行するコマンドの数が少ないからです。ちょっと乱暴な言い方をすれば、使用するコマンドの数を少なくすればマクロの実行速度を上げることが出来るわけです。

もちろん動作に必要なコードを削ることはできませんが、同じ動作を実現するにしてもコードを簡素化することは可能です。

	Test1	Test2	%
1回目	00:16	00:16	100.0%
2回目	00:16	00:15	93.8%
3回目	00:16	00:16	100.0%
4回目	00:16	00:15	93.8%
5回目	00:16	00:16	100.0%
6回目	00:17	00:16	94.1%
7回目	00:16	00:16	100.0%
8回目	00:16	00:15	93.8%
9回目	00:16	00:15	93.8%
10回目	00:17	00:16	94.1%
平均	00:16	00:16	96.3%

下記のTest1とTest2を計測してみました。それほど大きい差はでませんでしたね。Excel 97の頃は10%～20%の速度アップでしたが、今回の計測では5%前後。ほとんど差がないと言ってもいいでしょう。

Sub Test1()
    Dim i As Integer, j As Integer
    For i = 1 To 300
        For j = 1 To 10
            ActiveWorkbook.Worksheets(1).Range("A1").Value = _
            ActiveWorkbook.Worksheets(1).Range("A1").Value + j
        Next j
    Next i
End Sub

Sub Test2()
    Dim i As Integer, j As Integer
    With ActiveWorkbook.Worksheets(1).Range("A1")
    For i = 1 To 300
        For j = 1 To 10
                .Value = .Value + j
        Next j
    Next i
    End With
End Sub

ActiveWorkbook.Worksheets(1).Range("A1").Valueを分解すると、

(1)ActiveWorkbookプロパティ
(2)Worksheetsプロパティ
(3)Rangeプロパティ
(4)Valueプロパティ

と、4つのプロパティを参照しています。Test1では、さらに4つのプロパティで参照した数値を代入していますので合計8プロパティです。それに対してTest2では、ActiveWorkbookプロパティ、Worksheetsプロパティ、Rangeプロパティの3つをWithステートメントで省略しています。そのWithステートメントは、For Nextステートメントの外に記述している点に留意してください。これなら、For Nextステートメントの内部で実行されるプロパティは2つで済みます。

速度的に大きな差はありませんでしたが、こんなケースでは絶対Withステートメントを使うべきです。コードの可読性が高まり修正や編集が容易になり、同時にアドレスの間違いなど単純なバグの混入も減ります。何より、美しいです。同じプロパティを何度も記述して同じオブジェクトを操作するようなときには、まずWithステートメントの使用を検討してください。

ちなみに、次のようなコードも計測してみました。

Sub Test3()
    Dim i As Integer, j As Integer, myCell As Range
    Set myCell = ActiveWorkbook.Worksheets(1).Range("A1")
    For i = 1 To 300
        For j = 1 To 10
                myCell.Value = myCell.Value + j
        Next j
    Next i
End Sub

	Test1	Test3	%
1回目	00:16	00:20	125.0%
2回目	00:16	00:19	118.8%
3回目	00:16	00:20	125.0%
4回目	00:16	00:20	125.0%
5回目	00:16	00:19	118.8%
6回目	00:17	00:20	117.6%
7回目	00:16	00:19	118.8%
8回目	00:16	00:20	125.0%
9回目	00:16	00:20	125.0%
10回目	00:17	00:20	117.6%
平均	00:16	00:20	121.6%

Test3は、WithステートメントでActiveWorkbook.Worksheets(1).Range("A1")を省略するのではなく、myCellというオブジェクト型変数に格納しています。操作の対象となるオブジェクトが簡単にアクセスできないとき、こうして事前にオブジェクト型変数に格納しておき、以降で使用するのはよくやるテクニックです。

ところが、意外なことに実行速度は逆に遅くなりました。現在のところ、原因はわかりません。

個人的な感覚ですが、何度も同じオブジェクトを操作するとき、Withステートメントで省略するか、それともオブジェクト型変数を使うかは、人によって好みがあるようです。Withステートメント派は、Withの中でさらにWithを使い…とWithステートメントのネストにも抵抗がありません。一方のオブジェクト型変数派は、プロシージャの先頭でとにかくオブジェクト型変数に格納しまくり、いくつかの変数は結局一度も使わない…なんてこともあります。どちらかに固執するのではなく、可読性を考慮して、柔軟に使い分けられるようにしたいものです。速度的には大して違いません。ちなみに私はガチガチのWithステートメント派です(^^;

ちなみに、Withステートメントで操作対象のオブジェクトを省略した場合、メモリ内では自動的にオブジェクト型変数が用意され、その変数にオブジェクトが格納されます。Withステートメント内の「.Value」みたいなオブジェクト省略部分は、コンパイルされるときオブジェクト変数を利用した命令に置き換えられ、End With部分でオブジェクト型変数が解放される仕組みになっています。