VBA Dictionary

What is VBA Dictionary?

A VBA Dictionary is a powerful data structure that allows you to store and retrieve key-value pairs. It is like an array, but instead of using numerical indices to access values, it uses unique keys. Remember that each key used in a VBA dictionary must be unique. Therefore, if the key you try to add already exists in the dictionary, the new value will be overwritten.

VBA dictionaries are so named as they are similar to a real-world dictionary where the keys are like the words, and the items are their explanation. To declare a dictionary and add items to it, you can use the following code:

Working with VBA Dictionaries

Accessing items in the dictionary: You can retrieve values from the dictionary using the VBA Dictionary keys:

Debug.Print dict(“key1”)

It will print the following output.

Checking if a key exists: You can use the method shown below to check if a key exists in the dictionary.

If dict.Exists(“key1”) Then

    Debug.Print(“value1”)

End If

It will print the following output:

Removing items from the dictionary: You can remove an item from the dictionary using the method shown below:

dict.Remove “key1”

Looping through the dictionary: You can use a For…Each loop to iterate through all the items in the dictionary:

For Each key In dict.Keys
value = dict(key)
Debug.Print(Value)
Next key

It will print the output:

A VBA Dictionary is a versatile tool that can help you to streamline your code and make it more efficient. By following these tips, you can take full advantage of their capabilities.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Create An Instance Of A Dictionary With VBA Code?

Here’s how you create an instance of a dictionary in VBA. 

Step 1: Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and sub-procedures. Then, click the “Insert” and the “Module” button to create a new module or blank page.

Step 2: Before starting the project, go to the tools section in the VBA editor and turn on Microsoft scripting runtime.

Step 3: Declare a global variable ‘obdict’ of the datatype Object for the module to add a new VBA dictionary. You can use this variable for as many sub-procedures as you prefer.

Step 4: Create a sub-procedure ExampleDict() to show how dictionaries are used.

Step 5: Define a new dictionary using ‘obdict’.

Step 6: Add a bunch of keys and values to the variable dict as shown below:

Step 7: Declare a variable ‘value’ of datatype String. Assign the variable with the value of any keys using the Excel VBA dictionary function syntax <dictionary_name>(“Key”) and print value.

Code:

Sub ExampleDict()
Set obdict = CreateObject(“Scripting.Dictionary”)
Dim d As New Scripting.Dictionary
d.Add “Computer”, Item:=5
d.Add “Desktop”, Item:=2
d.Add “Mouse”, Item:=3
d.Add “Keyboard”, Item:=4
Dim value As String
value = d(“Desktop”)
Debug.Print (value)
End Sub

Step 8: Run the code by pressing the F5 or Run button. The output is shown below:

Understanding Key and Item

In a VBA Dictionary, a Key refers to a unique identifier used to retrieve the corresponding value or Item. Each Key must be unique within the dictionary and can be of any data type that can be compared using the built-in comparison operators.

An Item is a value associated with a particular Key in the VBA Dictionary. Each Key in the Dictionary can have one corresponding Item. Items can be of any data type, including objects, arrays, and other dictionaries.

The Add method adds a key-item pair to a VBA dictionary index.

In this example, “key1” is the Key, and “Value1” is the Item. You can retrieve the Item associated with a Key by using the Key in brackets:

It will result in the output being printed as “Value1” in the Immediate tab.

Trying to retrieve an Item with a Key that does not exist in the Dictionary will result in a runtime error. To avoid this, you can use the Exists method to check if a Key exists before attempting to retrieve its associated Item:

It will print the output:

To Check Whether A Mobile Phone Exists In A Dictionary Or Not

Consider a dictionary defined by five phone numbers as the items in a dictionary as shown below:

Step 1: Create a new dictionary called ‘mydict’ of the datatype, Scripting.Dictionary.

Step 2: Add the key-value pairs necessary into the dictionary as shown below:

Step 3: Initialize an If-Else condition to check the existence of phone numbers and print the value if so:

Since “Phone2” exists in the dictionary, it will print the phone number of “Phone2” in the Immediate tab.

Code:

Sub toFindPhoneNumber()
Set obdict = CreateObject(“Scripting.Dictionary”)
Dim mydict As New Scripting.Dictionary
mydict.Add “Phone1”, “9944045569”
mydict.Add “Phone2”, “9234219087”
mydict.Add “Phone3”, “8789651234”
mydict.Add “Phone4”, “7754322114”
mydict.Add “Phone5”, “6530225317”
If mydict.Exists(“Phone2”) Then
Debug.Print (“Mobile number is: ” & mydict(“Phone2”))
Else: Debug.Print (“Mobile number does not exist”)
End If
End Sub

Step 4: Press F5 or the Run button to print the output below:

Consequently, if the mobile number does not exist, it will print the following:

VBA Dictionary vs. Collection

Dictionary and Collection in VBA are valuable tools for data collection in VBA, but they differ in features and performance.

• VBA Dictionary is an object that allows you to store and retrieve Key-Item pairs.
• You can use a Dictionary to store and retrieve data quickly and efficiently, even for vast collections of data.
• A Dictionary is typically faster than a Collection when it comes to retrieving Items by Key because it uses a hashing algorithm to find the Items quickly based on their Key. However, unlike VBA Collection, it isn’t stored in sequential order.
• Therefore, to sort the dictionaries, we must implement VBA Dictionary Sort with the help of Microsoft Scripting Runtime Library.

• VBA Collection, on the other hand, is a simpler object that allows you to store and retrieve Items in sequential order.
• You can use a Collection to store and retrieve data, but it may not be as efficient as a Dictionary for large collections of data.
• In addition, collections are typically slower than Dictionaries when it comes to retrieving Items by Key because they have to search through the collection sequentially until they find the Item.

Here are some key differences between VBA Dictionary and Collection:

• Retrieving Items: Dictionary is faster than Collection when retrieving Items by Key because it uses a hashing algorithm to locate the Item based on its Key. Collection, on the other hand, has to search through the collection sequentially to find the Item.
• Ordering: The dictionary does not maintain the order of the Key-Item pairs, while Collection maintains the order of the Items in the collection.
• Key uniqueness: The dictionary requires unique Keys, while Collection allows duplicate Items to be added to the collection.
• Built-in methods: The dictionary has built-in methods for sorting and searching for Keys and Items, while Collection does not.

In summary, VBA Dictionary and Collection are helpful tools for working with collections of data in VBA, but they have different features and performance characteristics. For example, if you need to store and retrieve data quickly and efficiently and you want to search for Items by Key, a Dictionary is a better choice. On the other hand, a Collection may be a better choice if you need to maintain the order of the Items in the collection or you don’t need to search for Items by Key.

Important Things To Note

• Enabling the Microsoft Scripting Runtime library: You need to enable the Microsoft Scripting Runtime library in your VBA project to use the Dictionary object. To do this, open the VBA Editor, select “Tools” from the menu bar, and then choose “References.” In the References dialog box, find “Microsoft Scripting Runtime” and check the box next to it.
• Type safety: Unlike collections, which are untyped, dictionaries in VBA are strongly typed, meaning that you must declare the type of both the keys and values that you are storing in the dictionary. Ensure that the data types of the keys and values you add to the dictionary match the declared types.
• Key uniqueness: Each key in a VBA dictionary must be unique. If you try to add a key that already exists in the dictionary, the new value will overwrite the current value associated with that key.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the VBA Dictionary, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Dictionary. Here we create an instance of dictionary in VBA and some key differences between dictionary and collection. You may learn more from the following articles –

• VBA ByRef Argument Type Mismatch
• VBA Select Case
• VBA Input Box