CollectionはFor文でループすると遅くなる

知らずに落ちると抜け出せなくなるVBAの落とし穴です。

• Collectionのループ処理が遅い
• データ件数が増えたら途端にマクロが遅くなった

あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

Collectionの速度検証

Collectionの全要素をFor文とForEach文でループしてみます。

' For文
Dim x
Dim i As Long
For i = 1 To コレクション.Count
    x = コレクション.Item(i)
Next

' For Each文
Dim x, y
For Each y In コレクション
    x = y
Next

　
データ件数ごとの所要時間はこんな感じになります。

データ件数	For文	ForEach文
1000	0秒	0秒
10000	0.14秒	0秒
50000	3.66秒	0秒
100000	14.7秒	0秒

Collectionと変数のやり取りはメモリ上での処理のため、
数十万件程度であればForEach文のようにほぼ0秒で終わるはずです。

しかしFor文は如実に遅くなっているのがわかりますね。

データが数千件であればあまりに気になる速度ではありませんが、
数万件となるとマクロにかなり影響が出ます。

特に「コレクション(i)」を何度も書くような書き方になっていると、
上記の表をそのまま等倍した処理時間になってしまいます。

コレクションをループする際はForEach文を使用するようにしてください。

CollectionはIndexでアクセスすると遅い

今回の現象をより正確に説明すると、Collectionには
「CollectionをIndexでアクセスすると、後ろの要素になるほど時間がかかる」
という欠点があります。

つまり「コレクション.Item(i)」という記述自体が遅いという訳ですね。

これはCollectionの「なんでも格納できる」仕様に起因しています。

例えば配列(Array)であれば、中に入れる型を先に宣言するため、
「中身がLongならデータ1件4バイト ⇒ 1万番目のデータは4万バイト先」
という風に、n番目のデータがn×バイト先にあるのが簡単に求まります。

しかしCollectionは
「4バイトのLong、8バイトのDouble、文字数によって変わるString」
といったようにバイト数の異なるデータをごちゃまぜに入れることができます。

このためCollectionは「n番目のデータがどこにあるか」を計算で求めることができず、
1件目からn番目にたどり着くまですべてのデータにアクセスする
という方法でしかn番目のデータにアクセスできません。

よってItem(1000)はItem(1)の1000倍時間がかかるという仕様になっており、
データ件数が上がると途端に処理時間が跳ね上がる結果になっています。

データ件数	For文	ForEach文
1000	0秒	0秒
10000	0.14秒	0秒
50000	3.66秒	0秒
100000	14.7秒	0秒

1000番目までループした場合のアクセス回数は1 + 2 + 3 + … + 1000になり、
n(n-1)/2回、つまりnの二乗に比例した時間がかかるわけですね。
（これをO(n²)[オーダーn二乗]と呼んだりします）

確かに表をよく見ると、データが5倍になると25倍の時間がかかり、
データが2倍になると4倍の時間がかかっているのがわかります。

対してForEach文は前の要素のアドレスを記憶してくれているため、
各データ1回ずつのアクセス＝全部合わせてn回でアクセスを終えることができます。

まあこのあたりの理屈は別に知る必要はないと思います。

そもそもFor文よりFor Each文の方が短く簡潔に書けますし、
行番号のようなIndex管理をしなくて済むのがCollectionの良さです。

Collectionをループする際は、
読みやすさと速度を両立できるFor Each文を使用しておきましょう。

検証に使用したソースコード

検証に使用したソースコードを置いておきます。
ご参考ください。

ちなみにCollectionの中身はすべて「0」になっていますが、
文字列にしても、オブジェクトにしても時間に変化はありませんでした。

Sub Collection全要素へのアクセス所要時間_For()

    Dim コレクション As Collection
    Set コレクション = Getデータn件のCollection(50000)

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim x
    Dim i As Long
    For i = 1 To コレクション.Count
        x = コレクション.Item(i)
    Next

    終了時刻 = Timer
    Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"

End Sub

Sub Collection全要素へのアクセス所要時間_ForEach()

    Dim コレクション As Collection
    Set コレクション = Getデータn件のCollection(100000)

    Dim 開始時刻 As Double, 終了時刻 As Double
    開始時刻 = Timer

    Dim x, y
    For Each y In コレクション
        x = y
    Next

    終了時刻 = Timer
    Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"

End Sub

Function Getデータn件のCollection(n As Long) As Collection
    
    Set Getデータn件のCollection = New Collection
    
    Dim i As Long
    For i = 1 To n
        Getデータn件のCollection.Add 0
    Next
    
End Function