||Home| |New Reviews| |Software Methodologies| |Popular Science| |AI/Machine Learning| |Programming| |Java| |XML| |Software Tools| |Web| |Tutorials| |Other| |All By Date| |All By Title||
AS we discovered in the first part of this series on the VBA Dictionary Object, the Dicttionary Object gives VBA developers access to an associative array that allows us to store items in an array that is keyed by a value rather than just a numerical index. In other words you supply a unique key, such as a text string representing a name, and are able to store a value against it. Often this value is quite simple, a number, another string and so on. But what if what you want to store is another dictionary? What then?
Well, it turns out that this is straightforward to do using object references to access each of the dictionary objects that are stored in the main dictionary. First let's describe the scenario we want to model using a dictionary of dictionaries …
Imagine that each month we want to process the total sales of different products by salesperson. Each month we have a different range of products and different salespeople. We can't use fix arrays because we don't know in advance the number of different products or the people selling them. The data structure is simple though. Three columns of data: Name, Product, Number, with one row per record, and there can be multiple records for each person/product combination, as shown below:
AS shown previously, the first thing to do is to create a reference to the Microsoft Scripting Runtime, which is the DLL that contains the Dictionary (as well as the FileSystemObject). To do that we open Excel, hit F11 to get to the Visual Basic environment and then select References from the Tools menu. Navigate down through the list of available references and select Microsoft Scripting Runtime (\Windows\system32\scrrun.dll). Once that's done the Dictionary is available for you to use in your code.
With that in place we can write some code to process the data using the dictionaries. In this case we'll create a dictionary for each salesperson to include the total sales for each product they have sold. Each of these dictionaries will be stored in one main dictionary that will be keyed by the salespersons name.
Sub processData() Dim peopleDictionary As Dictionary Dim salesDictionary As Dictionary Dim r As Integer Dim salesPerson As Variant Dim prod As Variant Dim amount As Integer 'main dictionary to contain other dictionaries Set peopleDictionary = New Dictionary 'dictionary for sales - to be used once per person Set salesDictionary = New Dictionary r = 2 'first row contains header, so start at row 2 salesPerson = ActiveSheet.Cells(r, 1) prod = ActiveSheet.Cells(r, 2) amount = ActiveSheet.Cells(r, 3) 'keep processing data until we run out of salespeople... While Len(salesPerson) > 0 If peopleDictionary.Exists(salesPerson) Then 'sales person already has a sales dictionary - so lets grab it Set salesDictionary = peopleDictionary(salesPerson) If salesDictionary.Exists(prod) Then 'already have a total for this product, so lets update it amount = amount + salesDictionary(prod) salesDictionary(prod) = amount Else 'new product, so add it to dictionary with current amount salesDictionary.Add prod, amount End If Else 'new salesperson, so create dictionary and add product total Set salesDictionary = New Dictionary salesDictionary.Add prod, amount 'add salesperson and their dictionary to the main dictionary peopleDictionary.Add salesPerson, salesDictionary End If r = r + 1 salesPerson = ActiveSheet.Cells(r, 1) prod = ActiveSheet.Cells(r, 2) amount = ActiveSheet.Cells(r, 3) Wend 'And we can iterate through the complete dictionary to show what we have For Each salesPerson In peopleDictionary.Keys Set salesDictionary = peopleDictionary(salesPerson) For Each prod In salesDictionary.Keys Debug.Print "Name: " & salesPerson & _ " : Product: " & prod & " : Sales: " & salesDictionary(prod) Next Next End Sub
This is all very straightforward apart from where we re-use the saleDictionary variable. At first glance you'd think we would be overwriting this constantly because we use the same variable name to point to different peoples sales figures. However, the trick is in the use of the Set keyword. In object oriented terms Dictionary variables are what are termed object references rather than basic variables like Integer or Float. A Dictionary variable is a label that can be attached and re-attached to different data structures. In our case we simply re-use the variable salesDictionary to point to different instances of Dictionary objects, so we never over-write one person's data with anothers.
And that's all there is to using one dictionary object to contains others. In real life you'd be more likely to handle the example shown here using a Pivot table, but there are plenty of situations where an array of dictionary objects makes a lot of sense to use.