【VBA】WorksheetFunctionは実効速度が速い?

超有名ExcelブログのOfficeTanakaさんのブログで「WorksheetFunction」を使用するとVBAの処理速度が上がるという記事がありました。

これはもう、高速化テクニックでは鉄板です。しかも、劇的な速度向上になります。使わない手はない。それは、VBAからワークシート関数を呼び出すテクニックです。

http://officetanaka.net/excel/vba/speed/s9.htm

この記事を拝見させていただき、「確かにWorksheetFunctionはあまり使わないけど、使えばわかりやすく書けるな」と思いました。

「VBAで書ける部分はVBAで書く」という間違った信念みたいなものを持っていたので、それを見直す良い機会になりました。

一方で「WorksheetFunctionは速い」かと言われると少々疑問に思う部分もあったので、当記事で検証をしてみたいと思います。

優良な記事に対して、粗探しみたいなことをしてしまい申し訳ないですが、「VBAで書く派」としてVBAで書いても遅くないということを証明したいです。

結論から書くと「WorksheetFunctionは速度に優れるというわけではない」です。その理由と検証結果について次項以降に記載します。

Cellsでの値取得をループさせると遅い

記事を拝見すると、数値の合計をする例として以下のようなコードが掲載されていました。

掲載元:http://officetanaka.net/excel/vba/speed/s9.htm

結果は、WorksheetFunction.Sumのほうが圧倒的に速いということでした。実際に自分も試してみたところ

VBAで処理する→323ミリ秒

WorksheetFunctionで処理する→7ミリ秒

という結果になりました。WorksheetFunctionで処理するほうが速いのは火を見るより明らかです。

ですが、VBAの方が10万回ループさせてセルの値を取得している点が気になりました。セルの値を取得するCellsを大量に繰り返すと遅くなるのは知っているので、ここが原因かなと思いました。

値の取得をRangeにすればWorksheetFunctionにも負けない

そこで、以下のコードでも試してみました。

Sub Test1()
    Dim i As Long, A As Long
    Dim arr As Variant
    arr = Range("A1:A100000")
    
    For Each c In arr
        A = c
    Next c
End Sub

このコードですと、セルの値の取得をRange(“A1:A100000”)の1回で行うことができます。

また、WorksheetFunctionを行っている式もRange(“A1:A100000”)で値を取得しているので同等の条件となりました。

この条件で比較をすると…

WorksheetFunctionで処理する→7ミリ秒

VBAで処理する→13ミリ秒

その差がかなり小さくなりました。

それでもWorksheetFunctionのほうが速いという結果ではありますが、For文でCellsを取得していたときと比較すると30分の1以下になっています。

つまりクリティカルに影響を与えているのは、セルの値のとり方であってWorksheetFunctionを使うか否かではないと思いました。

また、SUMではなくCOUNTIFを使用した例も記載されておりましたが、こちらもRange(“A1:A100000”)と取得して処理することでほとんど処理速度は変わらないという結果になると思います。


WorksheetFunctionは積極的に使ったほうがいいと思う

WorksheetFunctionの速度検証については、粗探しをしてケチを付けてしまいましたが、積極的に使用をしていくべきかなと感じました。

何故ならば、コードが圧倒的にわかりやすいからです。

Sub Test1()
    Dim i As Long, A As Long
    Dim arr As Variant
    arr = Range("A1:A100000")
    
    For Each c In arr
        A = c
    Next c
End Sub

Sub Test2()
    A = WorksheetFunction.Sum(Range("A1:A100000"))
End Sub

上記を見ると単純にコードが少ないですし、やっていることもわかりやすいと思います。

デメリットを上げるとすると、以下のような感じでしょうか。

  • 拡張性がない(数値じゃなかったらエラーにするなど)
  • WorksheetFunction自体あまり使っているコードを見ないので、VBA技術者にはわかりにくいかも

メリットのほうが多いと思うので、これからは積極的に使っていきたいなと思いました。


コメントを残す

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)