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

ReDim Preserve dateArray(ubound(dateArray)+1) Does this work for you? the preserve keyword will keep data in the array — 99moorem
– 99moorem, Commented Jul 29, 2015 at 11:03
@99moorem where in the code would I put this and what does this do? — GBSingh
– GBSingh, Commented Jul 29, 2015 at 11:04

99moorem · Accepted Answer · 2015-07-29 11:13:05Z

2

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

answered Jul 29, 2015 at 11:13

99moorem

1,9931 gold badge16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GBSingh Over a year ago

Thanks you so much for this it worked. I didn't realise that by not putting the as date after every one did not make it into a date, I thought that everything before the as date would be made into a date, so thank you for teaching me that. Thats why I like stack overflow you guys are a fountain of knowledge that one can truly learn from thanks

ChipsLetten · Accepted Answer · 2015-07-29 11:07:21Z

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.

answered Jul 29, 2015 at 11:07

ChipsLetten

2,9531 gold badge15 silver badges29 bronze badges

1 Comment

GBSingh Over a year ago

thanks for the knowledge about the variables i did not know this

mielk · Accepted Answer · 2015-07-29 11:06:34Z

1

The reason is that you have declared array dateArray as having only one element, indexed with 1:

ReDim dateArray(1 To 1) As Date

Later in your code you try to assign a value to an element of this array with index 0 (but there is no such element and that is why this error is displayed):

For x = 0 To str2
    If x = 0 Then
        dateArray(x) = str1  '<---- in first iteration, when x = 0, you 
                             '      try to assign to element with 0 index.
    Else

(...)

answered Jul 29, 2015 at 11:06

mielk

3,94015 silver badges20 bronze badges

Comments

Rory · Accepted Answer · 2015-07-29 11:20:28Z

You've already calculated the size so you can use that with Redim once rather than using Preserve which is a relatively expensive operation as it copies your entire array. I'd also suggest some more descriptive variable names and you should explicitly declare the type of all variables:

Private Sub SearchButton4_Click()
    Dim wks                   As Excel.Worksheet
    Dim dtFrom                As Date
    Dim dtTo                  As Date
    Dim lNumberOfDays         As Long
    Dim x                     As Long
    Dim dateArray()           As Date

    Set wks = Worksheets("Exceptions")

    dtFrom = CDate(DateFromTextBox.Value)
    dtTo = CDate(DateToTextBox.Value)

    If dtTo < dtFrom Then
        dtTo = CDate(DateFromTextBox.Value)
        dtFrom = CDate(DateToTextBox.Value)
    End If

    lNumberOfDays = dtTo - dtFrom

    ReDim dateArray(1 To lNumberOfDays + 1, 1 To 1) As Date

    For x = 0 To lNumberOfDays
        dateArray(x + 1, 1) = dtFrom + x
    Next

    With wks.Range("A1").Resize(UBound(dateArray))
        .NumberFormat = "dd-mm-yyyy"
        .Value = dateArray
    End With
End Sub

I assumed you would be outputting the results to the worksheet, so I used a 2D array.

Collectives™ on Stack Overflow

adding items to an array VBA excel

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related