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.
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.
| 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 |
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
A 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
SKILLS 