VBAでワークシート関数を使うと遅くなることもある

VBAではワークシート関数を使うことができる。ワークシート関数というのはExcelでセルに入力して使う関数のことである。例えば、「Sum」や「CountIf」や「Vlookup」などである。

VBAでワークシート関数を使うと、コードを短くわかりやすくできる。また、ループを記述せずに処理を実現できるので高速化にも資するのだ。

個人的によく使うのは「RandBetween」である。任意の範囲の乱数を取得するコードは、有名だから当然知っているのだが、咄嗟に思い出せないこともあるし、書き間違える可能性もある。だから、「RandBetween」を使ってしまうのだ。

しかし、「RandBetween」に大きな欠点があった。わかりやすく簡単に書けるので便利なのだが、処理速度が遅jいのだ。

次のような2つのコードの処理時間を比べてみよう。

'自作関数
Sub MyRndBetweenTest()
    Const min As Long = 1
    Const max As Long = 100
    Dim arr(1 To 100000) As Long
    Dim i As Long
    Dim start As Double
    Dim finish As Double
    
    Randomize
    start = Timer
    For i = LBound(arr) To UBound(arr)
        arr(i) = Int((max - min + 1) * Rnd + min)
    Next
    finish = Timer
    
    Debug.Print "自作関数 : " & (finish - start)
End Sub

'ワークシート関数
Sub WorkSheetFunctionRndBetweenTest()
    Const min As Long = 1
    Const max As Long = 100
    Dim arr(1 To 100000) As Long
    Dim i As Long
    Dim start As Double
    Dim finish As Double
    
    Randomize
    start = Timer
    For i = LBound(arr) To UBound(arr)
        arr(i) = WorksheetFunction.RandBetween(min, max)
    Next
    finish = Timer
    
    Debug.Print "ワークシート関数 : " & (finish - start)
End Sub

何度か試してみると、自作関数の方は約0.03125で、ワークシート関数の方は約3.68359だった。

実に100倍もの差があるわけだ。もちろん、乱数を10万回も発生させるコードをVBAで書くことはめったにないだろう。1000回程度なら体感的に差がないので気にする必要はないとも言える。

ただし、このような処理速度の差は「RandBetween」に限ったものではない。他にも、ゼロ埋めや奇数判定でも大きな差が出た。

乱数発生、ゼロ埋め、奇数判定などのようにセルにアクセスしない処理に関しては、おそらくワークシート関数よりVBAの方が処理が速い。

そもそも、ワークシート関数は処理が遅いものなのではないか。ただし、ループを省略してセルにアクセスする回数を減らせる場合は、結果的に高速化するのではないだろうか。

ちなみに、ゼロ埋めの場合は、Format関数を使う

Format(12, "0000")

よりも、使わない

Right("0000" & 12, 4)

の方が速かった。