excel - 在VBA中递归打印下一个字典

Question

我想在 VBA 中打印嵌套字典。基本上我有一个 Dictionary，其中每个键都是一个 String，但每个值都可以是 String 或另一个 Dictionary >.

假设我的字典已获得值

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

我想在 Excel 电子表格中显示:

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

我的问题是我需要找到一种方法来猜测每个键下值的类型是什么，因此当我调用 dict("HELLO") 时，我可以显示该值，如果它是一个字符串，或者如果它是一个字典，则再次调用相同的函数。

为了做到这一点，我需要知道:

• 是否有办法知道存储在字典中的值的类型
• 是否有办法将该值转换为目标类型(字符串或字典)

这就是我尝试过的

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

Answer 1

我实际上会提出一种不同的方式来显示结果。我认为这更符合逻辑，但欢迎您对其进行修改以满足您的特定需求。正如提示打印节点的逻辑树，如下所示，然后在需要时操作结果。

因此树看起来像这样(注意我添加了一个深度级别)

以及要重现的代码

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

和电子表格结果:

<小时/>

要获取变量类型或其类型名称，您可以使用以下命令:

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