Dictionaries come in handy when you have to manipulate data structures that are complex to describe and the lists have their limits. Dictionaries are unordered collections of objects, that is, there is no notion of order. Access to dictionary items is by keys. No loop is needed to access a specific key, you will just need to call it!

If you are not comfortable with the dictionary concept, before consulting this article, I suggest you take a look at the generalities of the dictionary, here.

In the general article, we mainly attached a single item to a key. In reality for analysis purposes, we will mainly need to attach several items to a key. We will, therefore, have a choice between different types of items. To use the dictionary object optimally the question to ask is the following: according to my data set what are the best objects to use to be stored as an item? If your need is to increment items you will need to ask yourself the right questions about the storage of your items. Different possibilities are available to you: a character string, an array, a custom type, a class or even another dictionary?

Asking yourself this question is essential and will allow you better efficiency and better execution time of your VBA macro in Excel.

As in the generalities article for a better understanding of the items in the dictionary object, we will illustrate the uses 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 RankingTeamNumber of points (PTS)Match Played (Pld)Match Won (W)Match Draw (D)Match Loss (L)Goals For (GFGoals 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

An Array as items of a Dictionary in VBA

To store multiple items for a key, you can use an Array.

If you do not declare the Option Base 1  at the start of the VBA module, the first item in your array of items will be in position zero.

The array of items is a helpful method for multiple storages, however, you will not be able to increment items. To do it, you should store your items in a temporary array, and increment outside the dictionary.

To access the different elements of an item table, you must loop over each key on the dictionary using a For Each tool. As mentioned if no base option has been declared, the first item in the table will be the item in position 0.

Sub CreateDictionaryWithArrayOfItems()
'The dictionary object in VBA, speedway to process data
'This example stock for one key multiples items thanks to an array
'Key:=Name of the team & Item:=(NbPTS, Country, League name)

    Dim DictFootData As Object
    Dim TeamName As String
    Dim NbPTS, ColTeamName, ColNbPTS, ColCountry,ColLeagueName As Integer
    Dim NbRowsDatabase, iRow As Long
    Set DictFootData = CreateObject("Scripting.Dictionary")
    ColTeamName = 4
    ColNbPTS = 5
    ColCountry = 1
    ColLeagueName = 2
    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

        'Test if the key exists in the dictionnary
        'We add an array as items with 3 data: points, country, league
        If Not DictFootData.Exists(Cells(i, 1).Value2) Then
        DictFootData.Add Key:= TeamName, Item:= Array(Cells(i, ColNbPTS).Value2, _
            Cells(i, ColCountry).Value2, Cells(i, ColLeagueName).Value2)
        End If

    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

        With Sheets("Sports")
             .Cells(iRow,1).Value2 = k 'write the key
             .Cells(iRow,2).Value2 = DictFootData.Item(k)(0) 'Number of points
             .Cells(iRow,2).Value2 = DictFootData.Item(k)(1) 'Country
             .Cells(iRow,2).Value2 = DictFootData.Item(k)(2) 'Name
        End With

        iRow = iRow+1

    Next k
    'Free the variable 
    Set DictFootData = Nothing
End Sub

Increment an element of the item Array

To increment an element of an item array, it is mandatory to go through a temporary array, to increment on this temporary array and to restock it as an item in the dictionary.

Unfortunately, it is not possible to increment directly on the element of the table when it is attached to the dictionary.

This is why the best, most efficient and optimal method is to use the class module objects as an item. You will not experience this incrementation problem on a class object.

Sub IncrementElementArrayOfItemInDictionary () 

    Dim DictCountryData As Object
    Dim Country As String
    Dim NbPTS, ColTeamName, ColNbPTS, ColCountry, ColLeagueName As Integer
    Dim NbRowsDatabase, iRow As Long
    Dim TempArr As Variant

    Set DictCountryData = CreateObject("Scripting.Dictionary")
    ColTeamName = 4
    ColNbPTS = 5
    ColCountry = 1
    ColLeagueName = 2
    NbRowsDatabase = 40
    'Loop on the array to add a key-item pair
    'Here we add the country as the key and the number of points
    For iRow = 2 To NbRowsDatabase

        Country = Cells(iRow, ColCountry).Value2

        'Test if the key exists in the dictionary
        'We add an array as item with 3 data : points, country, league

        If Not DictCountryData.Exists(Country) Then

            DictCountryData.Add Key:= Country, _
               Item:= Array(Cells(iRow, ColLeagueName).Value2, _ 
                   Cells(iRow, ColNbPTS).Value2)

        ElseIf Not DictCountryData.Exists(Country) Then
            'Not possible to write this, the incrementation 
            'of points for the same country not work with this
            'DictCountryData.Item(Country)(1) = DictCountryData.Item(Country)(1) + Cells(i, ColNbPTS).Value2
            'We need to use a temporary aray
            TempArr = DictCountryData(Country) 'copy the table
            TempArr(1) = TempArr(1) + Cells(i, ColNbPTS).Value2 'increment the item
            DictCountryData(Country) = TempArr 're-copy the table in the dictionary

        End If

    Next iRow

End Sub

Class object as an item of a Dictionary in VBA (BEST METHOD)

We will see here, which corresponds to one of the best methods to process a dataset. The combination of the dictionary object with objects that you will have created through class modules.

If you are not adept with object-oriented programming through class module you can consult this article which will give you more explanations. It is not difficult to understand the concept and apply it. It will be the best tool that you can easily use and reuse for your different jobs, especially when you are confronted with the same data.

In the following example, we will go through the football data game by creating objects from our class module cClub then we will store the name of the club in key and the class object as item. A key will, therefore, have through its item a class club object which will have all the attributes and method which will have been defined in the class module.

So we will start by creating the class module cClub. We insert a class module in our code and we can write all attributes and methods (below a method to display the rank of the team created by the class module cClub) required :

Public Country, LeagueName,TeamName As String
Public FinalRanking,NbPTS, NbMatchPLD, NbMatchWon,NbMatchDraw As Integer
Public NbMatchLoss,NbGoalsFor, NbGoalsAgaint, NbGoalsDiff As Integer
Sub DisplayRankClub(C As cClub)
    MsgBox C.TeamName & " finished " & C.Country _
                    & " of the " & C. LeagueName & "."
End Sub

Then we will browse the dataset and create our dictionary of class cClub objects:

Public DictClubData As Object

Sub CreateDictionaryWithClassObjectsOfItems()
'The dictionary object in VBA, speedway to process data
'This example stock for one key multiples items with an object of the class module
'Key:=TeamName & Item:= cClub object

    Dim TeamName As String
    Dim ColTeamName, ColCountry,ColLeagueName,ColFinalRanking,ColNbPTS, As Integer
    Dim ColMatchPlayed, ColMatchWon, ColMatchDraw, ColMatchLoss As Integer
    Dim ColGoalsFor, ColGoalsAgainst, ColGoalsDiff As Integer
    Dim NbRowsDatabase,NbPTS, iRow As Long

    Dim Clb As cClub 'Declaration of an object from our class cClub
    Set DictClubData = CreateObject("Scripting.Dictionary")
    ColCountry = 1
    ColLeagueName = 2
    ColFinalRanking = 3
    ColTeamName = 4
    ColNbPTS = 5
    ColMatchPlayed = 6
    ColMatchWon = 7
    ColMatchDraw = 8
    ColMatchLoss = 9
    ColGoalsFor = 10
    ColGoalsAgainst = 11
    ColGoalsDiff = 12
    NbRowsDatabase = 40
    'Loop on the dataset array to add a key-item pair
    'Here we add the TeamName as the key and we will 
    'create and stored a cClub object as item

    For iRow = 2 To NbRowsDatabase

        TeamName = Cells(iRow, ColTeamName).Value2

        'We add the type Clb as item
        If Not DictClubData.Exists(TeamName) Then
            'this line is essential and allows you to create a new object cClub

            Set Clb = New cClub '

            'we associate with each attribute a value
            Clb.Country = Cells(iRow, ColCountry).Value2
            Clb.LeagueName = Cells(iRow, ColLeagueName).Value2
            Clb.TeamName = Cells(iRow,ColTeamName).Value2
            Clb.FinalRanking = Cells(iRow, ColFinalRanking).Value2
            Clb.NbPTS = Cells(iRow, ColNbPTS).Value2
            Clb.NbMatchPLD = Cells(iRow, ColMatchPlayed).Value2
            Clb.NbMatchWon = Cells(iRow, ColMatchWon).Value2
            Clb.NbMatchDraw = Cells(iRow, ColMatchDraw).Value2
            Clb.NbMatchLoss = Cells(iRow, ColMatchLoss).Value2
            Clb.NbGoalsFor = Cells(iRow, ColGoalsFor).Value2
            Clb.NbGoalsAgaint = Cells(iRow, ColGoalsAgainst).Value2
            Clb.NbGoalsDiff = Cells(iRow, ColGoalsDiff).Value2

            'We add in the Dictionary the pair TeamName and the class object 
            'with all attributs defined above
            DictClubData.Add TeamName, Clb

        End If

    Next iRow

    Set Clb = Nothing

     '--> at this level, all the data of the table are stored 
     'in memory and easily accessible, without loop
End Sub

The dictionary having been defined, we can now easily and quickly access any attribute of the items, without using a loop, just dig into the data in memory.

Sub DisplayStoredDataWithType()
At this moment your dictionary contains all teams
'and data associated with table
    Dim iRow As Long
    Dim k As String

    iRow = 1

    'Loop on keys of the dictionary
    'to access each item of each key
    For Each k in DictClubData.Keys

        With Sheets("Sports") 'we are in the sheet Sports
            .Cells(iRow,1).Value2 = k 'write the key TeamName
            .Cells(iRow,2).Value2 = DictFootData.Item(k).NbPTS 'number of points
            .Cells(iRow,3).Value2 = DictFootData.Item(k).Country 'country
            .Cells(iRow,4).Value2 = DictFootData.Item(k).TeamName 'name
        End With

        iRow = iRow + 1

    Next k

End Sub
Football game

Type as an item of a Dictionary in VBA

We will now see how to store a custom type that you defined beforehand, in a dictionary.

For more information on statement types, see this article.

The objective here is to create a Club type with different attributes. Each club will be stored in the dictionary with the name of the club in key and the personalized type in item completed beforehand.

Public Dim DictClubData As Object

Type Club
'Define a data type Person
'You can also declare the type as Public or Private

    TeamName As String
    Country As String
    LeagueName As String
    NumberPoints As Integer

End Type

Sub CreateDictionaryWithTypesOfItems()
'The dictionary object in VBA, speedway to process data
'This example stock for one key multiples items with the type element
'Key:=Name of the team & Item:=Club (Type)

    Dim TeamName As String
    Dim ColTeamName, ColCountry, ColLeagueName, ColMatchWon As Integer
    Dim NbRowsDatabase, iRow As Long
    Dim DictClubData As Object
    Set DictClubData = CreateObject("Scripting.Dictionary")
    ColCountry = 1
    ColLeagueName = 2    
    ColMatchWon = 7
    NbRowsDatabase = 40
    'we add the type Clb as item
    Clb.TeamName = "Bayern Munich"
    Clb.NumberPoints = 78
    Clb.Country = "FRANCE"

    DictClubData.Add Clb.TeamName, Clb

End Sub

This method is less robust than storing class objects because you can only store attributes, objects have no method associated and it is more complicated than with the associated object class method an item key pair with a type for each key.

Dictionary as an item of a Dictionary in VBA

We can also do an abyss of dictionaries. Indeed, it is possible to declare a dictionary as an item of a key in a dictionary.

Sub DictionaryInDictionary()
'Add a dictionary in a dictionary object as an item

    Dim DictCountry As Object
    Dim DictClub As Object
    Set DictCountry = CreateObject("Scripting.Dictionary")
    Set DictClub = CreateObject("Scripting.Dictionary")

    'Add an pair key-item in the Club dictionary    
    DictClub.Add "PSG", 1
    DictClub.Add "Olympique de Marseille", 5

    'Add DictClub as an item of the key France in the Country dictionary
    DictCountry.Add "FRANCE",  DictClub
    'Display the item of FRANCE key in the Country dictionary
    'Which is the dictionary Club, and we ask to print the item
    'of a club

    MsgBox DictCountry.Item("FRANCE").Item("PSG") 'Display 1
    MsgBox DictCountry.Item("FRANCE").Item("Olympique de Marseille") 'Display 5

    'Free variables
    Set DictCountry = Nothing
    Set DictClub = Nothing
End Sub

Leave a Reply

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

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