Excel VBA検証① 【配列 vs Collection】

配列(Array)とCollectionはどのように使い分けていますか？
ざっくりとした印象はこんな感じではないでしょうか？

• Collectionはメソッドが豊富で扱いやすいが、重い
• 配列は軽くて速いが、途中でサイズ変更が発生した時など扱いづらい

実際に配列はどれくらい軽くて速いのか、逆にCollectionはどれだけ重くて遅いのか、ベンチマークしてみました。

時間の計測・使用メモリの計測は以下サイトの内容を参考にしました。

時間の計測(マイクロ秒)
https://stackoverflow.com/questions/7103552/benchmarking-vba-code

メモリ使用量
https://stackoverflow.com/questions/47155175/how-to-read-the-amount-of-used-or-free-memory-in-excel-2016-vba

検証手順

1. 100万個要素を作成
2. 全要素をループ
3. 特定のindexの要素を取得

※配列はあらかじめ100万個分の領域を作っておく場合とReDim Preserveで逐次領域を広げるパターンを各々試してみます。

100万個要素を作成

固定配列(arrFixed)

あらかじめ配列要素数分を用意します。※以降arrFixedはこの方法で作成した配列を指します。

    Dim arrFixed(999999) As Long
    Dim i As Long
    For i = 1 To 1000000
        arrFixed(i - 1) = i
    Next i

• メモリ使用：増加せず
• 時間：0.07秒

動的配列(arrDynamic)

空の配列を用意して、値の入力時に随時領域を拡張します。※以降arrDynamicはこの方法で作成した配列を指します。

    Dim arrDynamic() As Long
    Dim i As Long
    For i = 1 To 1000000
        ReDim Preserve arrDynamic(i - 1)
        arrDynamic(i - 1) = i
    Next i

• メモリ使用：増加せず
• 時間：1.14秒

Collection(col)

配列ではなく、Collectionを使用します。※以降colはこの方法で作成したcollectionを指します。

    Dim col As Collection: Set col = New Collection
    Dim i As Long
    For i = 1 To 1000000
        col.Add i
    Next i

• メモリ使用：61Mbyte
• 時間：0.21秒

所感

• 100万回サイズ変更(ReDim Preserve)を行っている割にはarrDynamicの要素追加は高速だったように思いました
• Collectionは配列と比べて若干メモリを食うようですが、100万個の要素追加の割にはマイルドな印象を受けました

全要素をループ

固定配列(arrFixed)

    Dim item As Variant
    Dim v As Long
    For Each item In arrFixed
        v = item
    Next item

• 時間：0.06秒

動的配列(arrDynamic)

    Dim item As Variant
    Dim v As Long
    For Each item In arrDynamic
        v = item
    Next item

• 時間：0.11秒

Collection(col)

    Dim item As Variant
    Dim v As Long
    For Each item In col
        v = item
    Next

• 時間：0.08秒

所感

• どれも高速ですね、どれ使っても問題無し

index指定で要素を取得

100万個中5000個の要素をindex指定で取得します。

固定配列(arrFixed)

    For i = 1 To 5000
        v = arrFixed(i * 200 - 1)
    Next i

• 時間：0.03秒

動的配列(arrDynamic)

    For i = 1 To 5000
        v = arrDynamic(i * 200)
    Next i

• 時間：0.04秒

Collection(col)

    For i = 1 To 5000
        v = col.item(i * 200)
    Next i

• 時間：67.08秒

所感

• Collectionのindex指定での要素取得は遅い！！※深堀り検証は後述

Collectionのindex指定が遅すぎる？

Collectionのindex指定はなぜ遅いのでしょうか？
特に大きなindexを指定すると顕著に遅くなるようです。
5000個ずつ取得する位置を後ろにずらしていくと、どのくらい時間が増えるのか検証してみました。

index指定の範囲を前後に

    For i = 1 To 5000 '【この範囲を変更】
        v = col.item(i)
    Next i

取得回数	1回目	2回目	3回目
1 To 5000	0.12秒	0.11秒	0.15秒
20001 To 25000	0.95秒	0.88秒	0.85秒
40001 To 45000	2.08秒	2.09秒	1.90秒
60001 To 65000	3.75秒	3.89秒	3.92秒
80001 To 85000	9.47秒	8.20秒	7.99秒
100001 To 105000	13.18秒	11.07秒	11.30秒

後ろの要素を取り出そうとすればするほど遅いのがわかります。
VBAのCollectionはリスト（線形リスト）と呼ばれる構造でデータを保持しており、N番目の要素を取得しようとすると１番最初の要素からN番目まで順々に聞いて回る必要があります。
よってN番目が後ろであればあるほど要素の取得が遅くなります。

一方、配列は固定長の『入れ物』を予め用意しているため、直接N番目の要素の取得が可能です。

特定のindexの要素を取り出す必要がある場合、Collectionではなく、配列を選択した方が良いようです。

結論

• index指定して値を取得する必要がる場合は配列！！それ以外は好き好きで。