---

A Dictionary of Dictionaries

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.