VBA Dictionary

Excel VBA Dictionary

In VBA in order to store a group of values, we use Arrays and Collections. We can use Excel VBA 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.

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

So, using VBA Dictionaries we can group all kinds of data in a Dictionary to get access to all the items with a single variable.

In this article, we will show you one of the important concept “VBA Dictionary”. Even though VBA Dictionary is not the easiest topic in the world its worth spending time to have shot at it.

Ok, let’s start the practicality now.

Working with VBA Dictionaries in Excel

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 below steps.

Step 1: Go to Tools > References.

Step 2: Scroll down and select ‘Microsoft Scripting Runtime’ option then click ok.

Now we can access VBA Dictionary with Scripting Library.

Create Instance of Dictionary with Excel VBA Code

After setting the reference to ‘Microsoft Scripting Runtime’ we need to create an instance of Excel 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 VBA 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. In 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.

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 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 an Excel VBA 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 & second one is Phone Number.

Name of the Person is Key.

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

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 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?
• How to use VBA InStr Function?
• VBA Comparision Operator in Excel
• Delete Entire Folder using VBA Code
• Charts in VBA
• IIF in VBA
• VBA New Line
• VBA Split Function
• Basic Tutorial in VBA
• End Property in VBA