Does VBA have Dictionary Structure?

Viewed 326211

Does VBA have dictionary structure? Like key<>value array?

11 Answers

Yes.

Set a reference to MS Scripting runtime ('Microsoft Scripting Runtime'). As per @regjo's comment, go to Tools->References and tick the box for 'Microsoft Scripting Runtime'.

References Window

Create a dictionary instance using the code below:

Set dict = CreateObject("Scripting.Dictionary")

or

Dim dict As New Scripting.Dictionary 

Example of use:

If Not dict.Exists(key) Then 
    dict.Add key, value
End If 

Don't forget to set the dictionary to Nothing when you have finished using it.

Set dict = Nothing 

VBA does not have an internal implementation of a dictionary, but from VBA you can still use the dictionary object from MS Scripting Runtime Library.

Dim d
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "aaa"
d.Add "b", "bbb"
d.Add "c", "ccc"

If d.Exists("c") Then
    MsgBox d("c")
End If

VBA can use the dictionary structure of Scripting.Runtime.

And its implementation is actually a fancy one - just by doing myDict(x) = y, it checks whether there is a key x in the dictionary and if there is not such, it even creates it. If it is there, it uses it.

And it does not "yell" or "complain" about this extra step, performed "under the hood". Of course, you may check explicitly, whether a key exists with Dictionary.Exists(key). Thus, these 5 lines:

If myDict.exists("B") Then
    myDict("B") = myDict("B") + i * 3
Else
    myDict.Add "B", i * 3
End If

are the same as this 1 liner - myDict("B") = myDict("B") + i * 3. Check it out:

Sub TestMe()

    Dim myDict As Object, i As Long, myKey As Variant
    Set myDict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To 3
        Debug.Print myDict.Exists("A")
        myDict("A") = myDict("A") + i
        myDict("B") = myDict("B") + 5
    Next i
    
    For Each myKey In myDict.keys
        Debug.Print myKey; myDict(myKey)
    Next myKey

End Sub

enter image description here

You can access a non-Native HashTable through System.Collections.HashTable.

HashTable

Represents a collection of key/value pairs that are organized based on the hash code of the key.

Not sure you would ever want to use this over Scripting.Dictionary but adding here for the sake of completeness. You can review the methods in case there are some of interest e.g. Clone, CopyTo

Example:

Option Explicit

Public Sub UsingHashTable()

    Dim h As Object
    Set h = CreateObject("System.Collections.HashTable")
   
    h.Add "A", 1
    ' h.Add "A", 1  ''<< Will throw duplicate key error
    h.Add "B", 2
    h("B") = 2
      
    Dim keys As mscorlib.IEnumerable 'Need to cast in order to enumerate  'https://stackoverflow.com/a/56705428/6241235
    
    Set keys = h.keys
    
    Dim k As Variant
    
    For Each k In keys
        Debug.Print k, h(k)                      'outputs the key and its associated value
    Next
    
End Sub

This answer by @MathieuGuindon gives plenty of detail about HashTable and also why it is necessary to use mscorlib.IEnumerable (early bound reference to mscorlib) in order to enumerate the key:value pairs.


Related