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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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!