How to push value into non pre specific array size?

Question

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:

1. 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
2. When XML word matching found, will parse XML input/value into Excel VBA and keep inside this array.

Answer 1

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 = 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

Answer 2

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.:

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!