How to Create and Utilize a Dictionary in VBA

1. Prerequisites
2. Create the Dictionary Object Using VBA
3. Adding Items in the Dictionary Object Using VBA
4. Return a Value on the Dictionary Using VBA
5. Change a Value on the Dictionary Using VBA
6. Count the Item in the Dictionary Using VBA
7. Print All Keys and Values in the Dictionary

The Dictionary object in VBA is not related to the language dictionary, which we use to know the meaning of words.

In VBA, the Dictionary object is somehow similar to the Collection object in storing data. The main difference is that a Collection cannot:

1. Check if an item is in the collection
2. Change the value of an existing item

Of course, we can still use Collection and create workarounds with this issue, but Dictionary offers these solutions with predefined methods. Thus, Dictionary can be useful for specific tasks, particularly when retrieving a certain item.

This article demonstrates how to create and utilize the Dictionary object to do certain functionalities in your VBA code.

Prerequisites

The Dictionary object does not come with the default library of VBA. Thus, before fully utilizing the Dictionary object, it is needed to reference the Microsoft Scripting Runtime library, including the Dictionary object.

You are now all set.

Create the Dictionary Object Using VBA

Now that the Microsoft Scripting Runtime is in the References the code block below will demonstrate how to create a Dictionary Object.

Create an object from the Scripting.Dictionary library, then initialize it as a new object.

Sub DictionaryDemo()

Dim d As Scripting.Dictionary

Set d = New Scripting.Dictionary

End Sub

Adding Items in the Dictionary Object Using VBA

Syntax:

[DictionaryObject].Add([Key], [Item])

Parameters:

We can add items to the Dictionary using the code block earlier. Names (Glen, Myla, Katrina, Jose) were added as the key and their respective age as the values on the code block below.

Sub DictionaryDemo()

Dim d As Scripting.Dictionary

Set d = New Scripting.Dictionary

d.Add ("Glen", 25)
d.Add ("Myla", 49)
d.Add ("Jose", 58)
d.Add ("Katrina", 18)

End Sub

Return a Value on the Dictionary Using VBA

With the Dictionary, we can return a value by knowing the key that corresponds with it.

Return Syntax:

[Value] = [DictionaryObject](Key)

Change Syntax:

[DictionaryObject](Key) = [Value]

Parameters:

Using the syntax, the value of the key Myla returned 49.

Sub DictionaryDemo()

Dim d As Scripting.Dictionary

Set d = New Scripting.Dictionary

d.Add "Glen", 25
d.Add "Myla", 49
d.Add "Jose", 58
d.Add "Katrina", 18

Debug.Print d("Myla")

End Sub

Output:

49

Change a Value on the Dictionary Using VBA

With the Dictionary, we can change a value by knowing the key that corresponds with it.

Change Syntax:

[DictionaryObject](Key) = [Value]

Parameters:

On the code block below, the value of the key Myla was changed from 49 to 50 using the syntax above.

Sub DictionaryDemo()

Dim d As Scripting.Dictionary

Set d = New Scripting.Dictionary

d.Add "Glen", 25
d.Add "Myla", 49
d.Add "Jose", 58
d.Add "Katrina", 18

d("Myla") = 50

Debug.Print d("Myla")

End Sub

Output:

50

Count the Item in the Dictionary Using VBA

We can return the number of all elements inside the Dictionary using the Count method.

Count Syntax:

[Value]= [DictionaryObject].Count

Parameters:

In the example below, we get the contents of the d object by using the Count method.

Sub DictionaryDemo()

Dim d As Scripting.Dictionary

Set d = New Scripting.Dictionary

d.Add "Glen", 25
d.Add "Myla", 49
d.Add "Jose", 58
d.Add "Katrina", 18

Debug.Print "There are " & d.Count & " items in the Dictionary."

End Sub

Output:

There are 4 items in the Dictionary.

Print All Keys and Values in the Dictionary

The code block below will print all the keys and values inside the Dictionary using a loop.

Sub DictionaryDemo()

Dim d As Scripting.Dictionary

Set d = New Scripting.Dictionary

d.Add "Glen", 25
d.Add "Myla", 49
d.Add "Jose", 58
d.Add "Katrina", 18

Dim Key as Variant

For Each Key In d.Keys
    Debug.Print Key, d(Key)
Next

End Sub

Output:

Glen           25 
Myla           49 
Jose           58 
Katrina        18