VBAのコレクションへの添え字によるアクセスを早くする

※汎用性は低いです。

概要

前提

VBAのコレクションは、添え字を使ってアクセスする場合、後ろの方の要素ほど参照に時間がかかる（連結リストというデータ構造のため）。

時間がかからない参照方法

• ForEach文によるシーケンシャルアクセス
• 要素追加時に設定したキーによるアクセス

→ 添え字とキーの間に対応関係があれば比較的高速にアクセスできる。

具体例

添え字をキーにする

添え字（整数）から一意のキー（文字列）を作成する方法だが、
コレクションから要素を削除しないという前提を付けた場合、
「整数→文字列変換」を行えば一意のキーを取得できる（1番目の要素のキーは"1"といった形）。

実際に記述を考えると以下のようになる。

'Dim myCollection As VBA.Collection

'要素の追加
Call myCollection.Add(データ, CStr(myCollection.Count + 1))

'i番目の要素を参照
受け取る変数 = myCollection.Item(CStr(i))

問題点

• 要素の削除が出来なくなるため、要素の追加・削除が容易というコレクション（連結リスト）の利点の一つを殺している。
• 他の人が見たとき混乱しやすい。
• キー未指定の場合に比べて、要素の追加に時間がかかるようになる。

比較

自分の環境での結果

環境：Windows 10 64bit / Excel 2016 64bit
測定方法：Timer関数を用いた簡易測定

100000個の要素の格納時間

対象	処理時間（秒）
静的配列	0.000
動的配列（1個ずつReDim）	0.609
通常のCollection	0.016
キー指定のCollection	0.781
.NETのArrayList	0.563

1から100000までの要素の参照時間

対象	処理時間（秒）
静的配列	0.000
動的配列	0.000
通常のCollection	650.910
キー指定のCollection	0.329
.NETのArrayList	0.945
For Each Array	0.004
For Each Collection	0.018

この回数だと「動的配列」の方が「キー指定のCollection」よりも高速だが、
もっと要素数が多い場合では、要素の格納時間では「キー指定のCollection」の方が早くなる。

測定用コード

Sub CollectionBenchMark()
    Const CountMax& = 10000

    '準備
        '静的配列
        Dim staticArray(1 To CountMax) As Variant

        '動的配列1ずつ拡張する
        Dim dynamicArray() As Variant
        ReDim dynamicArray(1 To 1)

        '普通のコレクション
        Dim myNormalCol As VBA.Collection
        Set myNormalCol = New VBA.Collection

        'キー付きコレクション
        Dim myKeyCol As VBA.Collection
        Set myKeyCol = New VBA.Collection

        'ArrayList
        Dim myArrayList As Object
        Set myArrayList = CreateObject("System.Collections.ArrayList")

        '時間計測用
        Dim stTime As Single

        'For文用
        Dim i As Long


    Debug.Print vbCrLf; "要素の格納"; CountMax; "個"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let staticArray(i) = i
            Next i
        Debug.Print "静的配列", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let dynamicArray(i) = i
                ReDim Preserve dynamicArray(1 To UBound(dynamicArray) + 1)
            Next i
        Debug.Print "動的配列", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Call myNormalCol.Add(i)
            Next i
        Debug.Print "NormalCol", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Call myKeyCol.Add(i, CStr(myKeyCol.Count + 1))
            Next i
        Debug.Print "KeyCol", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Call myArrayList.Add(i)
            Next i
        Debug.Print "ArrayList", VBA.Format$(Timer - stTime, "0.000"); "秒"

    '要素取得変数
    Dim trash As Variant
    Debug.Print vbCrLf; "要素の参照 1 To "; CountMax

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let trash = staticArray(i)
            Next i
        Debug.Print "静的配列", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let trash = dynamicArray(i)
            Next i
        Debug.Print "動的配列", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let trash = myNormalCol.Item(i)
            Next i
        Debug.Print "NormalCol", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let trash = myKeyCol.Item(CStr(i))
            Next i
        Debug.Print "KeyCol", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For i = 1 To CountMax Step 1
                Let trash = myArrayList.Item(i - 1)
            Next i
        Debug.Print "ArrayList", VBA.Format$(Timer - stTime, "0.000"); "秒"

    'ForEach
    Dim tmp As Variant

        Let stTime = VBA.Timer
            For Each tmp In staticArray
                Let trash = tmp
            Next tmp
        Debug.Print "参考EachArray", VBA.Format$(Timer - stTime, "0.000"); "秒"

        Let stTime = VBA.Timer
            For Each tmp In myKeyCol
                Let trash = tmp
            Next tmp
        Debug.Print "参考EachCol", VBA.Format$(Timer - stTime, "0.000"); "秒"

    'エラー無視でアクセス（エラーを踏まえたアクセスコストの確認）
        Call myKeyCol.Remove(CountMax)
        Let stTime = VBA.Timer
            On Error Resume Next
                For i = 1 To CountMax Step 1
                    Let trash = myKeyCol.Item(CStr(CountMax))
                Next i
            On Error GoTo 0
        Debug.Print "参考ErrIgnore", VBA.Format$(Timer - stTime, "0.000"); "秒"
End Sub

まとめ？

ある程度要素数の多いコレクションに対する添え字アクセスは極力避けるべき。
添え字アクセスが必要になり、それがパフォーマンス上問題になる場合は、キーを使用したアクセスで代替できないか、他のデータ構造が使えないかを考える。

参考

VBA 配列とコレクションの違いをメモリ上のデータ構造から理解する
この記事でコレクションのデータ構造を知った。
他の記事もVBAの細かいところまでわかりやすく説明されていて勉強になる。