A dictionary is an object in VBA (similar to a collection) that stores data like an array. It can be used to store data of any type except an array.

A VBA dictionary has two parts:

1. Key
2. Value

…where key is simply a reference to the value provided.

Each item/value in the dictionary can be retrieved using its key, and therefore must be a unique value of datatype string, integer, etc.

If you’re a beginner, you can imagine this object as a real time dictionary where the keys are the words and items are the respective definitions. As with the real life dictionary, in the VBA dictionary object we do not need to iterate through all the keys to find the value of one specific key. 

How to add a reference to the VBA Dictionary

To use the VBA Dictionary, we need to add a reference to the MS Scripting runtime (‘Microsoft Scripting Runtime’). The menu below can lead us to that window.

Tools->References from the menu and click the checkbox ‘Microsoft Scripting Runtime’.

Then click on the “OK” button and close the window.

Creating a VBA Dictionary

The following code is a simple example of how to create a dictionary.

'Create a variable

Dim dic                   					
Set dic = CreateObject (“Scripting.Dictionary”)

'Add some keys with items / values

dic.Add “Benz”, 5    					
dic.Add  “BMW”, 10
dic.Add  “Audi”, 15
dic.Add  “Ferrari”, 100
dic.Add  “Porsche”, 50

Once the above code is executed, a dictionary object “dic” gets created with the following key and value pairs in it.

Keys	Values
Benz	5
BMW	10
Audi	15
Ferrari	100
Porsche	50

Collection VS dictionary – an overview

There are a few differences between collection object and dictionary object. A dictionary object has an edge over the collection object when it comes to a few specific functions.

A couple examples are provided below:

1. Check if a key exists:

In a collection, to find the existence of a value in a key-value pair, we need to iterate through all the keys. But with a dictionary, it is simple to check using the “.Exists” keyword.

If dic.Exists ("BMW") Then

‘perform any operations

End if

2. Change the values referenced by the keys :

In a collection, this task is complicated, though it is possible.

Whereas in a dictionary, the method is straightforward.

Example:

The below code changes the value of (“Audi”) key from 15 to 25 in the given dictionary object (“dic”) :

dic (“Audi”) = 25

Properties and functions supported by the VBA dictionary

“dic” is the dictionary object used for illustrative purposes in all the following examples.

Early Binding Declaration

The normal “Dim” keyword is used to declare a dictionary object during compile time.

Syntax: Dim <dictionary Object name> As Scripting.Dictionary

Example:

Dim dic As Scripting.Dictionary

Early Binding Creation

Setting a variable using a new keyword during compile time.

Syntax: Set <dictionary Object name> = New Scripting.Dictionary

Example:

Set dic = New Scripting.Dictionary

Late Binding Declaration

Declaring an object that will be created during run time.

Syntax: Dim <dictionary Object name> As Object

Example:

Dim dic As Object

Late Binding Creation

Setting an object using the CreateObject () method during run time.

Syntax: Set <dictionary Object name> = CreateObject (“Scripting.Dictionary”)

Example:

Set dic = CreateObject ("Scripting.Dictionary")

Adding an item to the dictionary

Adds a new key/item pair to a dictionary object by using the below command.

Syntax: <dictionary Object name>.Add Key, Value

Example:

dic.Add “Audi”, 5

dic.Add “BMW”,15

Changing the value for a specific key in the dictionary

Set a new key-value for an existing key in the dictionary object. If the key does not exist, it will automatically add the new key value.

Syntax: <dictionary Object name> (Key) = Value

Example:

dic (“BMW”) = 15

dic (“Benz”) = 16

Retrieve a value from the dictionary

Retrieve a value from the dictionary object for the given key value.

Syntax: <Var_name> = <dictionary Object name> (Key)

Example:

BMWCount = dic (“BMW”)

AudiCount = dic (“Audi”)

Check the existence of a key value in the dictionary

Return a Boolean value indicating whether the specified key is present in the given dictionary object or not.

Syntax: <dictionary Object name>.Exists (Key)

Example:

If dic.Exists (“Audi”)=True then

            Msgbox “Stock is available”

End if

Remove a specific item from the dictionary

Remove the specified key/item pair from the given dictionary object.

Syntax: <dictionary Object name>.Remove Key

Example:

dic.Remove “Audi”

