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|
|Spain||Liga||2||Atlético de Madrid||76||38||22||10||6||55||29||26|
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