excel - 如何访问嵌套字典的键？

Question

我尝试将一些数据存储在嵌套字典中。

然后我希望能够迭代这个嵌套字典并以某种方式输出数据。 (最终，在单元格中，但现在我只是想获取“最嵌套”的键)。

数据设置如下:

Recipient   Company InfoA   InfoB   InfoC   InfoD
John        CompanyA          123       
Jacob       CompanyB    322           44    
Smithy      CompanyC                
Smitherson  CompanyD     11    22            555

这个想法是循环遍历每一行数据，如果“Info[]”列中有数字，则根据信息/公司存储该数字，然后根据收件人存储。

这是我希望如何存储信息的“视觉效果”:

John            
    CompanyA        
        InfoB   123
Jacob           
    CompanyB        
        InfoA   322
        InfoC   44
Smitherson          
    CompanyD        
        InfoA   11
        InfoB   22
        InfoD   555

现在，使用我在 SE 上找到的 traversedictionary() 子程序，我能够成功循环遍历键并获取数据 - 但是，这是我的问题是，我似乎无法获取最终数据的“信息”部分。

这是构建字典的 VBA:

Sub create_dicts()
Dim final As Dictionary
Dim mini As Dictionary
Dim tmp As Dictionary

Dim dataRng As Range, cel As Range, iRow As Range
Set dataRng = Range("C2:F5")

Set final = New Dictionary

For Each iRow In dataRng.Rows
    Set mini = New Dictionary
    If WorksheetFunction.Sum(iRow) <> 0 Then
        Set tmp = New Dictionary
        For Each cel In iRow.Cells
            If cel.Value <> 0 Then
                ' Add that cell's number to a dictionary, with the Header as Key
                tmp.Add Cells(1, cel.Column), cel.Value
            End If
        Next cel
        ' Now that we've checked all cells in that row, add the resulting info to a dict for that Company
        mini.Add Cells(iRow.Row, 2), tmp
        ' Now that we have all info for that row/company, put in a dictionary
        ' with the RECIPIENT as the key
        final.Add Cells(iRow.Row, 1), mini
    End If
Next iRow
TraverseDictionary final
End Sub

我如何迭代它:

Private Sub TraverseDictionary(d As Dictionary)
Dim key As Variant
Dim depth As Long
Dim i As Long
'https://codereview.stackexchange.com/questions/63353/
    For Each key In d.Keys
        If VarType(d(key)) = 9 Then
            Debug.Print "KEY: " & key
            depth = depth + 1
            TraverseDictionary d(key)
        Else
            Debug.Print "ITEM: " & d(key)
        End If
        i = i + 1
    Next
End Sub

输出:

KEY: John
KEY: CompanyA
ITEM: 123
KEY: Jacob
KEY: CompanyB
ITEM: 322
ITEM: 44
KEY: Smitherson
KEY: CompanyD
ITEM: 11
ITEM: 22
ITEM: 555

我期待的是:

KEY: John
KEY: CompanyA
KEY: InfoB
ITEM: 123
...

因此，如您所见，我可以获得收件人，然后是公司，但无法显示“信息”部分。我错过/忽略了什么？

Answer 1

尝试以下遍历:

Private Sub TraverseDictionary(d As Dictionary, Optional ByVal depth As Long = 1)
Dim key As Variant
Dim i As Long

    For Each key In d.Keys
        If VarType(d(key)) = vbObject Then
            Debug.Print String(depth * 3, "-") & "KEY: " & key & " (dictionary)"
            TraverseDictionary d(key), depth + 1
        Else
            Debug.Print String(depth * 3, "-") & "ITEM: " & key & ": " & d(key)
        End If
        i = i + 1
    Next
End Sub

输出:

---KEY: John
------KEY: CompanyA
---------ITEM: InfoB: 123
---KEY: Jacob
------KEY: CompanyB
---------ITEM: InfoA: 322
---------ITEM: InfoC: 44
---KEY: Smitherson
------KEY: CompanyD
---------ITEM: InfoA: 11
---------ITEM: InfoB: 22
---------ITEM: InfoD: 555