I think the easiest way to print a Dictionary to an Excel spreadsheet is by using WorksheetFunction.Transpose( Variant type Array )
The below code
- Creates a sample Dictionary with keys and items
- Uses WorksheetFunction.Transpose(VariantArray) to print arrays in one go
Note: this does not involve any iteration to print the structure to the spreadsheet.
Tell me this isn’t quick an easy?
More on the topic:
We will use a recursive approach here. First we will pass a Dictionary to a procedure called TraverseDictionary() and then the procedure works out whether the Item is another Dictionary and if it is then it passes that Dictionary to a recursive call to TraverseDictionary()
First let’s have a look at the below visual representation of the complex Dictionary
Have have 3 different dictionaries here:
dict is the main Dictionary that includes other Dictionaries as subsets of .Item for particular Key s.
dict has 2 Key s:
FOO only stores a single String type: BAR .
BOO is a a bit more complex as it stores another Dictionary. subDict. as the Item for the BOO key.
subDict has 3 Key s:
HELLO and WORLD contains simple String type ITEM s: emoticons. ). (
OTHER is again a bit more complex as it stores another Dictionary. lvlDict. as its associated Item .
LvlDict stores a single KEY and ITEM pair: LVL KEY. LVL ITEM
Simplest code to build the above Dictionary structure could be:
Traversing and printing the complex Dictionary
The simplest way I could think of to traverse and print the structure was:
This somehow logically prints the structure to the Immediate Window (CTRL+G)
Ok, great but how does that output map to our first diagram?
How would we go about printing this to our spreadsheet?
Note: there probably is an unlimited number of ways to print our dict to a spreadsheet, but with the least modifications to the current code I came up with a solution that produces the following:
That way I can right away see the depth of nested Dictionaries. I can tell what is a subset of what. I can easily tell that:
- FOO and BOO and
the top assembly keys of dict
- BAR is a single ITEM on the FOO key
- BOO is more complex* (because it has a new KEY instead of an ITEM )
There we go… I cross out the first 3 out of the picture and repeat the process ’til I get to the last Item in dict Dictionary
NOTE: Sometimes, depending on the structure of data and algorithm – for example A Path Finding you would traverse back to find your way back to the top assembly root. We could locate the last item in the dictionary and then build the dictionary backwards but that would probably be an overkill for this simple demonstration. In this example I am only demonstrating the concept of recursively iterating a Dictionary so I am skipping any more advanced topics.
Here’s a full VBA solution and explanation:
I decided to add 2 global variables, i and depth .
The purpose of i is to know when to jump to the next row – it’s somehow related to the depth but not the depth variable but the result of the if-else statement in the TraverseDictionary(). Basically for each new key in each Dictionary you want to jump one row down.
The depth variable is used in the Range object .Offset property. It’s purpose is to shift to the right each time a new Dictionary is found. So like when there is a KEY. KEY pair you will be shifting. Note: you also shift when the KEY. ITEM pair occurs for the ITEM to appear next to the key ( one cell to the right of it ).
Both i and depth are assigned in the Main() procedure, right before the call to TraverseDictionary() .
Now, to understand how the TraverseDictionary() works I suggest you do the following:
Select each line as shown below in hit F9 to place a breakpoint (*or select line and on the toolbar click Debug -> Toggle breakpoint
Set the two windows side to side – so you can see what is going on on the spreadsheet and still control the flow in the VBE window.
Keep hitting F5 (or the green play icon ) to Step-Through code and place your cursor directly over variable names and watch the underlying values and the spreadsheet at the same time.Source: vba4all.com