Generalities

Do you have a lot of data to manipulate on your Excel sheet, and you want to work efficiently with suitable objects? A dictionary is an essential object for this type of programming, allowing you to store and access a large amount of data.

Dictionaries are implementations of a data structure more commonly known as an associative array. Your data will be stored in the machine’s memory and not in your sheets.

dictionary consists of a collection of key-item pairs. The key is always associated with an item. There is uniqueness of the key. The item can take multiple forms. 

This first article on dictionaries, presents the general use, how to create a dictionary, add new keys, delete them … A second article on this blog is devoted to the storage and handling of items and answers questions such as : what types of items can we store? How to store several items for a key? How to increment values in items?

A dictionary is a powerful object in VBA. It is possible to store different object as an item of a dictionary, whether it is a variable, an array, a custom type or even a class object. Access to the dictionary key and therefore to associated items is done very quickly by the code. It is an essential object for all VBA programmers (but also for python programmers).

For a better understanding of the dictionary object, we will illustrate the uses in this article through the following sports dataset which represents the football results of the three best clubs over the 2018-2019 season of the five biggest European championships.

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

Create a dictionary

To create a dictionary in VBA, we will use the CreateObject and insert the “Scripting.Dictionary” method, as when we create a WordOutlook or PowerPoint objects.

The dictionary can be declared as an Object.

Then we have access to all the dictionary methods and properties, including the addition of a key-item pair to the dictionary using the Add property.

Sub CreateDictionaryManually()
    
    '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"
    
    '...your instructions...

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

End Sub

You may have seen the syntax for using a dictionary with the above procedure but it is obvious that using the Dictionary only makes sense when it is used in combination with a loop that will allow you to fill in the Dictionary from the data (text file, content of an Excel file, database …).

So we’re going to loop on the table to fill the dictionary then we will loop on the keys of the dictionary to access each item of each key in the macro below.

Sub CreateDictionaryLoopDatabase()
    
    '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 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

Football

You have therefore seen how to create a dictionary, the general functioning as well as the storage of an item. The power of the dictionary object does not lie in the storage of a single item, but the handling and storage of several items for the same key. For more information, see this article.

Test the existence of a key’s dictionary

Remember each key must be unique. The first dictionary example did not require testing the existence of the key each time it was added to the dictionary because we knew that in our database each team was unique and had a line. We would now like to create a dictionary which will store each country and which will store an item in a character chain the name of each team of this country. We will, therefore, have to make an existence test.

It is important to clearly define what is the key to its data. This may be a variable, but also the concatenation of several variables. The first time the country key will be added with the first item, then if the key already exists we will concatenate the item of this key.

To test the existence of your key, you can use the method Exists with the key as a parameter.

Sub CreateDictionaryTestKeyExists()
    
    '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

    'Free the variable 
    'Set DictFootCountry = Nothing

End Sub

At the end of the execution (without the release of the variable) your dictionary will be made up of 4 countries keys and the names of the sports clubs will concatenate into 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

Remove a key’s dictionary

Assuming that the DictFootCountry dictionary was previously filled and declare as a public variable outside any procedure, we can exclude a key-item pair from this dictionary using the Remove method.

It is also possible to remove all keys from the dictionary using the RemoveAll instruction.

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

After the execution of the line code DicoFootCountry.Remove(“France”), your dictionary will only be composed of 4 countries.

KeysItems
EnglandManchester City – Liverpool FC – Chelsea FC
GermanyBayern Munich – Borussia Dortmund – RB Leipzig
ItalyJuventus – Napoli – Atalanta
SpainFC Barcelona – Atlético de Madrid – Real Madrid

Count the number of key in the dictionary

A dictionary in VBA can store a large amount of data, to find out the number of keys stored in your dictionary, use the Count method.

Assuming that the DictFootData dictionary was previously filled and declare as a public variable outside any procedure, we can count the number of keys stored, it will have as many keys as clubs, so 15.

Public DictFootData As Object

Sub CountKeysInDict()
  
   Dim NbKeys As Integer 

   NbKeys = DictFootData.Count 'Here NbKeys = 15

End Sub

Dictionary for MAC OS

If you are a VBA user on Mac Os, it is not possible to create ActiveX applications. The use of VBA is, therefore, more limited under MAC OS.

It is still possible to create a dictionary, thanks to the creation of class modules. To learn more about the class modules, follow this link.

If you want to create a dictionary with MAC OS, I recommend that you download this class module available under Github. Then insert this class module into your project.

Sub DictionaryInMacOs()
'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 site.

Dictionary icon

One Reply to “Generalities”

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 )

Google photo

You are commenting using your Google 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