For文での線形探索より高速 VBAで連想配列 Dictionaryオブジェクト

以下のような検索キー列がユニークであるテーブルからデータを取得したい（VLOOKUPみたいなイメージ）場合、処理回数が多いと WorksheetFanction.Vlookup やFor文での線形探索では時間がかかり過ぎる。

そんなときに役立つのが連想配列（Dictionaryオブジェクト）。

配列のようにインデックス番号からデータを取り出すのではなくキーの完全一致で要素を取り出せるので、処理が理解しやすいかつ高速であることが最大の利点。

オブジェクトを定義する

VBAでDictionaryオブジェクトを使うためには ツール > 参照設定 > 「Microsoft Scripting Runtime」にチェックを入れてOK。 Dictionary として定義する。

Dim DecName As Dictionary 
Set DecName = New Dictionary

または参照設定せず、CreateObjectで定義する

Dim DecName As Object
Set DecName = CreateObject("Scripting.Dictionary")

データを代入する

右のようなテーブルがあったときには以下のようにFor文によって2行目から順番にキーと要素を Add メソッドの第一引数に key 第二引数に value を渡して実行することでDictionaryオブジェクトにセットされる。

※同じ key を複数セットしようとするとエラーになる点に注意

' データテーブルをRangeオブジェクトとして受け取って連想配列に格納する
Sub initDic(dataTable As Range)

    Dim key, val As String
    Dim i As Long
    Dim DicName As Dictionary 
        Set DicName = New Dictionary
    
    For i = 2 To dataTable.Rows.Count

        key = dataTable(i, 1).Value
        val = dataTable(i, 2).Value
        
        ' Existsメソッドでキーが存在しているかチェックして重複エラーを回避
        If DicName.Exists(key) = False Then
        
            DicName.Add key, val
            Debug.Print "キー: " & key & " に「" & val & "」を登録しました"
            
        Else
            Debug.Print "キー: " & key & " は登録済みです"
            
        End If
        
    Next
    
End Sub

小技

オブジェクトをサブプロシージャ外で Public として定義することでグローバル変数にできる。

Public DicName As Dictionary 

Sub main()
        Call initDic(ActiveSheet.UsedRange)
End Sub

Sub initDic(dataTable As Range)

    Dim key, val As String
    Dim i As Long
    Set DicName = New Dictionary
    
    For i = 2 To dataTable.Rows.Count

        key = dataTable(i, 1).Value
        val = dataTable(i, 2).Value
        
        ' Existsメソッドでキーが存在しているかチェックして重複エラーを回避
        If DicName.Exists(key) = False Then
            DicName.Add key, val 
            Debug.Print "キー: " & key & " に「" & val & "」を登録しました"
        Else
            Debug.Print "キー: " & key & " は登録済みです"
        End If
        
    Next
    
End Sub

実行結果

Dictionaryオブジェクトからデータを取り出す

DecName.Item(key) とすることでデータを取得できる。

存在しないkeyを指定した場合は空白が返ってくる。

Debug.Print DicName.Item("埼玉")  ' => さいたま

Item は省略することも可能

Debug.Print DicName("埼玉")  ' => さいたま