excelvbadictionary

How to create a dictionary that contains other dictionaries when we don't know their number at the beginning of the program in vba


I have the following code:

Sub Generate_report()
    Dim ws As Worksheet
    Dim kamagiData As Worksheet
    Dim incomesData As Worksheet
    
    Dim i As Long, lst As Long
    Dim currentRow As Long
    Dim currentTime As Variant
    Dim currentKamag As String
    Dim currentHour As Long
    
    Set ws = ActiveSheet
    Set kamagiData = Sheets("Kamagi")
    Set incomesData = Sheets("Dane wjazdy")
    
    Set hours = CreateObject("Scripting.Dictionary")
    Dim kamags As Dictionary
    
    
    With kamagiData
        lst = .Cells(.Rows.count, 1).End(xlUp).Row
        For i = 2 To lst
            Set kamags = New Dictionary
            currentTime = .Cells(i, 11).Value
            currentHour = Hour(currentTime)
            currentKamag = .Cells(i, 14).Value
            If Not hours.exists(currentHour) Then
                hours.Add currentHour, kamags
            End If
            'If Not hours(currentHour).exists(currentKamag) Then
                'hours(currentHour).Add currentKamag, 1
            'Else
                'hours(currentHour).Item(currentKamag) = hours(currentHour).Item(currentKamag) + 1
            'End If
            Debug.Print (currentHour)
            Debug.Print (hours(currentHour))
            Debug.Print (currentKamag)
            'Debug.Print (hours(currentHour).Item(currentKamag))
        Next i
    End With
    
    'Po utworzeniu odpowiednich słowników należy uzupełnić odpowiednie pola w tabeli, najpierw czyszcząc je z poprzednich wersji
    'Dodać przycisk czyszczenia raportów
    'Dodać instrukcję
    'Obsłużyć liczenie wjazdów/wyjazdów z yardu
    
End Sub

Its operating principle was to be as follows: In a loop, I check the table rows in the Kamagi sheet, extract the time from the date entered in column K, and the driver number, contained in column N. Then I need to create a division thanks to which I will be assigned the number of occurrences of the driver number in a given hour. The idea is to create a dictionary in which the keys will be the extracted hours, and the values ​​will be dictionaries containing driver - number of occurrences pairs. I have no problem creating a dictionary with hours, the problem starts when I try to add a new dictionary as a value to which I could add drivers and increase the counter of their occurrences. I am trying to do it in this line:

If Not hours.exists(currentHour) Then
  hours.Add currentHour, kamags
End If

Unfortunately, no matter what I try, I keep getting errors of all kinds. I have no idea how to properly initialize the dictionary so that later in the code I can operate on the dictionary element as a separate dictionary. I would be very grateful for any help on this topic.


Solution

  • Dictionary of Dictionaries

    Screenshot of Sample Data

    Sub GenerateReport()
        
        Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
        
        Dim wsKamagi As Worksheet: Set wsKamagi = wb.Sheets("Kamagi")
        Dim wsIncomes As Worksheet: Set wsIncomes = wb.Sheets("Dane wjazdy")
        
        Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
        
        With wsKamagi
            
            Dim LastRow As Long: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            
            Dim i As Long, cHour As Long, cKamag As String
            
            For i = 2 To LastRow
                cHour = Hour(.Cells(i, "K").Value)
                If Not dict.Exists(cHour) Then
                    Set dict(cHour) = CreateObject("Scripting.Dictionary")
                End If
                cKamag = CStr(.Cells(i, "N").Value)
                dict(cHour)(cKamag) = dict(cHour)(cKamag) + 1
            Next i
        
        End With
        
        Dim oKey As Variant, iKey As Variant
        
        Debug.Print "Hour", "Kamag", "Occur."
        
        For Each oKey In dict.Keys
            Debug.Print "Outer - " & oKey
            For Each iKey In dict(oKey).Keys
                Debug.Print , "Inner " & iKey, dict(oKey)(iKey)
            Next iKey
        Next oKey
                     
        ' ???
        'Po utworzeniu odpowiednich slowników nalezy uzupelnic odpowiednie pola w tabeli, najpierw czyszczac je z poprzednich wersji
        'Dodac przycisk czyszczenia raportów
        'Dodac instrukcje
        'Obsluzyc liczenie wjazdów/wyjazdów z yardu
        
    End Sub
    

    Result

    Hour          Kamag         Occur.
    Outer - 12
                  Inner A        2 
                  Inner C        1 
    Outer - 13
                  Inner B        2 
                  Inner A        1 
                  Inner C        1 
    Outer - 14
                  Inner B        1 
                  Inner A        1 
                  Inner C        1