在 VBA 中附加动态数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28684942/
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
Appending a dynamic array in VBA
提问by KingKong
I'm looping through a named range and appending an array based on if the cell is empty or not. I'm not too familiar with arrays in VBA, so perhaps my logic is incorrect. The code keeps overwriting the 1st element in the array and it never gets incremented. I"m not sure why the UBound(myArray) always stays at 0 even after an element has been assigned to the 1st index.
我正在遍历一个命名范围并根据单元格是否为空附加一个数组。我对 VBA 中的数组不太熟悉，所以可能我的逻辑不正确。代码不断覆盖数组中的第一个元素，它永远不会增加。我不知道为什么 UBound(myArray) 即使在将元素分配给第一个索引后也始终保持为 0。
My code looks something like:
我的代码看起来像：
Dim myArray() As Double
ReDim Preserve myArray(0)
For Each cell In [myRange]
If cell <> "" Then
If UBound(myArray) > 0 Then
ReDim Preserve myArray(0 To UBound(myArray) + 1)
End If
myArray(UBound(myArray)) = cell.value
End If
Next
回答by Dan Donoghue
Use a variable and increment it, it makes no difference if the variable is larger than the ubound when the code completes:
使用变量并递增它，如果代码完成时变量大于 ubound，则没有区别：
Sub Test()
Dim myArray() As Double, X As Long
X = 0
ReDim Preserve myArray(X)
For Each cell In Range("Hello")
If cell <> "" Then
ReDim Preserve myArray(0 To X)
myArray(X) = cell.Value
X = X + 1
End If
Next
End Sub
回答by Jeanno
Change If UBound(myArray) > 0 Thento If UBound(myArray) >= 0 Thenthat will solve the problem.
更改If UBound(myArray) > 0 Then为如果UBound(myArray) >= 0 Then这将解决问题。
Sub Test()
Dim myArray() As Double
ReDim Preserve myArray(0)
For Each cell In Range("Hello")
If cell <> "" Then
If UBound(myArray) >= 0 Then
myArray(UBound(myArray)) = cell.Value
ReDim Preserve myArray(0 To UBound(myArray) + 1)
End If
End If
Next
End Sub
回答by B M
Expanding Dan Donoghue's solution to avoid excessive redim (and excessive recalculation in the case of Jeanno's solution through use of variables), I would use
扩展 Dan Donoghue 的解决方案以避免过度 redim（以及通过使用变量在 Jeanno 解决方案的情况下过度重新计算），我会使用
Dim rng as Range
Set rng = Range("Hello")
Dim myArray() As Double, X As Long, N As Long
X = 0
N = WorksheetFunction.CountIf(rng, "<>")
ReDim Preserve myArray(X)
For Each cell In rng
If cell <> "" And X < N Then
myArray(X) = cell.Value
X = X + 1
If X < N Then ReDim Preserve myArray(0 to X)
End If
Next
