What is a VBA Dictionary?
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:
- Key
- 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.
Contents
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:
- 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.
- Creating a class
- 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) &amp;amp;amp; " " &amp;amp;amp; 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 &amp;amp;amp; " " &amp;amp;amp; 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 &amp;amp;amp; 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.