Store all dates between 2 dates in vba into an array-CodePudding

I am using a function to get all dates between 2 dates in a list of Start Dates and End Dates: I am looking to store in an array each of the dates from start to end date with their unique ID. Data is column 1 ID, 2 Start Date, 3 End Date. The array would be a list of ID's with all pertaining dates from Start Date to End Date. Below is the code I have to get all dates:

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

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

For i = 2 To TESTWS.Cells(1, 1).End(xlDown).Row

DatesTest = getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))

Next i

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

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

CodePudding user response：

Just create an array with the size of the rows ReDim DatesTest(1 To LastRow - FirstRow 1) and fill that with your results from getDates.

Dim TESTWB As Workbook
Dim TESTWS As Worksheet

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

Const FirstRow As Long = 2
Dim LastRow As Long
LastRow = TESTWS.Cells(1, 1).End(xlDown).Row

Dim DatesTest() As Variant
ReDim DatesTest(1 To LastRow - FirstRow   1)

For i = FirstRow To LastRow
    DatesTest(i - FirstRow   1) = getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))
Next i

You can then access the first result of getDates with DatesTest(1) where DatesTest(1)(1) should give you the first date of the first set.