VBA create a nested dictionary

Question

I have the following set of code

Dim dic As Dictionary
Dim dataArray() As Variant
Dim headerRow(1 To 4) As Variant
Dim mySheet As Worksheet
Dim loopCounter As Long
Dim endRow As Long
Dim endColumn As Long
Dim keyColumn As Long
Dim x As Integer
Dim wsName2 As String
Dim duplicateDictionary As Dictionary

wsName2 = ActiveSheet.Name
Set duplicateDictionary = New Dictionary

Set dic = New Dictionary

With Worksheets(wsName2).Range("A1",Worksheets(wsName2).Range("A1").End(xlDown))
    keyColumn = 1
    endColumn = 4
    endRow = .Range("A1").End(xlDown).Row
    dataArray = Range(.Cells(1, 1), .Cells(endRow, endColumn)).Value
End With

For x = 1 To endColumn
    headerRow(x) = dataArray(1, x)
Next x

For loopCounter = 2 To endRow
    Dim storeKey As Variant
    Dim lineArray()
    Dim itemNumber As String
    Dim itemNumberAndDescription As String
    Dim q As Variant

    ReDim lineArray(1 To endColumn)

    For x = 1 To endColumn
        lineArray(x) = dataArray(loopCounter, x)
    Next x

    storeKey = lineArray(keyColumn)

    If Not dic.Exists(storeKey) Then
        dic.Add storeKey, New Collection
    End If

    'create duplicate dictionary if doesn't exist
    If Not duplicateDictionary.Exists(storeKey) Then
        duplicateDictionary.Add storeKey, New Dictionary
    End If

    dic(storeKey).Add lineArray

    itemNumber = UCase(Trim(Left(lineArray(2), InStr(1, lineArray(2), " -", vbBinaryCompare))))

    'add item in the duplicate dictionary based on the storekey
    duplicateDictionary(storeKey).Add itemNumber, itemNumber
Next loopCounter

What i'm trying to do, using the list below as an example, is to create a nested dictionary and each with their own key. The purpose for this is because I have a report that will checks the dictionary created above for duplicate item before adding to it.

City---Team

LA ----Lakers

CHI ----Bulls

NY ----Knicks

DAL ----Mavericks

BOS ----Celtics

I am able to create the first dictionary(city), but I can't seem to created a second (nested) dictionary for the team name based on the city. Help would be great. Thanks!

Answer 1

First create -- then add the new dictionary to the outer dict. The following sub should give you some idea:

Sub Test()
    Dim DOD As New Dictionary
    Dim InnerDict As Dictionary

    'create one inner dictionary
    Set InnerDict = New Dictionary
    InnerDict.Add "A", 1
    InnerDict.Add "B", 2
    'add it to the dictionary of dictionaries
    DOD.Add "dict1", InnerDict

    'create another inner dictionary
    Set InnerDict = New Dictionary
    InnerDict.Add "A", 3
    InnerDict.Add "B", 4
    InnerDict.Add "C", 5
    'add it to DOD
    DOD.Add "dict2", InnerDict

    'access like:
    Debug.Print DOD("dict1")("A") 'prints 1
    Debug.Print DOD("dict2")("A") 'prints 3

    'can add new keys to inner dicts:
    DOD("dict1").Add "C", 10
    Debug.Print DOD("dict1")("C") 'prints 10

End Sub