【Excel VBA】ハッシュテーブル（連想配列）

Excel VBAでハッシュテーブルを利用することができます。

ハッシュテーブルは辞書型オブジェクトで、キー(key)とアイテム(Item)をセット格納することができます。
配列の場合はインデックス番号と要素がセットで、インデックス番号は0から始まる整数ですが、ハッシュテーブルの場合、キーは整数以外の文字なども使用可能です。

これを利用してVlookup関数のように使用してみたいと思います。

例えば下記のように果物の名前と値段が書いてあるリストからいちごの値段を求めて、E2セルに値を求めたいと思います。

Sub test1()
    
    Dim price As Long
    Dim obj As Object
    Set obj = CreateObject("Scripting.Dictionary")
    
    With Sheet1
       For i = 2 To 11
          obj(Trim(.Cells(i, 1))) = .Cells(i, 2)
       Next i
    
       If obj.exists(Trim(.Range("E1"))) = True Then
          price = obj(Trim(.Range("E1")))
          .Range("E2") = price
       End If
    End With
    
    Set obj = Nothing
            
End Sub

ハッシュテーブルを利用する場合、必ず下記のようにCreateObjectを使用してオブジェクト型の変数に格納します。

Dim obj as Object
Set obj = CreateObject("Scripting.Dictionary")

For i = 2 To 11
   obj(Trim(.Cells(i, 1))) = .Cells(i, 2)
Next i

ループを使用して、キーとアイテムをハッシュテーブルに格納していきます。
今回は果物の名前をキーに、値段をアイテムに格納しています。

If obj.exists(Trim(.Range("E1"))) = True Then
   price = obj(Trim(.Range("E1")))
   .Range("E2") = price
 End If

ハッシュテーブルにはExistsメソッドを使用でき、キーが存在するか確認することができます。
こちらを利用していちごの値段を求めます。
この時、格納するときにTrim関数を使用したので、同様にTrim関数を使用します。

先ほどの例では、キーには果物の名前、アイテムには値段を入れましたが、アイテムに行番号を格納すれば、下記のように産地、値段も簡単に求めることができます。

Dim i as long
Dim rowNum as long

With Sheet1
   For i = 2 To 11
　　　obj(Trim(.Cells(i, 1))) = i
   Next i

   If obj.exists(Trim(.Range("E1"))) = True Then
      rowNum = obj(Trim(.Range("E1")))
      .Range("E2") = .Cells(rowNum, 2)  '産地
      .Range("E3") = .Cells(rownum, 3)) '値段
   End If
End With

今回の例は、ハッシュテーブルに格納する際、obj(Trim(.Cells(i, 1))) = .Cells(i, 2)という書き方をしましたが、この場合、同じキーがある場合、上書きされます。

例）１２行目に「いちご」４５０円とあった場合、いちごは４５０円になります。

下記のAddメソッドを利用してハッシュテーブルに格納するときは、同じキーがある場合、上書きされないため、エラーが出ます。

For i = 2 To 11
　　obj.Add Trim(.Cells(i, 1)) , .Cells(i, 2)
Next i

Addメソッドを使用しエラーを回避する場合は、重複がないリスト、もしくはハッシュテーブルに格納する際に必ずExistsメソッドを利用し、一度格納しているキーを除外する必要があります。