VBA – Nested Dictionary

Nested dictionary means a dictionary within a dictionary. Some days ago last week I was trying to build one and it took me some solid 15 minutes to do it, thus I have decided that it is worth an article in my blog. So, in order to get access to the dictionary object in VBA, you need a reference to “Microsoft Scripting Runtime”:

The rest should be ok. The idea of the nested dictionary is to have a dictionary, that has key and value. And the value of this dictionary is also a dictionary. If you take a look at the picture below, the outer is our first dictionary and under outer(1) , outer(2) and outer(3) there are other dictionaries. In order to see their values, you should call them like outer(1)(10) , outer(2)(20) . The values “10” and “20” are the keys of the outer(1) and outer(2) dictionaries, correspondingly:

Ok, I guess it is way better to show it than to explain it. This is the code, creating 10 inner dictionaries in our outer dictionary:

Public Sub NestedDictionaryExample()
    
    Dim outer As Dictionary
    Dim inner As Dictionary
    
    Set outer = New Dictionary
    
    Dim i As Long
    For i = 1 To 10
        Set inner = New Dictionary
        inner.Add 10 * i, "Value of inner dictionary ..."
        inner.Add 100 * i, "Another value of inner dictionary ..."
        inner.Add 1000 * i, "Third value of inner dictionary ..."
        outer.Add i, inner
    Next i
    
    Dim innerKey As Variant
    Dim outerKey As Variant
    
    For Each outerKey In outer.Keys
        Debug.Print "Outer key:"; outerKey
        Debug.Print "Inner key: value"
        
        'PrintDictionary outer(outerKey)
        For Each innerKey In outer(outerKey)
            Debug.Print innerKey; ": "; outer(outerKey)(innerKey)
        Next innerKey
        Debug.Print "----------------"
        
    Next outerKey
    
End Sub

Public Sub PrintDictionary(myDict As Object)
    
    Dim key     As Variant
    For Each key In myDict.Keys
        Debug.Print key; "-->"; myDict(key)
    Next key
    
End Sub

It works and it produces what I wanted. It probably looks quite a lot of code, but about 70% is put there just to print correctly its values, so do not worry. This is the printed values in the immediate window:

Cheers!