Working with dictionaries in VBA is a bit more unpleasant, than working with dictionaries with almost any other language. Because of the VBA IDE, you only see the keys, but not the values. Thus, if you have experience with Visual Studio (or any other nice IDE), you will be happy never to return back to the ugly Excel macro language with its IDE that looks like being built in 1997. Anyway, enough hating over the old IDE, and let’s see what we can do about it?
Three pink scooters in Sofia
Print a dictionary of strings
First thing you can do is probably to see the value of the dictionary, in the immediate window like this – ?dictionary("key") . The second thing is to print the whole dictionary in the immediate window. This is what PrintDictionary will do, printing a simple 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 |
Sub Main() Dim dict1 As New dictionary dict1.Add "Bulgaria", "Sofia" dict1.Add "Germany", "Berlin" dict1.Add "Russia", "Moscow" PrintDictionary dict1 End Sub Public Sub PrintDictionary(myDict As dictionary, Optional isCollection = False) Dim myKey As Variant For Each myKey In myDict.Keys Debug.Print myKey If isCollection Then Dim myElement As Variant For Each myElement In myDict(myKey) Debug.Print myElement Next Debug.Print "----------------" Else Debug.Print vbTab & myDict(myKey) End If Next End Sub |
Print a dictionary of collections
What if we had a list in our dictionary? E.g., instead of “Sofia”, we have added “Plovdiv” and “Pleven” to “Bulgaria”? Let’s see – then changing the optional parameter isCollection to True will be do quite a nice job:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub Main() Dim dict1 As New dictionary dict1.Add "Bulgaria", New Collection dict1.Add "Germany", New Collection dict1.Add "Russia", New Collection dict1("Bulgaria").Add "Sofia" dict1("Bulgaria").Add "Pleven" dict1("Bulgaria").Add "Plovdiv" dict1("Russia").Add "Moscow" dict1("Russia").Add "Kazan" PrintDictionary dict1, True End Sub |
Print a dictionary of dictionary of dictionary …
Now, let’s go even further. Making a nested dictionary will require some more lines. Printing it as well. As far as I do not want to involve recursion into printing it (although this is probably the best way), I will put 2 flags for the levels of nesting. So, let’s imagine that we have Bulgarian cities, with Bulgarian neighbourhoods in them with places in each neighbourhood, saying something interesting about a place. We can put 3 nested dictionary to display this:
|
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 42 43 44 45 46 47 48 49 |
Public Sub PrintNestedDictionary(myDict As Dictionary, Optional isNested1 = False, Optional isNested2 = False) Dim myKey As Variant For Each myKey In myDict.Keys Debug.Print myKey If isNested1 Then Dim myElement As Variant For Each myElement In myDict(myKey).Keys Debug.Print vbTab & myElement If isNested2 Then Dim myElement2 As Variant For Each myElement2 In myDict(myKey)(myElement).Keys Debug.Print vbTab & vbTab & myElement2 Debug.Print vbTab & vbTab & vbTab & myDict(myKey)(myElement)(myElement2) Next End If Debug.Print "----------" Next Debug.Print "----------" Else Debug.Print myDict(myKey) End If Next End Sub Sub Main() Dim DruzhbaPlaces As New Dictionary DruzhbaPlaces.Add "The Chinese Restaurant", "A nice place" DruzhbaPlaces.Add "The carwash", "A place to wash your car" DruzhbaPlaces.Add "The school", "This is where we studied" Dim MladostPlaces As New Dictionary MladostPlaces.Add "The Business Park", "Where I used to work!" MladostPlaces.Add "McDonald's", "Where I was invited to party! :)" Dim SofiaNeighbourhoods As New Dictionary SofiaNeighbourhoods.Add "Druzhba 2", DruzhbaPlaces SofiaNeighbourhoods.Add "Mladost 2", MladostPlaces Dim BulgarianCities As New Dictionary BulgarianCities.Add "Sofia", SofiaNeighbourhoods BulgarianCities.Add "Plovdiv", New Dictionary PrintNestedDictionary BulgarianCities, True, True End Sub |
As you see, in Sofia, we have “Druzhba” and there we have “The carwash”, which is “A place to wash your car”. Let’s see how this is going to be displayed:
Well, as expected. Enjoy!