VBA Dictionary

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

Code:

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 of 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 the “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.

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 & ITEM let me explain to 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.

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 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 an 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”.

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.

Recommended Articles

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 –

• How to use ME in Excel VBA?
• Charts in VBA
• New Line in VBA MsgBox
• What is VBA Split Function in Excel?