vba 如何将值推入非预先指定的数组大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5228792/
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
How to push value into non pre specific array size?
提问by LeenNew
I'm seeking help to push in value parse from XML based on certain filter/word matching into an arraylist. However, this array should not have a pre-defined array size as the XML inputs are dynamic from file to file. Meaning, XML file1 may have 10 of such inputs and XML File2 may have 15 inputs. Can someone pls advise how I can do below 2things:
我正在寻求帮助将基于某些过滤器/单词匹配的 XML 值解析推入arraylist. 但是，此数组不应具有预定义的数组大小，因为 XML 输入在文件之间是动态的。意思是，XML file1 可能有 10 个这样的输入，而 XML File2 可能有 15 个输入。有人可以建议我如何做以下两件事：
- How to define an array list with pre-defining the array size? The size depends on the XML input list when the user reads node by node
- When XML word matching found, will parse XML input/value into Excel VBA and keep inside this array.
- 如何通过预先定义数组大小来定义数组列表？大小取决于用户逐节点读取时的 XML 输入列表
- 当找到 XML 词匹配时，将 XML 输入/值解析为 Excel VBA 并保留在此数组中。
回答by chris neilsen
Arrays can be defined like
数组可以像这样定义
Dim MyArray() as string
and then sized and re-sized at run time
然后在运行时调整大小和重新调整大小
Redim MyArray(lb to ub)
or, to keep any existing data that is in the array
或者，保留数组中的任何现有数据
Redim Preserve MyArray(lb to ub)
lb and ub are bounds of array, and can be determined by code, eg
lb 和 ub 是数组的边界，可以通过代码确定，例如
lb = 1
ub = <number of matched found in xml>
to progressively resize
逐步调整大小
redim MyArray (0 to 0)
For each line in xml
if Match then
MyArray(ubound(MyArray)) = Match
Redim Preserve MyArray(0 to ubound(MyArray) + 1)
end if
Next
' Array ends up 1 size larger than number of matches '
if ubound(MyArray) > 0 then
redim Preserve MyArray (0 to ubound(MyArray) - 1)
end if
回答by user4483400
As silly as it might sound, sometimes it is useful to use a string which can be seen as a dynamic array of sorts, then split it. This approach works only if the objects in your resulting array are strings or numbers and you can be sure that the char. sequence you select as a separator will not occur inside any of the string representations of your objects, as e.g.:
尽管听起来很愚蠢，但有时使用一个可以被视为各种动态数组的字符串，然后将其拆分是很有用的。只有当结果数组中的对象是字符串或数字并且您可以确定 char. 您选择作为分隔符的序列不会出现在对象的任何字符串表示中，例如：
Temp = ""
Separator = ","
For A = 1 to 155
If Temp <> "" Then
Temp = Temp & Separator
End If
Temp = Temp & CStr(A)
Next 'A
myArray = Split(Temp, Separator)
'now myArray has the elements 1, 2, ..., 155 (which are strings!)
This may be of use under certain special circumstances, as it is a somewhat more intuitive way. Beware that an Array you create this way is an array of Strings!
这在某些特殊情况下可能有用，因为它是一种更直观的方式。请注意，您以这种方式创建的数组是字符串数组！
回答by Robert Todar
Although using collections or dictionaries might be better options for incrementally adding elements, there could be times when it is easier to just increment an array.
虽然使用集合或字典可能是增量添加元素的更好选择，但有时只增加数组可能更容易。
Function to push to an array
推送到数组的函数
Here is a function that can add an element or even multiple elements to the end of an array. I based this on how JavaScripts pushmethod works.
这是一个可以将一个元素甚至多个元素添加到数组末尾的函数。我基于 JavaScriptspush方法的工作原理。
' Mutates array by adding element(s) to the end of an array. Returns the new array length.
Public Function ArrayPush(ByRef sourceArray As Variant, ParamArray elements() As Variant) As Long
'@author: Robert Todar <https://github.com/todar>
'@param: <SourceArray> must be a single dimensional array.
'@param: <elements> are the elementss to be added.
' Change this if you prefer to work with option base 1
Const optionBase As Long = 0
Dim firstEmptyBound As Long
Select Case ArrayDimensionLength(sourceArray)
Case 0
firstEmptyBound = optionBase
' Create space for new elements in empty array.
ReDim sourceArray(optionBase To UBound(elements, 1) + optionBase)
Case 1
firstEmptyBound = UBound(sourceArray) + 1
' Add more space for new elements.
ReDim Preserve sourceArray( _
LBound(sourceArray) To UBound(sourceArray) + UBound(elements) + 1)
Case Else
Err.Raise 5, "ArrayPush", "ArrayPush function only works with single dimension arrays."
End Select
Dim index As Long
For index = LBound(elements) To UBound(elements)
' Add elements to the end of the array. Assign is to 'set' or 'let' depending on type.
If IsObject(elements(index)) Then
Set sourceArray(firstEmptyBound) = elements(index)
Else
Let sourceArray(firstEmptyBound) = elements(index)
End If
' Increment to the next empty index
firstEmptyBound = firstEmptyBound + 1
Next index
' Return new array length
ArrayPush = UBound(sourceArray) + (Int(optionBase = 0) * -1) - LBound(sourceArray)
End Function
This function also uses a helper function ArrayDimensionLengthto insure that an array was passed in and that it is only a single dimension.
该函数还使用一个辅助函数ArrayDimensionLength来确保传入的数组是单一维度的。
' Returns the length of the dimension of an array.
Public Function ArrayDimensionLength(ByVal sourceArray As Variant) As Long
On Error GoTo Catch
Do
Dim boundIndex As Long
boundIndex = boundIndex + 1
' Loop until this line errors out.
Dim test As Long
test = UBound(sourceArray, boundIndex)
Loop
Catch:
' Must remove one, this gives the proper dimension length.
ArrayDimensionLength = boundIndex - 1
End Function
Example using this function
使用此函数的示例
You can add single elements at a time, or several at once. Just note it has to ReDimthe array each time, so be aware of this in using it with large loops.
您可以一次添加单个元素，也可以一次添加多个元素。只需注意它ReDim每次都必须添加到数组中，因此在将其与大循环一起使用时要注意这一点。
Private Sub testArrayPush()
Dim data() As String
' Single element
ArrayPush data, "apple"
' Multiple elements
ArrayPush data, "banana", "carrot"
Debug.Print Join(data, ", ") '<~ apple, banana, carrot
End Sub
You can find this function and other similar array functions on my GitHub Page.
你可以在我的GitHub Page上找到这个函数和其他类似的数组函数。
