VBA – Printing Dictionary of a Dictionary of a Dictionary…

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:

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:

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:

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!

Tagged with: , , ,