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.

Kenya – Photo taken by me

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.

CountryLeague Name Season 2018/2019Final Ranking TeamNumber of points (PTS)Match Played (Pld)Match Won (W)Match Draw (D)Match Loss (L)Goals For (GF)Goals Against (GA)Goal Difference (GD)
EnglandPremier League1Manchester City98383224952372
EnglandPremier League2Liverpool FC97383071892267
EnglandPremier League3Chelsea FC72382198633924
FranceLigue 11Paris Saint-Germain913829451053570
FranceLigue 12Lille OSC75382297683335
FranceLigue 13Olympique Lyonnais72382198704723
GermanyBundesliga1Bayern Munich78342464883256
GermanyBundesliga2Borussia Dortmund76342374814437
GermanyBundesliga3RB Leipzig66341996632934
ItalySerie A1Juventus90382864703040
ItalySerie A2Napoli79382477743638
ItalySerie A3Atalanta69382099774631
SpainLiga1FC Barcelona87382693903654
SpainLiga2Atlético de Madrid 763822106552926
SpainLiga3Real Madrid 683821512634617

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.

KeysItems
EnglandManchester City – Liverpool FC – Chelsea FC
FranceParis Saint-Germain- Lille OSC – Olympique Lyonnais
GermanyBayern Munich – Borussia Dortmund – RB Leipzig
ItalyJuventus – Napoli – Atalanta
SpainFC 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.

KeysItems
EnglandManchester City – Liverpool FC – Chelsea FC
GermanyBayern Munich – Borussia Dortmund – RB Leipzig
ItalyJuventus – Napoli – Atalanta
SpainFC 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s