VBAでCollectionのループ方法をFor Eachに書き換えるだけで100倍高速になった

2010/04/22

2012/10/31

今、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

この記事の投稿者最新記事

しめじ(smeghead)

phel-lang でアスキーアート作成ツールを作成しました 2024/11/18
ソースコードの中のビジネスロジック 2024/11/08
🚀PHPのソースコードからPlantUMLのクラス図を出力するツール php-class-diagram v1.4.1 をリリースしました🎉 Property Hooks対応 2024/09/21
純粋なクラス 2024/09/14
phel-lang で小さいプログラムを作る方法 (TDD) 2024/09/12

excel vba

9件のコメント

ap 2011/04/10

1年も前のエントリだけど・・・コメントできないのは文字数制限でしょうか。。

試してみたら、原因が分りました。

・Collectionへのindexアクセスは、index値が大きいほど時間がかかる。・Keyアクセスは、要素数の影響は僅か。・For Eachは、Collectionの要素取得がやたら早い。
ap 2011/04/10

書けた！けど改行が・・・(p_q)ｼｸｼｸ

◆検証：　特定の要素に10000回ずつアクセス。以下、それぞれをループ。

Set c = list(“1”) -> 20.4ms

Set c = list(“10000”) -> 24.0ms

Set c = list(1) -> 8.7ms

Set c = list(10000) -> 601.9ms ?!Σ(ﾟ□ﾟ；)

エントリの実行結果＝index 300msは、9～600msの平均だと分ります。 indexの方が早いのはi=300まで。要素が多い場合はkeyが安定するようです。

更に、For Eachの3msは、list(1)と比べても早く、処理が効率化されているようです。ただ、配列など元々高速なデータでは、余計な内部処理が足を引っ張るのか、For i の方が僅かに早くなります。
smeghead 2011/04/10

apさんありがとうございます。

indexの方が早いのはi=300まで。要素が多い場合はkeyが安定するようです。

Collectionの要素取得の方法の違いによる検証ありがとうございます。

ただ、配列など元々高速なデータでは、余計な内部処理が足を引っ張るのか、For i の方が僅かに早くなります。

ネイティブな配列か、Collectionかの違いが大きく影響してるということですか。>他の人がFor i の方が速いと言っていた件などネイティブな配列の場合の方が、最適化を頑張っているのかもしれません。(使われる頻度が高いという判断から)
ap 2011/04/11

リンク先の”Fields”もデータベース関係ですし、配列同様、もともと最適化されているんでしょうね。高級言語育ちでメモリ上の構造とかには弱いので、作りが見えてくるとスッキリします。いい勉強になりました。
かずな 2012/10/29

CollectionとVariant()で結果が逆だった。 CollectionはLinked Listなのかな？
mua biệt thự 2017/01/20

Fastidious replies in return of this query with solid arguments and explaining everything on the topic of that. biệt thự đà nẵng mua biệt thự đà nẵng bán biệt thự đà nẵng biet thu da nang tìm hiểu biệt thự đà nẵng thông tin biệt thự đà nẵng Biệt thự Furama Đà Nẵng Biệt thự The Sunrise Bay Đà Nẵng biệt thự premier village đà nẵng
95Carmela 2017/08/05

Hi admin, i must say you have hi quality posts here. Your website can go viral. You need initial traffic only. How to get it? Search for; Mertiso’s tips go viral
86Ashli 2017/08/28

I have noticed you don’t monetize your website, don’t waste your traffic, you can earn extra cash every month because you’ve got high quality content. If you want to know how to make extra $$$, search for: best adsense alternative Wrastain’s tools
BestAmy 2018/05/02

I see you don’t monetize your page, don’t waste your traffic, you can earn additional bucks every month because you’ve got hi quality content. If you want to know how to make extra bucks, search for: Ercannou’s essential adsense alternative

コメントする

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。