【速度対決】1を100万回足してみる【VBA】

　1を100万回足すコードを少しずつ書き換えて速度比較をしてみました。

　セルのA1:A10000に入力された数字を全て合計する処理を100回繰り返しています。

Public StartTime As Double
Public EndTime As Double

Sub TimerStart()
    StartTime = Timer
End Sub

Sub TimerStop()
    EndTime = Timer
    MsgBox (EndTime - StartTime & " 秒")
End Sub

Sub Syokika()
    [a1:a10000].Clear
    [a1:a10000] = 1
End Sub

Sub Sample01()
    Call Syokika
    Call TimerStart         '---- 測定開始
    For i = 1 To 100
        For Each a In [a1:a10000] '<-- この書き方がNG
            ans = ans + a
        Next
    Next i
    Call TimerStop          '---- 約7秒
    MsgBox Format(ans, "#,###")
End Sub

Sub Sample02()
    Call Syokika
    Call TimerStart         '---- 測定開始
    For i = 1 To 100
        '-- 代入を使うだけで速度が15倍
        x = [a1:a10000]
        For Each a In x
            ans = ans + a
        Next
    Next i
    Call TimerStop          '---- 約0.45秒
    MsgBox Format(ans, "#,###")
End Sub

Sub Sample03()
    Call Syokika
    Call TimerStart         '---- 測定開始
    '-- 代入を繰り返し文の外に出すとさらに速くなる
    x = [a1:a10000]
    For i = 1 To 100
        For Each a In x
            ans = ans + a
        Next
    Next i
    Call TimerStop          '---- 約0.24秒
    MsgBox Format(ans, "#,###")
End Sub

Sub Sample04()
    Call Syokika
    Call TimerStart         '---- 測定開始
    Set wf = Application.WorksheetFunction
    For i = 1 To 100
        '-- さらにワークシート関数を使ってみる
        ans = ans + wf.Sum([a1:a10000])
    Next i
    Call TimerStop          '---- 約0.045秒!!!
    MsgBox Format(ans, "#,###")
End Sub

Sub Sample05()
    Call Syokika
    Call TimerStart         '---- 測定開始
    Set wf = Application.WorksheetFunction
    '-- さらに配列に代入してみる
    x = [a1:a10000]
    For i = 1 To 100
        ans = ans + wf.Sum(x)
    Next i
    Call TimerStop          '---- 約0.3秒
                            '遅なっとるやんけ＿|￣|○
    MsgBox Format(ans, "#,###")
End Sub

Sub Sample06()
    Call Syokika
    Call TimerStart         '---- 測定開始
    Set wf = Application.WorksheetFunction
    '-- オブジェクトを代入
    Set x = [a1:a10000]
    For i = 1 To 100
        ans = ans + wf.Sum(x)
    Next i
    Call TimerStop          '---- 約0.045秒!!!
    MsgBox Format(ans, "#,###")
End Sub

Sub Sample07()
    Call Syokika
    Call TimerStart         '---- 測定開始
    Set wf = Application.WorksheetFunction
    '-- あるいはアドレスを文字列で代入
    s = "a1:a10000"
    For i = 1 To 100
        ans = ans + wf.Sum(Range(s))
    Next i
    Call TimerStop          '---- 約0.045秒!!!
    MsgBox Format(ans, "#,###")
End Sub

　Sample02とSample03の違いはコードを書いている段階ですぐに気付きそうですが、Sample01のようなコードは僕なんかはついつい気付かずに書いてしまいそうです。

　Sample04はご存知Excel VBA最強のHuck。ワークシート関数の速さは圧倒的です。

　じゃあ配列に代入したSample05ならどうよ？と考えたのですが、逆に遅くなりました。「セルの内容を配列に格納して処理」と、「ワークシート関数」を組み合わせれば最強コンビになるかと思えば然に非ず。(あと、Sample05はExcel2002だと動きましたが、Excel2000だとエラーが出ました。)

　以上のことから、ワークシート関数を活用しつつ範囲を動的に変えたい場合はSample06 or 07のように書けば良い事になります。