‘Crossceck existence after removal

If dic.Exists (“Audi”)=False then

            Msgbox “Audi as been removed successfully”

End if

Count the number of key value pairs in the dictionary

Returns an integer value indicating the number of key value items in the given dictionary object.

Syntax: <dictionary Object name>.Count

Example:

Msgbox dic.Count

Remove all the items from the dictionary

Remove all the keys/item pairs in the given dictionary object.

Syntax: <dictionary Object name>.RemoveAll

Example:

dic.RemoveAll

Msgbox dic.count

Going through all items in the dictionary using for each loop

Print all the item pairs in the given dictionary object using a “for each” loop.

Syntax: For Each key In <dictionary Object name>.Keys

                        ‘Perform any operations

            Next key

Example: Dim key As Variant

   For Each key In dic.Keys  

Debug.Print key, dic (key)

   Next key

Going through all items in the dictionary using for loop (early binding)

Print all the item pairs in the given dictionary object using a “for” loop.

Syntax: For var_name = 0 To <dictionary Object name>.Count – 1

                        ‘Perform any operations

            Next var_name

            Example:  Dim i As Long

    For i = 0 To dic.Count - 1

Debug.Print dic.Keys (i), dic.Items (i)

    Next i

Going through all items in the dictionary using for loop (for loop – early and late binding)

Print all the item pairs in the given dictionary object using the “for” loop.

Syntax: For var_name = 0 To <dictionary Object name>.Count – 1

                        ‘Perform any operations

            Next var_name

Example:  Dim i As Long

    For i = 0 To dic.Count - 1

Debug.Print dic.Keys () (i), dic.Items () (i)

    Next i

Make the key a case sensitive element

This command is used to make the key-value a case sensitive element. For this, the dictionary must be empty.

Syntax: <dictionary Object name>.CompareMode = vbBinaryCompare

Example: dic.CompareMode = vbBinaryCompare

Make the key a non-case sensitive element

This command is used to make the key-value a non-case sensitive element. For this, the dictionary must be empty.

Syntax: <dictionary Object name>.CompareMode = vbTextCompare

            Example: dic.CompareMode = vbTextCompare

Filtering the dictionary

Filtering based on some criteria can also be achieved in the dictionary object as shown below.

For Each iter In Filter(MyDictionary.Keys, "Ch")

        MsgBox MyDictionary.Item(iter)

Next i

Storing and retrieving multiple values in the dictionary

This can be achieved using either of the two methods.

1. Creating a class
2. Concatenate and split the list of values using the “&” and split functions respectively as and when required.

Copying a dictionary’s values as a list in an Excel worksheet

The code below can help to create a list of the dictionary’s object’s values directly on the worksheet.

'All together in one cell
Sheets("Sheet2").Range("A1").Value = Join(MyDictionary.Keys, vbLf)

'Copy into multiple rows

Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys)

