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. We can use the dictionary to create a collection of key-value combinations. Once the object is linked to keys, later on, we can call them by just using the key name.
VBA Dictionary is very tough to get inside but we will try our level best to make it easy for you to understand. We can compare both Dictionary and Collection on the same scale but some of the VBA dictionaries offer some of the functionalities that are not available with VBA Collections object.
Working with VBA Dictionaries
In order to work with VBA Dictionaries first thing we need to do is 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 and select the ‘Microsoft Scripting Runtime’ option then click ok.
Now we can access VBA Dictionary with 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
Sub Dict_Example1() Dim Dict As Scripting.Dictionary End Sub
Now the variable “Dict” is an object variable. For object variable, we need to set the object reference by using the word “New”.
Set Dict = New Scripting.Dictionary
Now we can access all the properties and methods of the dictionary.
Now declare one variable as DictResult.
Dim DictResult As Variant
Now using the “Dict” variable we will create a new key.
Key is what the word we are adding is. Let’s add Mobile Phone name as “Redmi”.
Item is nothing but the definition to the word (key) we have added. This definition of the phone is its price so I will add the price to 15000.
Now for another variable “DictResult”, we will add keyword using “Dict” variable.
Key is the word we have created in the previous step i.e. 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.
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 & ITEM let me explain you with a simple example. Imagine the real world dictionary, with this dictionary we have words (keys) and meaning of those words (item). Similarly, words are Keys and 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 while saving the phone number. Here we have two things one is the Name of the Person & the second one is Phone Number.
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 as Item.
Check Whether the Mobile Phone is there or not
Imagine you are giving a user form to your customers to check the mobile phone’s price with a simple input box. Below Excel VBA code will present a Input Box in front of the user and they need to enter the brand of phone they are looking for, if the brand name is there in the dictionary it will show the price of the respective phone or else it will display the message as “Phone You are Looking for Doesn’t Exist in the Library”.
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 Sub
Run this code using F5 key or manually and see the result.
This has been a guide to VBA Dictionary. Here we learn how to use VBA Dictionary to create a collection of key-value combinations in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –