Dictionary – Generalities

Last Updated on January 20, 2022 – 4 min read

Do you have a lot of data to process on your Excel sheet? Do you want to work efficiently with suitable objects?

A dictionary is an essential object for this type of programming. It allows you to store and access a large amount of data.

What is a VBA dictionary ?

Dictionaries are implementations of a data structure more commonly known as an associative array. Your data is stored in the machine’s memory.

A dictionary consists of a collection of key-item pairs. The key is always associated with an item. Keys must be unique, but items can take multiple forms.

A dictionary is a powerful object in VBA. You can store various objects as items: a variable, an array, a custom type, or even a class object.

A dictionary is also an essential object for Python programmers.

You will see in this article how to create a dictionary, how to add new keys, how to delete a key.

In the article dictionary item, you will see how to handle items. What kinds of items can be stored? How to store several items for a key?

We will illustrate the use of items through the following sports data about football results of the three best clubs over a season for the five European leagues.

Country	League Name Season 2018/2019	Final Ranking	Team	Number of points (PTS)	Match Played (Pld)	Match Won	Match Draw (D)	Match Loss (L)	Goals For (GF)	Goals Against (GA)	Goal Difference (GD)
England	Premier League	1	Manchester City	98	38	32	2	4	95	23	72
England	Premier League	2	Liverpool FC	97	38	30	7	1	89	22	67
England	Premier League	3	Chelsea FC	72	38	21	9	8	63	39	24
France	Ligue 1	1	Paris Saint-Germain	91	38	29	4	5	105	35	70
France	Ligue 1	2	Lille OSC	75	38	22	9	7	68	33	35
France	Ligue 1	3	Olympique Lyonnais	72	38	21	9	8	70	47	23
Germany	Bundesliga	1	Bayern Munich	78	34	24	6	4	88	32	56
Germany	Bundesliga	2	Borussia Dortmund	76	34	23	7	4	81	44	37
Germany	Bundesliga	3	RB Leipzig	66	34	19	9	6	63	29	34
Italy	Serie A	1	Juventus	90	38	28	6	4	70	30	40
Italy	Serie A	2	Napoli	79	38	24	7	7	74	36	38
Italy	Serie A	3	Atalanta	69	38	20	9	9	77	46	31
Spain	Liga	1	FC Barcelona	87	38	26	9	3	90	36	54
Spain	Liga	2	Atlético de Madrid	76	38	22	10	6	55	29	26
Spain	Liga	3	Real Madrid	68	38	21	5	12	63	46	17

How do you create a dictionary ?

To create a dictionary in VBA, you can use the CreateObject and insert the “Scripting.Dictionary” method. The dictionary can be declared as an object.

You have access to the methods and properties. The addition of a key-item pair in your dictionary can be done using the Add property.

Sub Create_My_First_Dictionary()
    
    'Declaration
    Dim DictFootData As Object
    
    'Initialisation
    Set DictFootData = CreateObject("Scripting.Dictionary")
   
    'Add manually a key-item pair
    'Here we add the team as the key and the number of points 
    'at the end of the season
    DictFootData.Add Key:="Liverpool FC", Item:="97"
    DictFootData.Add Key:="Bayern Munich", Item:="78"

    'We can add the pair key-item without specifying the parameter name
    DictFootData.Add "FC Barcelona", "87"

    'At the end of your process
    'it is important to free the variable dictionary for the memory 
    Set DictFootData = Nothing

End Sub

The dictionary object is useful in combination with a loop on our data. It allows you to load your data in memory from a text file, a csv file, an Excel file.

In the following code, we loop over the data from the Excel spreadsheet to fill our dictionary. Then, we go through the keys of this dictionary to access each item of each key.

Sub Create_Dictionary_Loop_Data()
    
    'Declaration
    Dim DictFootData As Object
    Dim TeamName As String
    Dim NbPTS, ColTeamName, ColNbPTS As Integer
    Dim NbRowsDatabase, iRow As Long 

    'Initialisation
    Set DictFootData = CreateObject("Scripting.Dictionary")
    ColTeamName = 4
    ColNbPTS = 5
    NbRowsDatabase = 40 

    'Loop on the array to add a key-item pair
    'Here we add the team as the key and the number of points 
    For iRow = 2 To NbRowsDatabase

        TeamName = Cells(iRow,ColTeamName).Value2 
        NbPTS = Cells(iRow,ColNbPTS).Value2 
        DictFootData.Add Key:= TeamName, Item:= NbPTS

    Next iRow
    
   'At this moment your dictionary contains the name of all teams 
   'of your table and has associated in item the number of points.     

    'Loop on the keys of the dictionary 
    'in order to access each item of each key
    iRow = 1
    For Each k in DictFootData.Keys

        Sheets("Results").Cells(iRow,1).Value2 = k 'write the key
        Sheets("Results").Cells(iRow,2).Value2 = DictFootData.Item(k) 'write the item

        iRow = iRow+1

        '... your instructions...

    Next k

    'Free the variable 
    Set DictFootData = Nothing

End Sub

The strength of the dictionary object does not lie in the storage of a simple item, but the handling and storage of several items for the same key.

How do you check if a key exists in a VBA dictionary ?

Remember, each key must be unique. The first dictionary example did not require testing the existence of the key.

Now, we need to create a dictionary that will store each country as a key and the name of each teams item. We need to check the existence of the key.

It is essential to define clearly the keys. Keys can be a simple string variable or a concatenation of several variables to have uniqueness.

In the code below, first, the country key is added with the item, then if the key already exists we concatenate the existing item. To test the existence of your key, you can use the Exists method.

Sub Create_Dictionary_Test_Key_Exists()
    
    'Declaration
    Dim DictFootCountry As Object
    Dim Country,TeamName As String
    Dim NbPTS, ColCountryName, ColTeamName As Integer
    Dim NbRowsDatabase, iRow As Long

    'Initialisation
    Set DictFootCountry = CreateObject("Scripting.Dictionary")
    ColTeamName = 4
    ColCountryName = 1
    NbRowsDatabase = 40

    'Loop on the array to add a key-item pair
    'Here we add the team as the key and the number of points
    For iRow = 2 To NbRowsDatabase

        TeamName = Cells(iRow,ColTeamName).Value2
        Country = Cells(iRow, ColCountryName).Value2
        
        If Not DictFootCountry.Exists(Country) Then
            DictFootCountry.Add Key:= Country, Item:= TeamName
        ElseIf DictFootCountry.Exists(Country) Then
           DictFootCountry.Item(Country) = DictFootCountry.Item(Country) & " - " & TeamName
        End If

    Next iRow
    
    'Loop on the keys of the dictionary 
    'in order to access each item of each key
    iRow = 1

    For Each k in DictFootCountry.Keys

        Sheets("Results").Cells(iRow,1).Value2 = k 'display key
        Sheets("Results").Cells(iRow,2).Value2 = DictFootCountry.Item(k) 'display item
        
        iRow = iRow + 1
        
        '... your instructions...

    Next k

End Sub

At the end of the execution, the dictionary has four countries as keys and concatenated team’s names as items.

Keys	Items
England	Manchester City – Liverpool FC – Chelsea FC
France	Paris Saint-Germain- Lille OSC – Olympique Lyonnais
Germany	Bayern Munich – Borussia Dortmund – RB Leipzig
Italy	Juventus – Napoli – Atalanta
Spain	FC Barcelona – Atlético de Madrid – Real Madrid

How do you remove an existing key in a VBA dictionary ?

You can delete a key-item pair from your dictionary using the Remove method. You can also remove all keys from the dictionary using the RemoveAll method.

Public DictFootCountry As Object

Sub RemoveAKeyDictionary()

    'By removing the key France, there are only three countries left
    DictFootCountry.Remove("France")

    'Remove all keys
    DictFootCountry.RemoveAll

End Sub

The results of this code are the following. France has been removed from the dictionary.

Keys	Items
England	Manchester City – Liverpool FC – Chelsea FC
Germany	Bayern Munich – Borussia Dortmund – RB Leipzig
Italy	Juventus – Napoli – Atalanta
Spain	FC Barcelona – Atlético de Madrid – Real Madrid

How do you count the number of keys in a VBA dictionary ?

A dictionary in VBA can store a large amount of data. To know the number of keys, you can use the Count method.

Assuming that the Dict_Foot_Data dictionary was previously filled and declared as a public variable outside any procedure, you can count the number of keys stored using the Count method.

Public DictFootData As Object

Sub CountKeysInDict()
  
   Dim NbKeys As Integer 

   NbKeys = DictFootData.Count 'Here NbKeys = 15

End Sub

How declare a VBA Dictionary with Excel under Mac OS ?

If you are a Mac Os VBA user, you can not create ActiveX applications. The use of VBA is, therefore, more limited under Mac OS.

You can create a dictionary by using the creation of a class module. To learn more about classes modules, you can consult this article.

If you want to create a dictionary with Mac OS, I recommend you to download and insert this class module available on Github into your project.

Sub Dictionary_Mac_Os()
'Before using it, you need to the Dictionary
'Class Module for MAC OS
'available at this link: https://github.com/VBA-tools/VBA-Dictionary

    'Declaration
    Dim TeamName As String
    Dim NbPTS, ColTeamName, ColNbPTS As Integer
    Dim NbRowsDatabase, iRow As Long 

   'Declare as a New Dictionary Class
    Dim DictFootData As New Dictionary
    Dim DictFootCountry As New Dictionary

    'Initialisation
    Set DictFootData = CreateObject("Scripting.Dictionary")
    ColTeamName = 4
    ColNbPTS = 5
    NbRowsDatabase = 40 

    'Loop on the array to add a key-item pair
    'Here we add the team as the key and the number of points 
    For iRow = 2 To NbRowsDatabase

        TeamName = Cells(iRow,ColTeamName).Value2 
        NbPTS = Cells(iRow,ColNbPTS).Value2 

        DictFootData.Add TeamName, NbPTS

    Next iRow
    
    'At this moment your dictionary contains the name of all the teams 
    'of your table and has associated in item the number of points.     

    'Loop on the keys of the dictionary 
    'in order to access each item of each key

    iRow = 1

    For Each k in DictFootData.Keys

        Sheets("Results").Cells(iRow,1).Value2 = k 'write the key
        Sheets("Results").Cells(iRow,2).Value2 = DictFootData.Item(k) 'write the item

        iRow = iRow+1

        '... your instructions...

    Next k

    'Free the variable 
    Set DictFootData = Nothing

End Sub

For more information on using VBA on Mac OS, you can consult this helpful site.