adding items to an array VBA excel

Question

I am trying to add date into an array but am not able to do it. Every time that I try to do it I get a Subscript out of range error. I have used arrays before in other languages so this should work but it is not and I don't seem to understand why. Below I have added the code that is currently not working. What I am trying to do is after a user enters two dates my code should store the starting value and then add one to that date and add it to the array and so on until I have a array list of all the date from the start date to the end date. Any help would be great thanks

Private Sub SearchButton4_Click()
    Dim wks As Excel.Worksheet, str1, str2, str3, str4 As Date, x As Integer
    Dim dateArray() As Date

    ReDim dateArray(1 To 1) As Date
    Set wks = Worksheets("Exceptions")
    str1 = Format(DateFromTextBox.Value, "dd-mm-yyyy")
    str3 = Format(DateToTextBox.Value, "dd-mm-yyyy")
    str2 = DateDiff("d", str1, str3)

    If str2 < 0 Then
        str2 = str2 * -1
    End If

    For x = 0 To str2
        If x = 0 Then
            dateArray(x) = str1
        Else
            str4 = DateAdd("d", 1, str1)
            dateArray(x) = str4
            str1 = str4
        End If
    Next

End Sub

Answer 1

Try below, the redim will resize an array. ubound() finds the top end of an array so ubound()+1 will add one extra size to the array. The preserve keyword will preserve any values that are currently in the array

notice 1: how I have declared you variables str1 - 3 was not declared as a date. 2: how I have initialised your array dont need to do 1 to 1 can just say I want x amount

Hope that helps

Private Sub SearchButton4_Click()
    Dim wks As Excel.Worksheet, str1 As Date, str2 As Date, str3 As Date, str4 As Date, x As Integer
    Dim dateArray() As Date

    ReDim dateArray(1) As Date
    Set wks = Worksheets("Exceptions")
    str1 = Format(DateFromTextBox.Value, "dd-mm-yyyy")
    str3 = Format(DateToTextBox.Value, "dd-mm-yyyy")
    str2 = DateDiff("d", str1, str3)

    If str2 < 0 Then
        str2 = str2 * -1
    End If

    For x = 0 To str2
        If x = 0 Then
            dateArray(x) = str1
        Else
            str4 = DateAdd("d", 1, str1)
            dateArray(x) = str4
            str1 = str4
        End If
        ReDim Preserve dateArray(ubound(dateArray)+1) 
    Next

End Sub

Answer 2

You need to increase the size of your array. This line:

ReDim dateArray(1 To 1) As Date

only gives you one element in your array. You should be using:

ReDim dateArray(0 To str2) As Date

after you've worked out the value of str2.

btw, you can use the Abs function to give you a positive number:

str2 = Abs(DateDiff("d", str1, str3))

Also, when you declare multiple variables on one line you must include the type for every variable. In this line:

Dim wks As Excel.Worksheet, str1, str2, str3, str4 As Date, x As Integer

the variables str1, str2, str3 all get declared as Variant not Date.