How to Use Dictionary in VBA

One of the most useful data structures in VBA is the dictionary object, which allows you to store and retrieve data using a key-value pair.

In this blog, we will explore some examples of how dictionaries can be used in VBA.

1. Storing and Retrieving Data

Dictionaries are useful for storing data that can be easily accessed using a key. For example, if you have a list of employees and their salaries, you can store this data in a dictionary with the employee name as the key and the salary as the value. You can then retrieve the salary for a specific employee by using the employee name as the key.

1. Creating Look-Up Tables

Dictionaries can be used to create look-up tables. For example, if you have a list of product codes and their descriptions, you can store this data in a dictionary with the product code as the key and the description as the value. You can then use this dictionary to quickly look up the description of a product by using its code.

1. Checking for Duplicates

Dictionaries can be used to check for duplicates in a list. For example, if you have a list of names, you can use a dictionary to check whether each name appears more than once. You can do this by using the name as the key and the value as a count of how many times the name appears in the list.

1. Storing Data in a Specific Order

Dictionaries can be used to store data in a specific order. For example, if you have a list of dates and events, you can store this data in a dictionary with the date as the key and the event as the value. You can then retrieve the events in chronological order by iterating through the keys in the dictionary.

1. Storing Complex Data Structures

Dictionaries can be used to store complex data structures. For example, if you have a list of employees and their information such as name, age, salary, and job title, you can store this data in a dictionary with the employee name as the key and a dictionary of their information as the value.

Examples of how to use Dictionary object in VBA:

Here are some real-world examples of how you can use the Dictionary object in VBA:

1. Creating a lookup table Suppose you have a table of data with two columns, and you want to create a lookup table to find the value in the second column based on a value in the first column. You can use a Dictionary object to create the lookup table with the keys being the values in the first column and the values being the values in the second column. Here's an example:

Dim lookupTable As New Dictionary
lookupTable.Add "A", 1    
lookupTable.Add "B", 2    
lookupTable.Add "C", 3    
Dim result As Integer    
result = lookupTable("B") 
Debug.Print result 'displays 2

1. Storing data in a cache If your VBA code is accessing data that is slow to retrieve, you can use a Dictionary object to cache the data in memory to speed up subsequent access. Here's an example:

Dim dataCache As New Dictionary

Function GetData(key As String) As String

If dataCache.Exists(key) Then 

    GetData = dataCache.Item(key) 

Else 

    'retrieve data from slow source 

    Dim result As String 

    result = RetrieveDataFromDatabase(key) 

    dataCache.Add key, result 

    GetData = result 

End If 

End Function

1. Counting occurrences of items Suppose you have a list of items, and you want to count how many times each item appears in the list. You can use a Dictionary object to keep track of the counts. Here's an example:

Dim itemCounts As New Dictionary

Sub CountItems()

Dim itemList As Variant

itemList = Array("Apple", "Banana", "Apple", "Orange", "Banana", "Apple") 

Dim item As Variant 

For Each item In itemList 

    If itemCounts.Exists(item) Then 

        itemCounts.Item(item) = itemCounts.Item(item) + 1 

    Else 

        itemCounts.Add item, 1 

    End If 

Next item 

Dim key As Variant 

For Each key In itemCounts.Keys 

    Debug.Print key & ": " & itemCounts.Item(key) 

Next key 

End Sub

Here is the syntax for some of the commonly used properties and methods of the Dictionary object in VBA:

Properties:

Count Syntax:

DictObject.Count

Example: Debug.Print dict.Count

Keys Syntax:

DictObject.Keys

Example: Debug.Print Join(dict.Keys, ",")

Items Syntax:

DictObject.Items

Example: Debug.Print Join(dict.Items, ",")

CompareMode Syntax:

DictObject.CompareMode

Example: dict.CompareMode = TextCompare

Methods:

Add Syntax: DictObject.Add Key, Item

Example: dict.Add "Apple", 1

Item Syntax: DictObject.Item(Key)

Example: Debug.Print dict.Item("Apple")

Exists Syntax: DictObject.Exists(Key)

Example: If dict.Exists("Apple") Then ...

Remove Syntax: DictObject.Remove(Key)

Example: dict.Remove("Apple")

RemoveAll Syntax: DictObject.RemoveAll()

Example: dict.RemoveAll()

Keys Syntax: DictObject.Keys()

Example: For Each key In dict.Keys ...

Items Syntax: DictObject.Items()

Example: For Each item In dict.Items ...

CompareMode Syntax: DictObject.CompareMode = CompareMethod

Example: dict.CompareMode = vbTextCompare

Conclusion, dictionaries are a powerful tool in VBA that can be used for storing and retrieving data, creating look-up tables, checking for duplicates, storing data in a specific order, and storing complex data structures.

By leveraging the power of dictionaries, we can create fast running and effective VBA programs.

Ashwani