Recursively type following dictators in VBA

Question

Recursively type following dictators in VBA

I would like to print nested dictionaries in VBA. Basically I have Dictionary

where each key is String

, but each value can be either String

or else Dictionary

.

Tell me mine Dictionary

has meaning

{ "FOO" => "BAR" , "HELLO" => { "WORLD => ":)", "OTHER" => ":(" } }

I want to display in an Excel spreadsheet:

FOO  |BAR  |
HELLO|WORLD|:)
HELLO|OTHER|:(

My problem is that I need to find a way to guess what the type of the value under each key is, so when I call dict("HELLO")

, I can either display the value if it is a string, or if the dictionary calls again.

To do this, I need to know:

if there is a way to find out what type of value is stored in the dictionary
if there is a way to pass this value to the target type (string or dictionary)

So here is what I tried

Function display_dictionary(dict As Scripting.Dictionary, out As Range) As Integer

  Dim vkey As Variant
  Dim key As String
  Dim row_offset As Integer
  Dim value As Object
  Dim svalue As String
  Dim dvalue As Dictionary
  Dim each_offset  As Integer

  row_offset = 0

  For Each vkey In dict.Keys()
    key = vkey
    Set value = dict(key)
    if value is String then
       svalue = ???
       out.offset(row_offset, 0).value = key
       out.offset(row_offset, 1).value = svalue
       row_offset = row_offset + 1
    else if value is Dictionary
       dvalue = ???
       each_offset = display_dictionary(dvalue, out.offset(row_offset, 1))
       For each_row = 0 To each_offset - 1
        out.offset(row_offset + each_row) = key
       Next
       row_offset = row_offset + each_offset
    End If
  Next

End

+3

dictionary vba data-structures excel recursion

Arthur Sep 18 '14 at 9:48

source to share

1 answer

user2140173 · Accepted Answer · 2014-09-18T09:59:39+0000

I'm going to suggest a slightly different way to display the results. I think this is more logical, but you can change it according to your specific needs. Just as a hint, print the logical node tree as shown below and then process the results as needed.

So the tree will look like this, for example (note, I added one more depth level)

dictionary tree

and the code to play

Private i As Long
Private depth As Long

Sub Main()
Cells.ClearContents

    Dim dict As New Dictionary
    Dim subDict As New Dictionary
    Dim lvlDict As New Dictionary

    lvlDict.Add "LVL KEY", "LVL ITEM"

    subDict.Add "HELLO", ":)"
    subDict.Add "WORLD", ":("
    subDict.Add "OTHER", lvlDict

    dict.Add "FOO", "BAR"
    dict.Add "BOO", subDict

    i = 1
    depth = 0
    TraverseDictionary dict

    Columns.AutoFit

End Sub

Private Sub TraverseDictionary(d As Dictionary)

    For Each Key In d.Keys
        Range("A" & i).Offset(0, depth) = "KEY: " & Key
        If VarType(d(Key)) = 9 Then
            depth = depth + 1
            TraverseDictionary d(Key)
        Else
            Range("B" & i).Offset(0, depth) = "ITEM: " & d(Key)
        End If
        i = i + 1
    Next
End Sub

and the result of the spreadsheet:

enter image description here

To get the type of a variable or its type name, you can use this:

Debug.Print TypeName(dict("HELLO")), VarType(dict("HELLO"))

Recursively type following dictators in VBA

More articles: