7

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.
Robert Todar
  • 2,085
  • 2
  • 11
  • 31
LeenNew
  • 109
  • 1
  • 4
  • 6
  • 1
    You may also get some value out of looking at http://stackoverflow.com/q/8560718/641067 . This question runs a `Redim Preserve` every 1000 records using a `Mod` test as the `Redim` process can be expensive code time wise – brettdj Dec 22 '11 at 05:37

3 Answers3

18

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
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Thanks Chris, however i will only be able to know the ub of the array at the end of the For loop but i need to assign/push the input value into the array each time a matching found from XML. – LeenNew Mar 08 '11 at 08:11
  • @LeenNew, the redim when each match is found. I'll edit answer to demo – chris neilsen Mar 08 '11 at 08:30
7

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!

user4483400
  • 71
  • 1
  • 2
1

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 push 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 a helper function ArrayDimensionLength to insure that an array was passed in and that it is only a single 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

Example using this function

You can add single elements at a time, or several at once. Just note it has to ReDim the array each time, so be aware of this in 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.

Robert Todar
  • 2,085
  • 2
  • 11
  • 31