How to Use VBA Dictionary in Excel: 6 Practical Methods

Method 1 – Create a VBA Dictionary

Steps:

• Open the Microsoft Visual Basic window by pressing  Alt+F11 .
• Click on the Tools tab and go to,

Tools → References

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

• From the Insert tab, go to,

Insert → Module

• Write down the following code, and the VBA Dictionary will be created.

Sub CreateDictionary ()
Dim x As New Dictionary 
End Sub

Method 2 – Add Keys and Items to the Dictionary

Steps:

• Define a variable in the Dictionary. Our defined variable is “x”.
• Add 5 keys and items. You could do that by using the Add command.
• See the following code run. 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 be visible at the bottom of the module.

• Press  Ctrl+G  to open the Immediate Window.
• Use your cursor to drag and adjust the two windows’ positions as needed.

• Press  F8  three times to run the first three lines of the code.
• Select the variable x from the fourth line of the code and drag it to the Watch Window.

• Click on the “+” button beside x and keep pressing  F8  to run the rest of the lines of the code.
• See the added item in the Watch Window as the code is running.

Method 3 – Retrieve an Item

Steps:

• Add the following line to the previous code to retrieve the item for “Cookies”.

Debug.Print (x(“Cookies”))

• Press  F5  to run the code.
• The Immediate Window will show the item for “Cookies”.

Method 4 – Check Whether a Key Exists

Steps:

• 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

• Press F5 to run the code.
• A Message Box will appear with the message “It is on the list” as “Waffle” exists in the dictionary.

Method 5 – Search the Dictionary for Keys and Items

Steps:

• 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

• Run the code by pressing F5, and it will open an Input Box.
• Type “Donut” in the box and click OK.

• A Message Box will open with the message “Quantity Sold Donut is: 455.”

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

• The Message Box will show “ The Food Item is Missing.”

Method 6 – Update Values of Keys

Steps:

• Update the value of “Brownie” from 970 to 1000. 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

• 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.

<< Go Back to Dictionary in Excel | Proofing in Excel | Learn Excel