It is possible to have an array with no upper bound?

Question

I'm populating an array based on whether or not criteria across a worksheet are met. The thing is, I don't know how long the array is, as it could be larger than the dataset itself (will have duplicates of some entries). What I have is something along the lines of:

Sub ArrayTest
Dim MyArray as Variant
Dim i as Long
Dim index as Long
Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).row
ReDim myarray(index)

For i = 1 to lastrow
    If (something) then
        index = index  + 1
        ReDim Preserve recordArrray(Index)
        recordArrray(Index) = range("A" & i)

    End If 
Next
End Sub

However, once index reaches lastrow, I get a subscript out of range error. I can avoid this by simply replacing the ReDim line with ReDim myarray(1 to 100000000). Obviously, this is not an ideal solution.

Can I create an array without having to define what the upper bound will be before it is created?

You always have to define the upper bound. Alternatives would be to `Redim Preserve` every so often, perhaps in sizes of 1000, or to use a Collection (or Dictionary). — Ron Rosenfeld, May 23 '16 at 14:16
If you don't want do redim the array too often, maybe a [dictionary](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) is what you're looking for. In your example the index shouldn't get higher than lastrow though and using an upper bound and redimming after the loop might be more efficient. edit: there is also an example for a collection in the linked question — arcadeprecinct, May 23 '16 at 14:16

score 0 · Accepted Answer · answered May 23 '16 at 14:05

Arrays always have to have both an upper and lower bound (though you can change the boundaries dynamically with Redim).

My guess is that the problem you're running into is that the starting value for index is 0 which is outside of the legal bounds of your array. I suspect that this would fix the immediate problem:

Sub ArrayTest
Dim MyArray as Variant
Dim i as Long
dim index as Long
Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).row
ReDim myarray(1 to lastrow)

For i = 1 to lastrow
    If (something) then
        index = index  + 1
        recordArrray(Index) = range("A" & i)
    End If 
Next
End Sub

Excellent. Thanks. Redimming in the loop worked. I've updated the original question now. — user1996971, May 23 '16 at 14:19

It is possible to have an array with no upper bound?

1 Answers1