如何在 VBA 中声明数组变量？

How do I declare an array variable in VBA?

提问by Vba Dev

I need to add the var in array

我需要在数组中添加 var

Public Sub Testprog()

Dim test As Variant
Dim iCounter As Integer

If test = Empty Then
    iCounter = 0
    test(iCounter) = "test"
Else
    iCounter = UBound(test)
End If
End Sub

Getting error at test(iCounter) = "test"

获取错误 test(iCounter) = "test"

Please suggest some solution

请提出一些解决方案

回答by Cody Gray

Generally, you should declare variables of a specific type, rather than Variant. In this example, the testvariable should be of type String.

通常，您应该声明特定类型的变量，而不是Variant. 在这个例子中，test变量的类型应该是String。

And, because it's an array, you need to indicate that specifically when you declare the variable. There are two ways of declaring array variables:

而且，因为它是一个数组，所以您需要在声明变量时特别指明。声明数组变量有两种方式：

1. If you know the size of the array (the number of elements that it should contain) when you write the program, you can specify that number in parentheses in the declaration:

   Dim test(1) As String   'declares an array with 2 elements that holds strings
   

   This type of array is referred to as a staticarray, as its size is fixed, or static.

2. If you do not know the size of the array when you write the application, you can use a dynamicarray. A dynamic array is one whose size is not specified in the declaration (Dimstatement), but rather is determined later during the execution of the program using the ReDimstatement. For example:

   Dim test() As String
   Dim arraySize As Integer
   
   ' Code to do other things, like calculate the size required for the array
   ' ...
   arraySize = 5
   
   ReDim test(arraySize)  'size the array to the value of the arraySize variable
   

1. 如果您在编写程序时知道数组的大小（它应该包含的元素数），则可以在声明中的括号中指定该数字：

   Dim test(1) As String   'declares an array with 2 elements that holds strings
   

   这种类型的数组被称为静态数组，因为它的大小是固定的或静态的。

2. 如果在编写应用程序时不知道数组的大小，可以使用动态数组。动态数组的大小未在声明（Dim语句）中指定，而是稍后在使用该ReDim语句的程序执行期间确定。例如：

   Dim test() As String
   Dim arraySize As Integer
   
   ' Code to do other things, like calculate the size required for the array
   ' ...
   arraySize = 5
   
   ReDim test(arraySize)  'size the array to the value of the arraySize variable
   

回答by RolandTumble

Further to Cody Gray's answer, there's a third way (everything there applies her as well):

除了 Cody Gray 的回答之外，还有第三种方式（一切都适用于她）：

You can also use a dynamicarray that's resized on the fly:

您还可以使用动态调整大小的动态数组：

Dim test() as String
Dim arraySize as Integer

Do While someCondition
    '...whatever
    arraySize = arraySize + 1
    ReDim Preserve test(arraySize)
    test(arraySize) = newStringValue
Loop

Note the Preservekeyword. Without it, redimensioning an array also initializes all the elements.

注意Preserve关键字。没有它，重新调整数组的大小也会初始化所有元素。

回答by MicrosoftShouldBeKickedInNuts

Further to RolandTumble's answer to Cody Gray's answer, both fine answers, here is another very simple and flexible way, when you know all of the array contents at coding time - e.g. you just want to build an array that contains 1, 10, 20 and 50. This also uses variant declaration, but doesn't use ReDim. Like in Roland's answer, the enumerated count of the number of array elements need not be specifically known, but is obtainable by using uBound.

除了 RolandTumble 对 Cody Gray 的回答的回答，两个很好的答案，这是另一种非常简单和灵活的方法，当您在编码时知道所有数组内容时 - 例如，您只想构建一个包含 1、10、20 和50. 这也使用了变体声明，但不使用 ReDim。就像在 Roland 的回答中一样，不需要特别知道数组元素数量的枚举计数，但可以通过使用 uBound 获得。

sub Demo_array()
    Dim MyArray as Variant, MyArray2 as Variant, i as Long

    MyArray = Array(1, 10, 20, 50)  'The key - the powerful Array() statement
    MyArray2 = Array("Apple", "Pear", "Orange") 'strings work too

    For i = 0 to UBound(MyArray)
        Debug.Print i, MyArray(i)
    Next i
    For i = 0 to UBound(MyArray2)
        Debug.Print i, MyArray2(i)
    Next i
