CODEWITHSUNDEEP
excelvbadictionary
BillyRay
BillyRay

Reputation: 413

How to save a VBA nested dictionary to excel cells?

Not sure how to save a nested dictionary created with Visual Basic Applications to an excel spreadsheet. Would it be best to loop and save or use Application.Transpose(dic.Keys)? If we use Transpose, how can we save the inner keys as well?

Sub GenerateCatalogList()

Dim dictA As New Scripting.Dictionary
Dim dictB As New Scripting.Dictionary

dictB.Add "Catalog", "XXX DJSQVOS"
dictB.Add "Qty", 1

dictA.Add "Item_1", dictB

' Save Transposed dictionary here?
Range("G3").Value = WorksheetFunction.Transpose(dictB.Items)

Dim outerKey As Variant
Dim innerKey As Variant

For Each outerKey In dictA.Keys
    Debug.Print "Main key: "; outerKey
    Debug.Print "inner key: "
    For Each innerKey In dictA(outerKey)
        Debug.Print innerKey; ": "; dictA(outerKey)(innerKey)
        
        ' save dictionary here?
        Range("F3").Value = WorksheetFunction.Transpose(dictA(outerKey)(innerKey))
        
        
    Next innerKey
    Debug.Print "---------------"
Next outerKey

        
End Sub

Upvotes: 0

Views: 514

Answers (1)

CDP1802
CDP1802

Reputation: 16184

As you sure you need nested dictionaries ?

Option Explicit

Sub GenerateCatalogList()
    Dim dictA As New Scripting.Dictionary, key, cell As Range
    
    dictA.Add "Item_1", Array("XXX DJSQVOS", 1)
    dictA.Add "Item_2", Array("AAA DJSQVOS", 2)
    dictA.Add "Item_3", Array("BBB DJSQVOS", 3)
    dictA.Add "Item_4", Array("CCC DJSQVOS", 4)
    
    Range("F2:H2") = Array("Item", "Catalog", "Qu")
    Set cell = Range("F3")
    For Each key In dictA.Keys
        cell = key
        cell.Offset(0, 1).Resize(1, 2) = dictA(key)
        Set cell = cell.Offset(1)
    Next
End Sub

Upvotes: 1

Related Questions