What is a VBA Dictionary? - VBA and VB.Net Tutorials, Education and Programming Services (2023)

What is a VBA Dictionary? - VBA and VB.Net Tutorials, Education and Programming Services (1)

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

…wherekey 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
  • Creating a VBA Dictionary
  • Collection VS dictionary – an overview
  • Properties and functions supported by the VBA dictionary
    • Early Binding Declaration
    • Early Binding Creation
    • Late Binding Declaration
    • Late Binding Creation
    • Adding an item to the dictionary
    • Changing the value for a specific key in the dictionary
    • Retrieve a value from the dictionary
    • Check the existence of a key value in the dictionary
    • Remove a specific item from the dictionary
    • Count the number of key value pairs in the dictionary
    • Remove all the items from the dictionary
    • Going through all items in the dictionary using for each loop
    • Going through all items in the dictionary using for loop (early binding)
    • Going through all items in the dictionary using for loop (for loop – early and late binding)
    • Make the key a case sensitive element
    • Make the key a non-case sensitive element
    • Filtering the dictionary
    • Storing and retrieving multiple values in the dictionary
    • Copying a dictionary’s values as a list in an Excel worksheet
    • Copying dictionary keys to an array
    • Sorting a dictionary
  • A piece of code to combine everything :
  • Summary

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.

What is a VBA Dictionary? - VBA and VB.Net Tutorials, Education and Programming Services (2)

Creating a VBA Dictionary

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

'Create a variableDim dic Set dic = CreateObject (“Scripting.Dictionary”)'Add some keys with items / valuesdic.Add “Benz”, 5 dic.Add “BMW”, 10dic.Add “Audi”, 15dic.Add “Ferrari”, 100dic.Add “Porsche”, 50

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

KeysValues
Benz5
BMW10
Audi15
Ferrari100
Porsche50

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 thedictionary 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 cellSheets("Sheet2").Range("A1").Value = Join(MyDictionary.Keys, vbLf)'Copy into multiple rowsRange("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.

Tagged with: Arrays, Collection, Dictionary Object, EarlyBinding, LateBinding, Loop, Reference

Top Articles
Latest Posts
Article information

Author: Margart Wisoky

Last Updated: 03/01/2023

Views: 5991

Rating: 4.8 / 5 (58 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Margart Wisoky

Birthday: 1993-05-13

Address: 2113 Abernathy Knoll, New Tamerafurt, CT 66893-2169

Phone: +25815234346805

Job: Central Developer

Hobby: Machining, Pottery, Rafting, Cosplaying, Jogging, Taekwondo, Scouting

Introduction: My name is Margart Wisoky, I am a gorgeous, shiny, successful, beautiful, adventurous, excited, pleasant person who loves writing and wants to share my knowledge and understanding with you.