Dictionary – Items

Last Updated on February 3, 2022 – 4 min read

In VBA, dictionaries are essential for data processing to save time. Dictionaries are unordered collections of a key-item pairs.

The VBA dictionary object links keys to items. You don’t need to loop on the dictionary to access a specific key. You can directly call the key. Unlike the Collection object, the Dictionary object doesn’t allow you to retrieve an item by its ordinal position.

In this article, you will see several objects to attach as items for a key in a dictionary.

If you are not comfortable with the dictionary concept, you should read the article on dictionary generalities.

South of Thailande – Photo taken by me

In the dictionary generalities article, we only attached a single value as an item of a key. As part of data analysis in VBA, you will need to attach multiple values as items to a unique key.

To use the dictionary object you need to ask yourself this question: according to your data what are the best objects to stored as items of your key dictionary? Different possibilities are available: a value, an array, a custom type, a class object, or another dictionary.

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 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

Dictionary Items as Arrays

To store multiple items on a key, you can use an array.

If Base Option 1 is not declared at the beginning of your module, the first item in your array will be at position 0.

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

    'Declaration
    Dim DictFootData As Object
    Dim TeamName As String
    Dim NbPTS, ColTeamName, ColNbPTS, ColCountry,ColLeagueName As Integer
    Dim NbRowsDatabase, iRow As Long
 
    'Initialisation
    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
    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 an array    
 
    '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 an array as item

The array as an item is helpful for multiple storages, however, VBA will not give you the possibility to increment the value of your items in the array without using a temporary array not attached to your dictionary.

Sub Increment_Element_Array_Item_Dictionary () 

    'Declaration
    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

    'Initialisation
    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

To avoid this incrementation issue, the most efficient and optimal method is to store a class module object as an item of your dictionary key.


Dictionary Items as Classes Objects

dictionary object with class modules as an item of a key is the most efficient method to store and process data.

If you are not adept with object-oriented programming through a class module, you can consult the following article.

In the following example, we loop through the football dataset to create objects from our class module cClub. We store the club name as the key in the dictionary and the class object as the item.

We start by creating the class module cClub and adding all attributes and methods.

Below is an example to display the rank of the team created with the class module cClub:

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

Now, we loop on our dataset and we create a dictionary with class objects cClub stored as an item of a key dictionary:

Public DictClubData As Object

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

    'Declaration
    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
 
    'Initialisation
    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 is now ready to be used. You can easily and quickly access an item of a key without looping on the keys, just by fetching data in the dictionary.

Sub Display_Stored_Data()
At this moment your dictionary contains all teams
'and data associated with table
   
    'Declaration
    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

Dictionary Items as Types

You can also store a custom type as an item in your dictionary. For more information on a Type, you can consult this article.

The purpose is to create a Type Club with several attributes. Clubs data is stored in the dictionary with the club name as the key and the personalized Type Club as an item.

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 Create_Dictionary_With_Type_Items()
'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)

    'Declarations
    Dim TeamName As String
    Dim ColTeamName, ColCountry, ColLeagueName, ColMatchWon As Integer
    Dim NbRowsDatabase, iRow As Long
    Dim DictClubData As Object
 
    'Initialisations
    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. A type can’t have methods.

Dictionary Items as Dictionaries

You can also create an abyss of dictionaries. It is possible to add a dictionary as an item of a dictionary key.

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

    'Declarations
    Dim DictCountry As Object
    Dim DictClub As Object
   
    'Initialisations
    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:

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