How to Create and Use a Dictionary in Excel

Here is an overview.

Download Practice Workbook

Dictionary in Excel.xlsm

Example 1 – Creating a Data Dictionary in Excel Using an Excel Formula

To create a data dictionary, use the combination of VLOOKUP, TYPE, and INDEX functions in Excel.

Click here to enlarge the image

Create a dataset to create a data dictionary.
Create a data classification table using Value and Return Type. Return Type returns the same type of values in Value.
A Data Dictionary was created using the formula in C22. The formula is:

=VLOOKUP(TYPE(INDEX($B$4:$F$5,2,MATCH(B22,$B$4:$F$4,0))),$B$14:$C$17,2,FALSE)

Adding data dictionary

Formula Breakdown

MATCH(B22, $B$4:$F$4, 0): searches for the value in B22 within $B$4:$F$4 and returns the relative position of the match (column number) with an exact match requirement (0 for exact match).
INDEX($B$4:$F$5, 2, MATCH(B22, $B$4:$F$4, 0)): returns the value at the intersection of row 2 and the column where the value in B22 matches the values in $B$4:$F$4.
TYPE(INDEX($B$4:$F$5, 2, MATCH(B22, $B$4:$F$4, 0))): is used to determine the data type of a value.It takes the value from step 1 and returns its data type, : 1 (number), 2 (text), 4 (logic) and 16 (error).
VLOOKUP(TYPE(INDEX($B$4:$F$5, 2, MATCH(B22, $B$4:$F$4, 0))), $B$14:$C$17, 2, FALSE): searches for the result of the INDEX-MATCH operation’s data type (either 1 for a number or 2 for text) within $B$14:$C$17 and returns the corresponding value from the second column with an exact match requirement (FALSE). Here return type is Number.

Example 2 – Creating a Custom Dictionary

Excel Options will be used to add a dictionary(.dic) file downloaded from the internet.

Press Alt + F + T to go to Excel Options.
Select Proofing >> Custom Dictionaries >> Add.

Adding new dictionary from Excel options.

Click here to enlarge the image

Select a dictionary file and click Open.

Selecting Dictionary from custom dictionary

A new dictionary file was added.

Added new dictionary

Example 3 – Creating a Dictionary Using VBA

To run the VBA code, select Developer >> Visual Basic.

Adding Visual Basic

Press Alt + T + R to go to the References – VBAProject window.
Check Microsoft Scripting Runtime.
Click OK.

Checking Microsoft Scripting Runtime

Go to the Insert tab and select Module.

Inserting new module

Enter the code below and click Run or press F5.

Sub VBA_Dictionary2()
  Dim Dictionary As Dictionary
  Set Dictionary = New Dictionary
With Dictionary
  .Add key:=11, Item:="Robert"
  .Add key:=12, Item:="Michael"
  .Add key:=13, Item:="Sabastean"
  .Add key:=14, Item:="Gwen"
  .Add key:=15, Item:="Bruce"
  .Add key:=16, Item:="Peter"
End With
Debug.Print (Dictionary(11))
End Sub

Code Breakdown

Sub VBA_Dictionary2()

Dim Dictionary As Dictionary

Set Dictionary = New Dictionary

The snippet creates a dictionary object and assigns it to the variable “Dictionary.” It allows storing key-value pairs for easy retrieval of values based on their corresponding keys.

With Dictionary

.Add key:=11, Item:=”Robert”

.Add key:=12, Item:=”Michael”

.Add key:=13, Item:=”Sabastean”

.Add key:=14, Item:=”Gwen”

.Add key:=15, Item:=”Bruce”

.Add key:=16, Item:=”Peter”

End With

In this code snippet, a dictionary object named “Dictionary” is used to store several key-value pairs. The keys range from 11 to 16, and the corresponding values are names such as “Robert,” “Michael,” “Sabastean,” “Gwen,” “Bruce,” and “Peter”.The “.Add” method is used to add each key-value pair to the dictionary.

Debug.Print (Dictionary(11))

prints the name of key:=11 in the Immediate window.

End Sub

Press Ctrl + G to go to the Immediate window.
Press Alt + V + H to open the Watches window.
Drag and drop Dictionary to Watches and Press F8 until the 4th line is executed.

Click here to enlarge the image

Click the “+” sign before the Dictionary expression in the Watches window.
Keep pressing F8 until you finish the line-by-line execution.

Line by line execution of vba code

Note: You can add items to a Dictionary using the .Add method. Specify the key and the corresponding value using key:= and Item:= parameters. For example: Dictionary.Add key:=11, Item:=”Robert”.

How to Use the Dictionary in Excel?

Press Alt + Left-Click after selecting B4 to get the built-in Excel dictionary.

Finding Synonyms using dictionary

How to Add a New Word to the Excel Dictionary?

Press Alt + F + T to go to Excel Options.
Select Proofing >> Custom Dictionaries >> Edit Word List… >> Word(s).
Add words to the Word(s) text box. They will be added to the dictionary.

Add a New word to Excel

Things to Remember

Library Reference: Ensure that you have added the appropriate library reference to use the Dictionary object. In VBA, you can go to the Tools menu, select References, and check the box Microsoft Scripting Runtime to enable the Dictionary functionality.
Declaration and Initialization: Declare a Dictionary object variable and initialize it using the New Dictionary syntax. For example: Dim Dictionary As New Dictionary.
Adding Key-Value Pairs: Use the .Add method to add key-value pairs to the dictionary. Specify the key and the corresponding value using the key:= and Item:= parameters. For example: Dictionary.Add key:=11, Item:=”Robert”.
Error Handling: Ensure to handle errors that may occur when accessing dictionary elements. Check if a key exists in the dictionary using the .Exists method before accessing its value. For example: If Dictionary.Exists(11) Then Debug.Print Dictionary(11).

Frequently Asked Questions

1. What is a Dictionary object in Excel VBA?

The Dictionary object in Excel VBA is a data structure that allows you to store and retrieve key-value pairs. It provides efficient access to values based on their corresponding keys.

2. How do I retrieve a value from a Dictionary in Excel VBA?

To retrieve a value from a Dictionary, use the key within parentheses. For example: Dictionary(11). This will return the value associated with key 11.

3. Can I check if a key exists in a Dictionary before accessing its value?

Yes, you can check if a key exists in a Dictionary using the .Exists method. For example: If Dictionary.Exists(11) Then Debug.Print Dictionary(11). This prevents potential errors when accessing non-existent keys.

4. How can I remove a key-value pair from a Dictionary in Excel VBA?

You can remove a specific key-value pair from a Dictionary using the .Remove method. For example: Dictionary.Remove 11. This will remove key 11 and its associated value from the Dictionary.

5. Can I iterate through a Dictionary in Excel VBA?

Yes, you can iterate through a Dictionary using a loop, such as For Each…Next. This allows you to perform operations on each key-value pair in the Dictionary.