[SOLVED] Store all dates between 2 dates in vba into an array

Issue

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

Solution

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)

Dim i As Long
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.

If you want to loop through all of them you can do it like that:

Dim DateSet As Variant
For Each DateSet In DatesTest  ' loop through all sets of dates
    Dim DateItem As Variant
    For Each DateItem In DateSet  ' loop through all dates of one set
        Debug.Print DateItem
    Next DateItem
Next DateSet

or like

Dim iSet As Long
For iSet = 1 To Ubound(DatesTest)  ' loop through all sets of dates
    Dim iDate As Long
    For iDate = 0 To Ubound(DatesTest(iSet))  ' loop through all dates of one set
        Debug.Print "Set " & iSet, "Date " & DatesTest(iSet)(iDate)
    Next iDate
Next iSet

This should output something like

Set 1        Date 2022-03-08
Set 1        Date 2022-03-09
Set 1        Date 2022-03-10
Set 2        Date 2022-04-01
Set 2        Date 2022-03-02
Set 2        Date 2022-03-03
…

---

If you want to use the ID(i,1) as key then you need to use a Collection instead of an array.

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

DatesTest As New Collection

Dim i As Long
For i = FirstRow To LastRow
    DatesTest.Add getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3)), TESTWS.Cells(i, 1)
Next i

Then you can use DatesTest(TESTWS.Cells(2, 1)) to get the first set of dates and DatesTest(TESTWS.Cells(2, 1))(1) would give you the first date of that set.

Answered By – Pᴇʜ

Answer Checked By – David Marino (BugsFixing Volunteer)