今、ExcelのVBAでちょっとしたものを作ってるのですが、コーディング中に急にパフォーマンスが悪くなるという現象が発生したので、原因を調べてみたらCollectionの各要素を処理する場面が問題のようだった。CollectionをFor Eachで周すようにしただけで、さっきの遅さが嘘のように解決してしまった。
本当にFor i = … のループを For Eachに書き換えただけで速くなるのか確信持てなかったし、他の原因も重なった結果かも、という疑惑も拭いきれない状態だったので、ExcelXPでベンチマークを取ってみました。
主要部分
下のLoop_for_indexとLoop_for_eachを比較しました。
'For i = ...版。 各要素を添字指定でアクセスする Public Sub Loop_for_index() Dim dummy As Long Dim i As Long For i = 1 To list.Count Dim c As EmptyClass Set c = list(i) dummy = dummy + c.dummy Next End Sub 'For Each版。 For Eachで直接アクセスする Public Sub Loop_for_each() Dim dummy As Long Dim c As EmptyClass For Each c In list dummy = dummy + c.dummy Next End Sub |
結果
計測の結果、For Each版は、For i = …版より、100倍高速でした。
For Each版の方が速そうだと思っていたけど、100倍はいくらなんでも差がありすぎなんじゃないか？
For i = …版の書き方に問題があるんでしょうか？
もうちょっと調べてみると、単純に100倍というのは大袈裟で、Collectionの要素数が少ない(1000以下程度)場合は、数倍の差しかありませんでした。
とすると、問題はCollectionの要素が多い場合のFor i = …版の遅さか。
VBA使いの人には、あたり前の話なのかもというのも疑って、「vba for each 高速」で調べてみると、逆のことが書いてあるエントリを見つけて更に混乱してきた。
結論
コレクションに属する個々の要素を列挙する場合には「For～Next ステートメント」の方が「For Each～Next ステートメント」よりも高速である。
■T’sWare Access Labo #18 ～For～NextとFor Each～Nextを比較する～
単純に、「VBAで大量のデータを扱うな」という話なのかもしれない。
以下が、今回計測に使用した全スクリプトです。
EmptyClass
計測で利用するクラス。
Option Explicit Public dummy As Integer |
Timer
ミリ秒で計測するためのモジュール
Option Explicit Private Declare Function timeGetTime Lib "winmm.dll" () As Long Public startTime As Long Sub StopWatchStart() startTime = timeGetTime() End Sub Function StopWatchStop() As Long StopWatchStop = timeGetTime() - startTime End Function |
Module1
ベンチマークスクリプト
Option Explicit Public list As Collection '前処理 データの準備 Public Sub Setup() Set list = New Collection Dim i As Long Dim c As EmptyClass For i = 1 To 10000 Set c = New EmptyClass Call list.Add(c) Next End Sub 'For i = ...版。 各要素を添字指定でアクセスする Public Sub Loop_for_index() Dim dummy As Long Dim i As Long For i = 1 To list.Count Dim c As EmptyClass Set c = list(i) dummy = dummy + c.dummy Next End Sub 'For Each版。 For Eachで直接アクセスする Public Sub Loop_for_each() Dim dummy As Long Dim c As EmptyClass For Each c In list dummy = dummy + c.dummy Next End Sub Public Sub main() Dim i As Integer Call Setup 'Loop_for_index の実行 For i = 1 To 10 Call StopWatchStart Call Loop_for_index Debug.Print "Loop_for_index " & StopWatchStop & "ms" Next 'Loop_for_each の実行 For i = 1 To 10 Call StopWatchStart Call Loop_for_each Debug.Print "Loop_for_each " & StopWatchStop & "ms" Next End Sub |
実行結果
Loop_for_index 329ms Loop_for_index 293ms Loop_for_index 298ms Loop_for_index 294ms Loop_for_index 297ms Loop_for_index 291ms Loop_for_index 294ms Loop_for_index 294ms Loop_for_index 294ms Loop_for_index 296ms Loop_for_each 3ms Loop_for_each 3ms Loop_for_each 3ms Loop_for_each 3ms Loop_for_each 3ms Loop_for_each 2ms Loop_for_each 3ms Loop_for_each 3ms Loop_for_each 3ms Loop_for_each 3ms |
