Is it possible to create nested dictionaries from this sample data?

Question

I'm familiar with Collections and arrays, but now I'm trying to expand into dictionaries. I have a working excel VBA app that creates and stores construction pay applications. It's clunky and poorly coded thanks to yours truly. I've learned much since I wrote this program, and it's time to make needed performance and structural improvements.

Some of the challenges of working with the raw data is...

Creating new pay applications New pay applications need information from previous application Editing existing pay applications Archiving old pay applications The data is located in excel file on SharePoint and front end excel file is shared among different users.

I feel dictionaries could greatly simplify my code, but not sure if what I'm looking to do is possible. If it is possible, I really only need a nudge in the right direction. If not, do you have a suggestion of the best way to tackle this? I control the data, so I could break it up into separate tables if needed. I'd prefer to stay away from Access or SQL Server on the back end.

My sample data looks like this:

Project #	Application #	Item #	Description	Value
23-001	1	1	TEXT1	CURRENCY1
23-001	1	2	TEXT2	CURRENCY2
23-001	2	1	TEXT1	CURRENCY1
23-001	2	2	TEXT2	CURRENCY2
23-002	1	1	TEXT1	CURRENCY1
23-002	1	2	TEXT2	CURRENCY2
23-002	2	1	TEXT1	CURRENCY1
23-002	2	2	TEXT2	CURRENCY2

Hopefully this sample data is enough to communicate the pattern. My actual data is a couple thousand rows and 11 columns after Item #.

I'd like to see the dictionary look like this when the dictionary is put into a watch and expanded:

23-001
    1
        1
            Description
            Value
        2
            Description
            Value
23-002
    1
        1
            Description
            Value
        2
            Description
            Value

Looks just like the output of a pivot table...

Answer 1

Dictionaries, Collections, and Classes

• The outer dictionary's keys hold the Projects while each associated item holds another inner dictionary. Each inner dictionary's key holds the Application while each associated item holds a collection of the Property objects containing the 3 (11) properties like Item, Description, Currency... etc.
• BTW, VBE will never show the structure required unless you use arrays. You will have to imagine it.

An Example in a Standard Module e.g. Module1

Option Explicit

Sub DictDataTEST()

    Dim dict As Object: Set dict = DictData

    Dim Prop As cProps, pKey, aKey
    
    For Each pKey In dict.Keys
        Debug.Print "Product: " & pKey
        For Each aKey In dict(pKey)
            Debug.Print "Application: " & aKey
            Debug.Print "Properties:"
            For Each Prop In dict(pKey)(aKey)
                Debug.Print Prop.pId, Prop.pDescription, Prop.pCurrency
            Next Prop
        Next aKey
    Next pKey

    Debug.Print "1st Properties of 2nd Application of 1st Project (""23-001""):"
    
    Set Prop = dict("23-001")(2)(1)
    Debug.Print Prop.pId, Prop.pDescription, Prop.pCurrency

    Debug.Print "2nd Properties of 1st Application of 2nd Project (""23-002""):"
    
    Set Prop = dict("23-002")(1)(2)
    Debug.Print Prop.pId, Prop.pDescription, Prop.pCurrency

End Sub

The Function in a Standard Module e.g. Module1

Function DictData() As Object

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim rg As Range, rCount As Long
    
    With ws.Range("A1").CurrentRegion
        rCount = .Rows.Count - 1 ' exclude headers
        Set rg = .Resize(rCount).Offset(1)
    End With
    
    Dim Data(): Data = rg.Value
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim Prop As cProps, r As Long, Project As String, App As Long
    
    For r = 1 To rCount
        Project = CStr(Data(r, 1))
        If Not dict.Exists(Project) Then
            Set dict(Project) = CreateObject("Scripting.Dictionary")
        End If
        App = Data(r, 2)
        If Not dict(Project).Exists(App) Then
            Set dict(Project)(App) = New Collection
        End If
        Set Prop = New cProps
        Prop.pId = Data(r, 3)
        Prop.pDescription = Data(r, 4)
        Prop.pCurrency = Data(r, 5)
        dict(Project)(App).Add Prop
    Next r
                 
    Set DictData = dict

End Function

Class Module cProps

Option Explicit

Private m_pId As Long
Private m_pDescription As String
Private m_pCurrency As String
'
Public Property Get pId() As Long
    pId = m_pId
End Property

Property Let pId(pId As Long)
    m_pId = pId
End Property

Public Property Get pDescription() As String
    pDescription = m_pDescription
End Property

Property Let pDescription(pDescription As String)
    m_pDescription = pDescription
End Property

Public Property Get pCurrency() As String
    pCurrency = m_pCurrency
End Property

Property Let pCurrency(pCurrency As String)
    m_pCurrency = pCurrency
End Property

The Result

Product: 23-001
Application: 1
Properties:
 1            TEXT1         Currency1
 2            TEXT2         Currency2
Application: 2
Properties:
 1            TEXT3         Currency3
 2            TEXT4         Currency4
Product: 23-002
Application: 1
Properties:
 1            TEXT5         Currency5
 2            TEXT6         Currency6
Application: 2
Properties:
 1            TEXT7         Currency7
 2            TEXT8         Currency8
1st Properties of 2nd Application of 1st Project ("23-001"):
 1            TEXT3         Currency3
2nd Properties of 1st Application of 2nd Project ("23-002"):
 2            TEXT6         Currency6