How to make an array in excel vba with no set limit?

Question

I want to create a list of numbers using an array, but I don't want the last number to be known, instead it's dependent on other factors, for example you ask a user what the top limit is, and the array will stop there.

I created an array that will produce a list of numbers, but when the end number is known for example:

Sub makearray50() 'creates a list of numbers from 1 to 50

    Dim i As Integer
    Dim theArray(1 To 50) As Double
        For i = 1 To 50
            theArray(i) = Int(0 + i)
        Next i
        For i = 1 To 50
            Sheets("ARRAY").Cells(i, 1).Value = theArray(i)
        Next i
End Sub

So I thought I would try with an unknown upper limit, this was what I tried:

Sub makearrayx() 'creates a list of numbers from 1 to x

    Dim i As Integer
    Dim x As Integer
    x = 10
    Dim theArray(1 To x) As Double
        For i = 1 To x
            theArray(i) = Int(0 + i)
        Next i
        For i = 1 To x
            Sheets("ARRAY").Cells(i, 1).Value = theArray(i)
        Next i
End Sub

I thought by trying with x "known" I could then edit it and ask the user what they would like x to be (using an input box) but VBA won't allow it, I get the error message:

error message screenshot

Or also `ReDim Preserve` to keep the array as you add to it.[Here's](http://stackoverflow.com/questions/2916009/vba-what-does-redim-preserve-do-and-simple-array-question) an SO thread on it, and the [Microsoft](https://msdn.microsoft.com/en-us/library/w8k3cys2.aspx) page. Also, how'd you code the input box? They should be able to be used. — BruceWayne, Dec 01 '15 at 16:28
Also -- what is the point of `Int(0 + i)` when `i` is already an integer? — John Coleman, Dec 01 '15 at 16:29
To piggy back off @JohnColeman's question - why even add `0` to the number? What's your thinking there? — BruceWayne, Dec 01 '15 at 16:54

score 1 · Answer 1 · answered Dec 01 '15 at 16:32

1

Here's a quick example of how to re-dimension an array:

Sub test()
Dim i&
Dim theArray()
Dim cel As Range

i = 0

For Each cel In Range("A1:A28")
    ReDim Preserve theArray(i) 'This will resize the array, but keep previous values
    theArray(i) = cel.Value
    i = i + 1
Next cel

For i = LBound(theArray) To UBound(theArray) 'this will just show you the array is working in the Immediate Window
    Debug.Print theArray(i)
Next i

End Sub

In my example, I put the row numbers in A1:A28. It correctly increases the array size each time. You could get as crazy as you want with this, such as adding If statements, (If cel.value = "Gotham" Then theArray(i) = cel.value) or other ways that would help determine the array size.

Or, if you want to keep your example, and set the array size up front, you can. Say I have my column A, but the data size (the number of rows) always changes. You could set the array size to, for example, Application.WorksheetFunction.Counta(Range("A:A")) to get the number of non-blank cells, which you could use to size your array.

answered Dec 01 '15 at 16:32

BruceWayne

22,923
15
65
110

1

I'm not sure if I trust `ReDim Preserve` in a loop. Unless the VBA interpreter allocates room for arrays to grow, this would be quadratic in the number of passes through the loop with all of the copying of preserved elements into the newly allocated arrays. Sometimes what I do instead is allocate more than I need and then cut it down to size with `ReDim Preserve` after the loop. Of course, that only works when you have a known upper bound on the number of passes through the loop. – John Coleman Dec 01 '15 at 16:50
@JohnColeman - That's interesting! I didn't know `Preserve` could be so touchy. So, if you do `myArray(100)` then load it up with data, say only 90 values, you can then do (at the end, when you're done adding to the array) `ReDim Preserve myArray(90)`? – BruceWayne Dec 01 '15 at 16:53
1

I'm pretty sure that `ReDim Preserve` can shrink arrays just as well as grow them. – John Coleman Dec 01 '15 at 16:56
1

I agree with @JohnColeman, because `ReDim` and especially when you add `Preserve`, is a potential performance killer. You know how many times the loop is going to iterate, so definitely do it outside of the loop. Then you're only resizing the array once, and you don't need the Preserve – ManishChristian Dec 01 '15 at 18:41

score 1 · Answer 2 · answered Dec 01 '15 at 16:41

You could create a function to return such arrays:

Function MakeArray(n As Long) As Variant
    'Creates a 1-based array containing the values
    '1,2,...,n

    Dim A As Variant, i As Long
    ReDim A(1 To n)
    For i = 1 To n
        A(i) = i
    Next i

    MakeArray = A
End Function

Note how I use Long rather than Integer. With there being over a million rows in a spreadsheet, using Integer is asking for an overflow error sooner or later. Note also that there is no need to declare A as an array. Variants can hold arrays and the ReDim statement causes it to become an array.

You can test it like:

Sub test()
    Dim theArray As Variant, n As Long, i As Long
    n = InputBox("How many elements")
    theArray = MakeArray(n)
    For i = 1 To n
        Cells(i, 1).Value = theArray(i)
    Next i
End Sub

Finally, if you have a situation where your array is growing dynamically all the time, it might make more sense to refactor the code so that it uses a collection, which is the closest VBA comes to having a built-in dynamic list data structure.

score 0 · Answer 3 · answered Dec 01 '15 at 16:49

Here is the simplest way. This sub will:

-> Ask the user for upper limit for an array
-> Create an array
-> Print value to Sheet

Sub makearrayx()

    Dim i, x As Integer
    Dim theArray As Variant

    x = InputBox("Tell me array limit")

    ReDim theArray(1 To x)
    For i = 1 To x
        theArray(i) = i
    Next

    For i = 1 To x
        Sheets("ARRAY").Cells(i, 1).Value = theArray(i)
    Next

End Sub

How to make an array in excel vba with no set limit?

3 Answers3