Dictionary in Excel – Create & Use

Here is an overview of the process of creating a dictionary.

Download Practice Workbook

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:

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.

Click here to enlarge the image

• Select a dictionary file and click Open.

• A new dictionary file was added.

3. Creating a Dictionary Using VBA

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

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

• Go to the Insert tab and select 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

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

How to Use the Dictionary in Excel?

Press Alt + Left-Click after selecting B4 to get the built-in Excel 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.

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.

Dictionary in Excel: Knowledge Hub

• Create Data Dictionary
• VBA Dictionary
• English Dictionary in Excel
• Lookup in Excel Dictionary

<< Go Back to Proofing in Excel | Learn Excel