Excel VBA Dictionary
Using VBA Dictionary, we can group all kinds of data in a dictionary to get access to all the items with a single variable. For example, we can use the Dictionary to create a collection of key-value combinations. Then, once the object links to keys, we can call them later by just using the key name.
VBA Dictionary is very tough to get inside, but we will try our best to make it easy for you to understand. We can compare both Dictionary and Collection on the same scale. Still, some of the VBA dictionaries offer some of the functionalities that are not available with the VBA Collections object.
Working with VBA Dictionaries
To work with VBA Dictionaries, we first need to set the object reference to ‘Microsoft Scripting Runtime.’
To set the reference, follow the below steps.
Step 1: Go to Tools > References.
Step 2: Scroll down, select the ‘Microsoft Scripting Runtime’ option, then click “OK.”
Now, we can access the VBA Dictionary with the Scripting Library.
Create Instance of Dictionary with VBA Code
After setting the reference to ‘Microsoft Scripting Runtime,’ we need to create an instance of the VBA Dictionary. First, declare the variable as Scripting.Dictionary.
Code:
Sub Dict_Example1()
Dim Dict As Scripting.Dictionary
End Sub
Now, the variable “Dict” is an object variable. Therefore, we need to set the object reference for the object variable by using the word “New.”
Set Dict = New Scripting.Dictionary
Now, we can access all the properties and methods of the dictionary.
Note: All the green buttoned words are “Methods,” and others are “Properties.”
Now, declare one variable as “DictResult.”
Dim DictResult As Variant
We will create a new key using the “Dict” variable.
The Key is what the word we are adding is. So, let’s add the mobile phone name as “Redmi.”
Item is nothing but the definition of the word (key) we have added. This definition of the phone is its price so we will add the price to 15000.
Now, for another variable, “DictResult,” we will add a keyword using the “Dict” variable.
The Key is the word we have created in the previous step, the name of the phone.
Now, the variable “DictResult” has the item of the key we have added. Now show the result of the variable in the VBA message box.
Code:
Sub Dict_Example1()
Dim Dict As Scripting.Dictionary
Set Dict = New Scripting.Dictionary
Dim DictResult As Variant
Dict.Add Key:=”Redmi”, Item:=15000
DictResult = Dict(“Redmi”)
MsgBox DictResult
End Sub
Now, run the code manually or using the F5 key, and a message box will show you the price (item) of the phone (key) we have added using “Dict.”
Understanding KEY & ITEM
If you have not understood KEY and ITEM, let us explain with a simple example. Imagine a real-world dictionary. With this Dictionary, we have words (keys) and the meaning of those words (item). Similarly, words are “Keys,” and the definition or meaning is the “Item.”
Now, look at one more example of a Dictionary. Assume you are searching for a phone number of a particular person. How do you search?
Obviously, by using the name, we have used it while saving the phone number. So, here we have two things one is the Name of the Person and the second one is the Phone Number.
The name of the Person is Key.
The Phone Number is Item.
If you want the example of Excel, we can give VLOOKUP as an example. We use the formula to look for values based on the LOOKUP VALUE (Key). The result returned by the VLOOKUP function is called Item.
Check Whether the Mobile Phone is there or not.
Imagine giving your customers a user form to check the mobile phone’s price with a simple input box. Below Excel VBA code will present an input box in front of the user, and they need to enter the brand of the phone they are looking for. If the brand name is in the Dictionary, it will show the price of the respective phone or display the message as “Phone You are Looking for Doesn’t Exist in the Library.”
Code:
Sub Dict_Example2()
Dim PhoneDict As Scripting.Dictionary
Dim DictResult As Variant
Set PhoneDict = New Scripting.Dictionary
PhoneDict.Add Key:="Redmi", Item:=15000
PhoneDict.Add Key:="Samsung", Item:=25000
PhoneDict.Add Key:="Oppo", Item:=20000
PhoneDict.Add Key:="VIVO", Item:=21000
PhoneDict.Add Key:="Jio", Item:=2500
DictResult = Application.InputBox(Prompt:="Please Enter the Phone Name")
If PhoneDict.Exists(DictResult) Then
MsgBox "The Price of the Phone " & DictResult & " is : " & PhoneDict(DictResult)
Else
MsgBox "Phone You are Looking for Doesn't Exists in the Library"
End If
End SubRun this code using the F5 key or manually and see the result.
Recommended Articles
This article has been a guide to the VBA Dictionary. Here, we learn how to use VBA Dictionary to create a collection of key-value combinations in Excel with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –