WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Dictionary

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.

VBA Dictionary

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.

VBA Dictionary step 1

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

VBA Dictionary step 2

Now we can access VBA Dictionary with Scripting Library.

You can download this VBA Dictionary Excel Template here – VBA Dictionary Excel Template

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. For object variable, we need to set the object reference 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

Now using the “Dict” variable, we will create a new key.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example 1-4

The Key is what the word we are adding is. 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 I 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 i.e., 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 & ITEM, let me explain to you 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 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 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 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 the 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.

VBA Dictionary Example 2

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?
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Dictionary Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More