How to Use VBA Dictionary in Excel (6 Practical Examples)

The VBA Dictionary is a useful tool for storing various types of data and easily using them with a single variable. This is why learning the VBA Dictionary can come in handy to save time while working in Excel. In this article, we will show you how to use the VBA Dictionary in Excel.

Introduction to VBA Dictionary

The VBA Dictionary is a VBA object that stores all the data in a pair. One is the key, and the other is the item. The item is where you can store a value, and the key is the name that is assigned to that value.

How to Use VBA Dictionary in Excel: 6 Suitable Examples

In this article, we will demonstrate six suitable examples of how to use the VBA Dictionary in Excel. We will learn to create a dictionary, add keys and items, and retrieve them. We will also explain how to check if an item exists or not, how to search for a key, and how to update the value of a key. Here’s an overview of the VBA code for today’s task.

1. Create a VBA Dictionary

In the beginning, we will create a VBA Dictionary in Excel. Follow these steps to do it.

Steps:

• First of all, open the Microsoft Visual Basic window by pressing  Alt+F11 .
• In the window, click on the Tools tab and go to,

Tools → References

• A dialogue box will appear. Check the box of Microsoft Scripting Runtime.
• Then click OK.

• Now from the Insert tab, go to,

Insert → Module

• In the module, write down the following code and the VBA Dictionary will be created.

Sub CreateDictionary ()
Dim x As New Dictionary 
End Sub

Read More: How to Create Data Dictionary in Excel

2. Add Keys and Items to the Dictionary

In this example, we will add keys and their items to the dictionary. The procedure to add keys and items is discussed in the following section.

Steps:

• First, define a variable in the Dictionary. Our defined variable is “x”.
• Now we want to add 5 keys and items. You could do that by using the Add command.
• Now let’s see the following code run. For that, copy the following formula.

Sub CreateDictionary ()
Dim x As New Dictionary
x.Add “Cookies”, 556
x.Add “Coffee”, 1265
x.Add “Brownie”, 970
x.Add “Donut”, 455
x.Add “Waffle”, 612
End Sub

• Before running the code, we will need two windows to see the code running.
• To open the Watch Window, click on the View tab and go to,

View → Watch Window

• The Watch window will open and will be visible at the bottom of the module.

• Next, press  Ctrl+G  to open the Immediate Window.
• Use your cursor to drag and adjust the two windows’ positions as per your need.

• Then press  F8  three times to run the first three lines of the code.
• After that, select the variable x from the fourth line of the code and drag it to the Watch Window.

• Finally, click on the “+” button beside x and keep pressing  F8  to run the rest of the lines of the code.
• Hence, you will be able to see the added item in the Watch Window as the code is running.

3. Retrieve an Item

Now we will retrieve an item from the added items. Read the following steps to learn how to do it.

Steps:

• First, you need to add the following line to the previous code to retrieve the item for “Cookies”.

Debug.Print (x(“Cookies”))

• Then press  F5  to run the code.
• As a result, the Immediate Window will show the item for “Cookies”.

4. Check Whether a Key Exists

You can also check if a key exists in a VBA Dictionary in Excel. Keep reading to learn how to do it.

Steps:

• First of all, write the following code to see if “Waffle“ exists in the VBA Dictionary or not.

Sub CreateDictionary ()
Dim x As New Dictionary
x.Add “Cookies”, 556
x.Add “Coffee”, 1265
x.Add “Brownie”, 970
x.Add “Donut”, 455
x.Add “Waffle”, 612
If x.Exists (“Waffle”) Then
MsgBox “It is on the list”
Else
x.Add “Waffle”, 612
End If
End Sub

• Next, press F5 to run the code.
• Hence, a Message Box will appear with the message “It is on the list” as “Waffle” exists in the dictionary.

5. Search the Dictionary for Keys and Items

In this example, you will learn how to search for any key or item in the Excel VBA Dictionary. The steps are discussed below.

Steps:

• First, copy the following formula and paste it into the Module.

Sub CreateDictionary ()
Dim x As New Dictionary
Dim xResult As Variant
x.Add “Cookies”, 556
x.Add “Coffee”, 1265
x.Add “Brownie”, 970
x.Add “Donut”, 455
x.Add “Waffle”, 612
xResult = Application.InputBox (Prompt:=”Enter the Food Name”)
If x.Exists (xResult) Then
MsgBox “Quantity Sold ” & xResult & “ & x(xResult)
Else
MsgBox “The Food Item is Missing”
End If
End Sub

• Next, run the code by pressing F5 and it will open an Input Box.
• Then type “Donut” in the box and click OK.

• As a result, a Message Box will open with the message “Quantity Sold Donut is: 455.”

• Now try a key that does not exist in the dictionary. Write “Chocolate” in the Input Box.

• This time, the Message Box will show “ The Food Item is Missing.”

Read More: How to Lookup in Excel Dictionary 

6. Update Values of Keys

In the last example, we will show you how to update the value of any key. You can follow the next steps to do it.

Steps:

• First of all, we want to update the value of “Brownie” from 970 to 1000. To do it, write down the following code.

Sub CreateDictionary()
Dim x As New Dictionary
x.Add "Cookies", 556
x.Add "Coffee", 1265
x.Add "Brownie", 970
x.Add "Donut", 455
x.Add "Waffle", 612
If x.Exists("Brownie") Then
x("Brownie") = 1000
Else
x.Add "Brownie", 1000
End If
Debug.Print (x("Brownie"))
End Sub

• Next, run the code by pressing F5 and you will find the updated value of “Brownie” in the Immediate Window.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

Thanks for making it this far. I hope you find this article useful. Now you know six examples of how to use the VBA Dictionary in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.

Related Articles

• Use English Dictionary in Excel