3

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

Improve this question
3
  • Or also ReDim Preserve to keep the array as you add to it.Here's an SO thread on it, and the Microsoft page. Also, how'd you code the input box? They should be able to be used. Commented Dec 1, 2015 at 16:28
  • Also -- what is the point of Int(0 + i) when i is already an integer? Commented Dec 1, 2015 at 16:29
  • To piggy back off @JohnColeman's question - why even add 0 to the number? What's your thinking there? Commented Dec 1, 2015 at 16:54

3 Answers 3

Reset to default
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.

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

4 Comments

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.
@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)?
I'm pretty sure that ReDim Preserve can shrink arrays just as well as grow them.
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
1

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.

Improve this answer

Comments

0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.