[ACCEPTED]-How to push value into non pre specific array size?-vba

Accepted answer
Score: 17

Arrays can be defined like

Dim MyArray() as string

and then sized 4 and re-sized at run time

Redim MyArray(lb to ub)

or, to keep any 3 existing data that is in the array

Redim Preserve MyArray(lb to ub)

lb and 2 ub are bounds of array, and can be determined 1 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
' 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
Score: 7

As silly as it might sound, sometimes it 12 is useful to use a string which can be seen 11 as a dynamic array of sorts, then split 10 it. This approach works only if the objects 9 in your resulting array are strings or numbers 8 and you can be sure that the char. sequence 7 you select as a separator will not occur 6 inside any of the string representations 5 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 4 use under certain special circumstances, as 3 it is a somewhat more intuitive way. Beware 2 that an Array you create this way is an 1 array of Strings!

Score: 1

Although using collections or dictionaries 15 might be better options for incrementally 14 adding elements, there could be times when 13 it is easier to just increment an array.

Function to push to an array

Here 12 is a function that can add an element or 11 even multiple elements to the end of an 10 array. I based this on how JavaScripts push method 9 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)
            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 8 ArrayDimensionLength to insure that an array was passed in and 7 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
        Dim boundIndex As Long
        boundIndex = boundIndex + 1

        ' Loop until this line errors out.
        Dim test As Long
        test = UBound(sourceArray, boundIndex)
    ' Must remove one, this gives the proper dimension length.
    ArrayDimensionLength = boundIndex - 1

End Function

Example using this function

You can 6 add single elements at a time, or several 5 at once. Just note it has to ReDim the array 4 each time, so be aware of this in using 3 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 2 and other similar array functions on my 1 GitHub Page.

More Related questions