如何在无设置限制的excel vba中创建数组?

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:

我想通过尝试x“已知”，我可以编辑它并询问用户他们希望x是什么(使用输入框)，但是VBA不允许，我得到了错误消息:

error message screenshot

错误信息截图

3 个解决方案

#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.

注意我如何使用Long而不是Integer。由于电子表格中有超过一百万行，使用Integer迟早会导致溢出错误。还要注意，不需要将A声明为数组。变量可以保存数组，而ReDim语句使它成为一个数组。

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.

最后，如果您的数组一直在动态增长，那么重构代码使其使用集合可能更有意义，这是最接近于拥有内置动态列表数据结构的VBA。

#2

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.

在我的示例中，我将行号放在A1:A28中。它每次都正确地增加数组大小。您可以用它来做任何您想做的事情，比如添加If语句(If cel)。value = "Gotham"，然后是数组(i) = cel.value，或其他有助于确定数组大小的方法。

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.

或者，如果您希望保持示例，并预先设置数组大小，您可以这样做。假设我有A列，但是数据大小(行数)总是变化的。您可以将数组大小设置为，例如Application.WorksheetFunction.Counta(Range(“A:A”))，以获取非空单元格的数量，您可以使用它们来调整数组的大小。

#3

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

#1