I am creating a VBA code to summarize the value of specific Unit in one excel sheet from a certain time interval and show result in another sheet

Question

Here, i created a dictionary to store the value that is the sum of all the same Unit (key), and then showing the result in another sheet by taking the key-value in dictionary. But the value in dictionary after summation is always Zero, what is missing in my code?

Private Sub btnTonghop_Click()
    Dim wsDulieu As Worksheet
    Dim wsKetqua As Worksheet
    Dim donviDict As Object
    Dim donviRun As Variant
    Dim donvi As Variant
    Dim rng As Range
    Dim cell As Range
    Dim startDate As Date
    Dim endDate As Date
    Dim nextRow As Long
   
    Set wsDulieu = ThisWorkbook.Sheets("BangTonghop")
    Set wsKetqua = ThisWorkbook.Sheets("Timkiem_trichxuat")
    wsKetqua.Rows("6:" & wsKetqua.Rows.count).ClearContents
    Set donviDict = CreateObject("Scripting.Dictionary")
    On Error GoTo ErrorHandler
    startDate = DateValue(txtStartDate.Value)
    endDate = DateValue(txtEndDate.Value)
    On Error GoTo 0
    If endDate < startDate Then
        MsgBox "Ngày ket thúc không duoc truoc ngày bat dau!", vbExclamation
        Exit Sub
    End If

This is to add value into dictionary

    For currentRow = 2 To wsDulieu.Cells(wsDulieu.Rows.count, 1).End(xlUp).Row
        If wsDulieu.Cells(currentRow, 9).Value >= startDate And wsDulieu.Cells(currentRow, 9).Value <= endDate Then
            donvi = wsDulieu.Cells(currentRow, 2).Value
            If Not donviDict.exists(donvi) Then
                donviDict.Add donvi, Array(0, 0, 0, 0) ' [de nghi cn, de nghi tc, cap cn, cap  tc]
            End If
           
            donviDict(donvi)(0) = donviDict(donvi)(0) + wsDulieu.Cells(currentRow, 5).Value  ' de nghi cn
            donviDict(donvi)(1) = donviDict(donvi)(1) + wsDulieu.Cells(currentRow, 6).Value ' de nghi tc
            donviDict(donvi)(2) = donviDict(donvi)(2) + wsDulieu.Cells(currentRow, 7).Value ' cap cn
            donviDict(donvi)(3) = donviDict(donvi)(3) + wsDulieu.Cells(currentRow, 8).Value ' cap tc
              
        End If
    Next currentRow

Write out result to another sheet

    'Ghi ket qua vao bang Timkiem_trichxuat
    wsKetqua.Cells(6, 1).Value = "STT"
    wsKetqua.Cells(6, 2).Value = "Don vi"
    wsKetqua.Cells(6, 3).Value = "De nghi CN"
    wsKetqua.Cells(6, 4).Value = "De nghi TC"
    wsKetqua.Cells(6, 5).Value = "Cap CN"
    wsKetqua.Cells(6, 6).Value = "Cap TC"
    nextRow = 7
    For Each donviRun In donviDict.Keys
        wsKetqua.Cells(nextRow, 1).Value = nextRow - 1 ' STT
        wsKetqua.Cells(nextRow, 2).Value = donviRun ' Ðon vi
        wsKetqua.Cells(nextRow, 3).Value = donviDict(donviRun)(0) ' de nghi cn
        wsKetqua.Cells(nextRow, 4).Value = donviDict(donviRun)(1) 'de nghi tc
        wsKetqua.Cells(nextRow, 5).Value = donviDict(donviRun)(2) ' cap cn
        wsKetqua.Cells(nextRow, 6).Value = donviDict(donviRun)(3) ' cap tc
        nextRow = nextRow + 1
    Next donviRun
    MsgBox "Tong hop du lieu xong!", vbInformation + vbOKOnly, "Xong"
    
ErrorHandler:
    MsgBox "Vui lòng nhap dúng dinh dang ngày (mm/dd/yyyy).", vbExclamation
End Sub 

I have tried checking the datatype of the value in excel and value in dictionary, try casting both value to be the same, but it is still not working correctly.

Answer 1

Update Array in Dictionary (Workaround)

• You cannot update an array held in an item of a dictionary.
• Here's a workaround using a temporary array.

A Quick Fix

' This is to populate the dictionary

Dim Arr() As Variant

For currentRow = 2 To wsDulieu.Cells(wsDulieu.Rows.Count, 1).End(xlUp).Row
    
    If wsDulieu.Cells(currentRow, 9).Value >= startDate And wsDulieu.Cells(currentRow, 9).Value <= endDate Then
        
        donvi = wsDulieu.Cells(currentRow, 2).Value
        
        If Not donviDict.exists(donvi) Then
            donviDict.Add donvi, VBA.Array(0, 0, 0, 0) ' [de nghi cn, de nghi tc, cap cn, cap  tc]
        End If
        
        Arr = donviDict(donvi)
        
        Arr(0) = Arr(0) + wsDulieu.Cells(currentRow, 5).Value  ' de nghi cn
        Arr(1) = Arr(1) + wsDulieu.Cells(currentRow, 6).Value ' de nghi tc
        Arr(2) = Arr(2) + wsDulieu.Cells(currentRow, 7).Value ' cap cn
        Arr(3) = Arr(3) + wsDulieu.Cells(currentRow, 8).Value ' cap tc
          
        donviDict(donvi) = Arr
          
    End If

Next currentRow

• Additionally, you could create a loop:

        Dim i As Long
        
        For i = 0 To 3
            Arr(i) = Arr(i) + wsDulieu.Cells(currentRow, i + 5).Value
        Next i

• BTW, VBA.Array ensures a zero-based array (Option Base- related).