Excel VBA Dictionary

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 CollectionsVBA CollectionsVBA Collection is an uncomplicated data structure used for storing/collecting objects. Unlike VBA Array, you can more conveniently add & remove the items here. read more object.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Dictionary (wallstreetmojo.com)

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 boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

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 functionVLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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 codeExcel VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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.”

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 the F5 key or manually and see the result.

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: –

  • How to use ME in Excel VBA?Charts in VBANew Line in VBA MsgBoxWhat is VBA Split Function in Excel?