How to insert a value into an undefined array size?

Question

I am looking for help to push the analysis of meaning from XML based on a specific filter / word match in arraylist . However, this array does not have to have a predetermined array size, since the XML input is dynamic from file to file. That is, XML file1 can have 10 such inputs, and XML file2 can have 15 inputs. Can someone please advise how I can do below 2 things:

1. How to determine the list of arrays with preliminary determination of the size of the array? The size depends on the XML input list when the user reads site by site
2. When an XML word match is found, it parses the XML input / value in Excel VBA and stores it in this array.

Answer 1

Arrays can be defined as

 Dim MyArray() as string 

and then size and resizing at runtime

 Redim MyArray(lb to ub) 

or to save existing data in an array

 Redim Preserve MyArray(lb to ub) 

lb and ub are the boundaries of the array and can be defined by code, for example

 lb = 1 ub = <number of matched found in xml> 

for gradual resizing

 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 

Answer 2

As silly as it may seem, it is sometimes useful to use a string that can be thought of as a dynamic array, and then split it. This approach only works if the objects in the resulting array are strings or numbers, and you can be sure that char. the sequence that you select as a separator does not occur in any of the string representations of your objects, for example:

 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 can be useful in certain special circumstances, as this is a somewhat more intuitive way. Remember that the array you created in this way is an array of strings!

Answer 3

Although using collections or dictionaries may be the best option for adding items in stages, there are times when it’s easier to just expand the array.

---

Function to push to array

Here is a function that can add an element or even several elements to the end of an array. I based this on how the push JavaScripts method works.

 ' 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 the helper function ArrayDimensionLength to ensure that the array has been passed and that it is only one dimension.

 ' 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 

---

An example of using this function

You can add individual elements at the same time or several at the same time. Just note that it needs a ReDim array every time, so keep that in mind when using it with large loops.

 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 .