[ACCEPTED]-How to push value into non pre specific array size?-vba
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 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
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!
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
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) 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 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 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 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