填充 VBA 动态数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8850984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Populating VBA dynamic arrays
提问by sebastien leblanc
The following code gives me error 9 "subscript out of range". I meant to declare a dynamic array so that the dimension changes as I add elements to it. Do I have to create a "spot" on the array before I store something in it like in JS?
下面的代码给了我错误 9“下标超出范围”。我打算声明一个动态数组，以便在向其中添加元素时维度会发生变化。在像在 JS 中那样在数组中存储一些东西之前，我是否必须在数组上创建一个“点”？
Sub test_array()
Dim test() As Integer
Dim i As Integer
For i = 0 To 3
test(i) = 3 + i
Next i
End Sub
回答by Fluffi1974
in your for loop use a Redim on the array like here:
在您的 for 循环中，在数组上使用 Redim，如下所示：
For i = 0 to 3
ReDim Preserve test(i)
test(i) = 3 + i
Next i
回答by a505999
As Cody and Brett mentioned, you could reduce VBA slowdown with sensible use of Redim Preserve. Brett suggested Modto do this.
正如 Cody 和 Brett 提到的，您可以通过合理使用Redim Preserve. 布雷特建议Mod这样做。
You can also use a user defined Typeand Subto do this. Consider my code below:
您还可以使用用户定义Type和Sub来执行此操作。考虑我的代码如下：
Public Type dsIntArrayType
eElems() As Integer
eSize As Integer
End Type
Public Sub PushBackIntArray( _
ByRef dsIntArray As dsIntArrayType, _
ByVal intValue As Integer)
With dsIntArray
If UBound(.eElems) < (.eSize + 1) Then
ReDim Preserve .eElems(.eSize * 2 + 1)
End If
.eSize = .eSize + 1
.eElems(.eSize) = intValue
End With
End Sub
This calls ReDim Preserveonly when the size has doubled. The member variable eSizekeeps track of the actual data size of eElems. This approach has helped me improve performance when final array length is not known until run time.
这ReDim Preserve仅在大小加倍时调用。成员变量eSize跟踪 的实际数据大小eElems。当直到运行时才知道最终数组长度时，这种方法帮助我提高了性能。
Hope this helps others too.
希望这对其他人也有帮助。
回答by Cody Gray
Yes, you're looking for the ReDimstatement, which dynamically allocates the required amount of space in the array.
是的，您正在寻找ReDim动态分配数组中所需空间量的语句。
The following statement
以下声明
Dim MyArray()
declares an array without dimensions, so the compiler doesn't know how big it is and can't store anything inside of it.
声明一个没有维度的数组，所以编译器不知道它有多大，也不能在里面存储任何东西。
But you can use the ReDimstatement to resize the array:
但是您可以使用该ReDim语句来调整数组的大小：
ReDim MyArray(0 To 3)
And if you need to resize the array while preservingits contents, you can use the Preservekeyword along with the ReDimstatement:
如果您需要在保留其内容的同时调整数组大小，您可以将Preserve关键字与ReDim语句一起使用：
ReDim Preserve MyArray(0 To 3)
But do note that both ReDimand particularly ReDim Preservehave a heavy performance cost. Try to avoid doing this over and over in a loop if at all possible; your users will thank you.
但请注意，两者都ReDim特别ReDim Preserve具有沉重的性能成本。如果可能，尽量避免在循环中一遍又一遍地执行此操作；你的用户会感谢你。
However, in the simple example shown in your question (if it's not just a throwaway sample), you don't need ReDimat all. Just declare the array with explicit dimensions:
但是，在您的问题中显示的简单示例中（如果它不仅仅是一次性示例），您根本不需要ReDim。只需声明具有显式维度的数组：
Dim MyArray(0 To 3)
回答by brettdj
In addition to Cody's useful comments it is worth noting that at times you won't know how big your array should be. The two options in this situation are
除了 Cody 的有用评论之外，值得注意的是，有时您不知道您的数组应该有多大。这种情况下的两个选项是
- Creating an array big enough to handle anything you think will be thrown at it
- Sensibleuse of
Redim Preserve
- 创建一个足够大的数组来处理你认为会被抛出的任何东西
- 合理使用
Redim Preserve
The code below provides an example of a routine that will dimension myArrayin line with the lngSizevariable, then add additional elements (equal to the initial array size) by use of a Modtest whenever the upper bound is about to be exceeded
下面的代码提供了一个例程示例，该例程将myArray与lngSize变量保持一致，然后Mod在即将超过上限时使用测试添加其他元素（等于初始数组大小）
Option Base 1
Sub ArraySample()
Dim myArray() As String
Dim lngCnt As Long
Dim lngSize As Long
lngSize = 10
ReDim myArray(1 To lngSize)
For lngCnt = 1 To lngSize*5
If lngCnt Mod lngSize = 0 Then ReDim Preserve myArray(1 To UBound(myArray) + lngSize)
myArray(lngCnt) = "I am record number " & lngCnt
Next
End Sub
回答by Petr Pivonka
I see many (all) posts above relying on LBound/UBoundcalls upon yet potentially uninitialized VBA dynamic array, what causes application's inevitable death ...
我看到上面有很多（所有）帖子依赖LBound/UBound调用但可能未初始化的 VBA 动态数组，是什么导致了应用程序不可避免的死亡......
Erratic code:
错误代码：
Dim x As Long
Dim arr1() As SomeType
...
x = UBound(arr1) 'crashes
Dim x As Long
Dim arr1() As SomeType
...
x = UBound(arr1) 'crashes
Correct code:
正确代码：
Dim x As Long
Dim arr1() As SomeType
...
ReDim Preserve arr1(0 To 0)
...
x = UBound(arr1)
Dim x As Long
Dim arr1() As SomeType
...
ReDim Preserve arr1(0 To 0)
...
x = UBound(arr1)
... i.e. any code where Dim arr1()is followed immediatelly by LBound(arr1)/UBound(arr1)calls without ReDim arr1(...)in between, crashes. The roundabout is to employ an On Error Resume Nextand check the Err.Numberright after the LBound(arr1)/UBound(arr1)call - it should be 0 if the array is initialized, otherwise non-zero. As there is some VBA built-in misbehavior, the further check of array's limits is needed. Detailed explanation may everybody read at Chip Pearson's website(which should be celebrated as a Mankind Treasure Of VBA Wisdom...)
... 即任何代码，其中Dim arr1()紧跟LBound(arr1)/UBound(arr1)调用ReDim arr1(...)之间没有，崩溃。回旋处是使用 anOn Error Resume Next并Err.Number在LBound(arr1)/UBound(arr1)调用之后检查右侧- 如果数组已初始化，则应为 0，否则为非零。由于有一些 VBA 内置的不当行为，需要进一步检查数组的限制。详细解释大家可以在Chip Pearson的网站上看（应该被称为VBA智慧的人类宝藏……）
Heh, that's my first post, believe it is legible.
呵呵，这是我的第一篇文章，相信它是清晰的。
