VBA type mismatch Adding dates via function to find dates in between 2 dates into dictionary-CodePudding

I am trying to add a key and then dates from an array dates () data type into a dictionary, but I get a type mismatch error, any ideas?

Sub Test_Dates()
'
Dim TESTWB As Workbook
Dim TESTWS As Worksheet

Set TESTWB = ThisWorkbook
Set TESTWS = TESTWB.Worksheets("TEST")

Dim DatesDict As Scripting.Dictionary

For i = 1 To TESTWS.Cells(1, 1).End(xlDown).Row
      DatesDict.Add TESTWS.Cells(i, 1), getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))
      
Next i

End Sub

Here is the function to get dates between 2 dates (i,2) and (i,3)

Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant

    Dim varDates()      As Date
    Dim lngDateCounter  As Long

    ReDim varDates(0 To CLng(EndDate) - CLng(StartDate))

    For lngDateCounter = LBound(varDates) To UBound(varDates)
        varDates(lngDateCounter) = CDate(StartDate)
        StartDate = CDate(CDbl(StartDate)   1)
    Next lngDateCounter

    getDates = varDates

ClearMemory:
    If IsArray(varDates) Then Erase varDates
    lngDateCounter = Empty

End Function

CodePudding user response：

Seems like maybe one or more of your rows do not have valid dates in ColB or ColC.

It would help with debugging to avoid the implicit Date conversion:

Eg:

Sub Test_Dates()
    '
    Dim TESTWB As Workbook
    Dim TESTWS As Worksheet, i As Long, k
    Dim DatesDict As Scripting.Dictionary
    Dim dtStart As Date, dtEnd As Date
    
    Set TESTWB = ThisWorkbook
    Set TESTWS = TESTWB.Worksheets("TEST")
    Set DatesDict = New Scripting.Dictionary
    
    For i = 1 To TESTWS.Cells(Rows.Count, "A").End(xlUp).Row
        With TESTWS.Rows(i)
            dtStart = CDate(.Columns("B").Value) '<< explicit Date conversion
            dtEnd = CDate(.Columns("C").Value)
            If dtEnd >= dtStart Then
                DatesDict.Add .Columns("A").Value, getDates(dtStart, dtEnd)
            End If
        End With
    Next i
    
    'checking...
    For Each k In DatesDict
        Debug.Print TypeName(k), k, DatesDict(k)(0)
    Next k

End Sub

Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant
    Dim varDates()      As Date
    Dim lngDateCounter  As Long
    ReDim varDates(0 To CLng(EndDate) - CLng(StartDate))
    For lngDateCounter = LBound(varDates) To UBound(varDates)
        varDates(lngDateCounter) = CDate(StartDate)
        StartDate = CDate(CDbl(StartDate)   1)
    Next lngDateCounter
    getDates = varDates
End Function