End Sub

I love this more than any of the other ways to create arrays. What's great is that you can add or subtract members of the array right there in the Array statement, and nothing else need be done to code. To add Egg to your 3 element food array, you just type

我比任何其他创建数组的方法都更喜欢它。很棒的是，您可以直接在 Array 语句中添加或减去数组的成员，无需对代码执行任何其他操作。要将 Egg 添加到您的 3 元素食物数组中，您只需键入

, "Egg"

， “蛋”

in the appropriate place, and you're done. Your food array now has the 4 elements, and nothing had to be modified in the Dim, and ReDim is omitted entirely.

在适当的地方，你就完成了。您的食物数组现在有 4 个元素，并且 Dim 中没有任何内容需要修改，并且 ReDim 被完全省略。

If a 0-based array is not desired - i.e., using MyArray(0) - one solution is just to jam a 0 or "" for that first element.

如果不需要基于 0 的数组 - 即，使用 MyArray(0) - 一个解决方案就是为第一个元素堵塞 0 或 ""。

Note, this might be regarded badly by some coding purists; one fair objection would be that "hard data" should be in Const statements, not code statements in routines. Another beef might be that, if you stick 36 elements into an array, you should set a const to 36, rather than code in ignorance of that. The latter objection is debatable, because it imposes a requirement to maintain the Const with 36 rather than relying on uBound. If you add a 37th element but leave the Const at 36, trouble is possible.

请注意，一些纯粹的编码主义者可能会认为这很糟糕；一个公平的反对意见是“硬数据”应该在 Const 语句中，而不是例程中的代码语句。另一个问题可能是，如果您将 36 个元素放入一个数组中，您应该将 const 设置为 36，而不是在无知的情况下编写代码。后一种反对意见是有争议的，因为它要求用 36 来维护 Const 而不是依赖 uBound。如果添加第 37 个元素但将 Const 保留为 36，则可能会出现问题。

回答by Alter

As pointed out by others, your problem is that you have not declared an array

正如其他人所指出的，你的问题是你没有声明一个数组

Below I've tried to recreate your program so that it works as you intended. I tried to leave as much as possible as it was (such as leaving your array as a variant)

下面我尝试重新创建您的程序，使其按您的预期工作。我试图尽可能多地离开（例如将您的数组保留为变体）

Public Sub Testprog()
    '"test()" is an array, "test" is not
    Dim test() As Variant
    'I am assuming that iCounter is the array size
    Dim iCounter As Integer

    '"On Error Resume Next" just makes us skip over a section that throws the error
    On Error Resume Next

    'if test() has not been assigned a UBound or LBound yet, calling either will throw an error
    '   without an LBound and UBound an array won't hold anything (we will assign them later)

    'Array size can be determined by (UBound(test) - LBound(test)) + 1
    If (UBound(test) - LBound(test)) + 1 > 0 Then
        iCounter = (UBound(test) - LBound(test)) + 1

        'So that we don't run the code that deals with UBound(test) throwing an error
        Exit Sub
    End If

    'All the code below here will run if UBound(test)/LBound(test) threw an error
    iCounter = 0

    'This makes LBound(test) = 0
    '   and UBound(test) = iCounter where iCounter is 0
    '   Which gives us one element at test(0)
    ReDim Preserve test(0 To iCounter)

    test(iCounter) = "test"
End Sub

回答by Mark Cidade

You have to declare the array variable as an array:

您必须将数组变量声明为数组：

Dim test(10) As Variant

回答by KamalPanhwar

David, Error comes Microsoft Office Excel has stopped working. Two options check online for a solution and close the programme and other option Close the program I am sure error is in my array but I am reading everything and seem this is way to define arrays.

大卫，错误来了 Microsoft Office Excel 已停止工作。两个选项在线检查解决方案并关闭程序和其他选项关闭程序我确定错误在我的数组中，但我正在阅读所有内容，似乎这是定义数组的方式。

回答by user2879389

The Array index only accepts a long value.

Array 索引仅接受 long 值。

You declared iCounter as an integer. You should declare it as a long.

您将 iCounter 声明为整数。您应该将其声明为 long。