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.
|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|
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) '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 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 () '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
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 '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 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 '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
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) '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, 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 '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