Range("C1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items)

Copying dictionary keys to an array

For this, we just need to create an object variant and pass the dictionary key set to the object.

Syntax: <Variant object>=<dictionary Object name>.keys

Example:

' Declare variant to use as array
    Dim arr As Variant

    ' Copy keys to array
    arr = MyDictionary.Keys
    
    ' Print array to Immediate Window(Ctrl + G to View)
    Call PrintArrayToImmediate(arr, "Keys:")
    
    ' Copy items to array
    arr = MyDictionary.Items
    
    ' Print array to Immediate Window(Ctrl + G to View)
    Call PrintArrayToImmediate(arr, "Items:")

Sorting a dictionary

Sorting is possible in a dictionary by two ways.

One is by copying it into a collection object or arraylist and use the “Sort” keyword. The sort order can also be reversed using this method.

The other method is to copy the keys and values to an excel worksheet, use the sort menu to sort them and then copy the sorted data back to the dictionary object. So, if thought logically , an ideal developer can find numerous ways to achieve anything he wants.

A piece of code to combine everything :

Here is a basic code snippet that covers most of what has been explained above. It is recommended you copy and paste it to a VBA editor of an Excel spreadsheet, add the necessary reference (explained above in this article) and run the code line by line (F8) to understand how it works. This can help you become more confident about how it all works.

The below video explains much about the code as it is run. A line by line explanation is provided using breakpoints and the immediate window for the viewers to understand the VBA dictionary object completely.

Code Snippet:

Sub Dictionary_trial()
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Create the dictionary object
    Dim MyDict As New Scripting.Dictionary
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Add values
    MyDict.Add "Taj Mahal", "India.- Agra"
    MyDict.Add "Chichen Itza", "Mexico.- Yucatán"
    MyDict.Add "Christ the Redeemer", "Brazil.- Rio de Janeiro"
    MyDict.Add "Colosseum", "Italy.- Rome"
    MyDict.Add "Great Wall of China", "China"
    MyDict.Add "Machu Picchu", "Cuzco Region"
    MyDict.Add "Petra", "Jordan.-Ma'an Governorate"
    MyDict.Add "Great Pyramid of Giza", "Egypt."
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Using the count keyword to iterate

    For n = 0 To MyDict.Count - 1
        Debug.Print MyDict.Keys(n) & " " & MyDict.Items(n)
    Next n
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Changing some values
    MyDict("Christ the Redeemer") = "Brazil"
    MyDict("Petra") = "Jordan"
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Iterating through all keys
    For Each i In MyDict.Keys
        Debug.Print i & " " & MyDict(i)
    Next i
''+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'checking for existence of a key
    If MyDict.Exists("Petra") Then
        Debug.Print MyDict("Petra")
    End If
    Debug.Print MyDict.Exists("Petra")
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Checking for existence of the same key with a different case
    Debug.Print MyDict.Exists("petra")
    
'Removing all keys before changing comparemode
    MyDict.RemoveAll

'Change compare mode and try again
    MyDict.Comparemode = TextCompare
    
'Adding some data again
    MyDict.Add "Taj Mahal", "India.- Agra"
    MyDict.Add "Chichen Itza", "Mexico.- Yucatán"
    MyDict.Add "Christ the Redeemer", "Brazil.- Rio de Janeiro"
    MyDict.Add "Colosseum", "Italy.- Rome"
    MyDict.Add "Great Wall of China", "China"
    MyDict.Add "Machu Picchu", "Cuzco Region"
    MyDict.Add "Petra", "Jordan.-Ma'an Governorate"
    MyDict.Add "Great Pyramid of Giza", "Egypt."
     
'Verify if comparemode works
    Debug.Print MyDict.Exists("petra")
''++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Filtering the dictionary
    For Each iter In Filter(MyDict.Keys, "Wall")
        Debug.Print MyDict.Item(iter)
    Next iter
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Copying the dictionary to an array
    
    ' Declare variant to use as array
    Dim arr As Variant

    ' Copy keys to array
    arr = MyDict.Keys
    
    ' Print array to Immediate Window(Ctrl + G to View)
    Call PrintArrayToImmediate(arr, "Keys:")
    
    ' Copy items to array
    arr = MyDict.Items
    
    ' Print array to Immediate Window(Ctrl + G to View)
    Call PrintArrayToImmediate(arr, "Items:")
''++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Copy the list of items to the worksheet

'Into one cell
    Sheets("Trial").Range("A1").Value = Join(MyDict.Keys, vbLf)
    
'In multiple rows
    Range("B1").Resize(MyDict.Count, 1) = WorksheetFunction.Transpose(MyDict.Keys)
    Range("C1").Resize(MyDict.Count, 1) = WorksheetFunction.Transpose(MyDict.Items)

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Removing one item
    Debug.Print MyDict.Count
    MyDict.Remove ("Petra")
    Debug.Print MyDict.Count
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Removing all items
    MyDict.RemoveAll
    Debug.Print MyDict.Count
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Sub
' Prints an array to the Immediate Window(Ctrl + G to View)
Sub PrintArrayToImmediate(arr As Variant, headerText As String)
    
    Debug.Print vbNewLine & headerText
    Dim entry As Variant
    For Each entry In arr
        Debug.Print entry
    Next
        
End Sub

Summary

Dictionary and collection objects have similarities, but their difference provides dictionary with an edge over collections when it comes to ease of use. The functions provided above show that dictionary is a very efficient tool for a clean scripting of scenarios, making use of relative references for processing, using the key and value attributes.

Watch out for our articles on arrays, lists and collection objects to experience the different use cases for each of these objects. This might help you choose the correct object in your code based on your scenario.