VBA Dictionary

Last Updated :

21 Aug, 2024

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents
VBA Dictionary

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.

VBA Dictionary step 1

Step 2: Scroll down, select the 'Microsoft Scripting Runtime' option, then click "OK."

VBA Dictionary step 2

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
VBA Dictionary Example 1

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
Example 1-1

Now, we can access all the properties and methods of the dictionary.

VBA Dictionary Example 1-2

Note: All the green buttoned words are "Methods," and others are "Properties."

Now, declare one variable as "DictResult."

Dim DictResult As Variant
VBA Dictionary Example 1-3

We will create a new key using the "Dict" variable.

Example 1-4

The Key is what the word we are adding is. So, let's add the mobile phone name as "Redmi."

VBA Dictionary Example 1-5

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.

VBA Dictionary Example 1-6

Now, for another variable, "DictResult," we will add a keyword using the "Dict" variable.

Example 1-7

The Key is the word we have created in the previous step, the name of the phone.

Example 1-8

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
Example 1-9

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

VBA Dictionary Example 1-10

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 Sub

Run this code using the F5 key or manually and see the result.

VBA Dictionary Example 2

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