Menu
I am trying to loop through a table that has a column for "customers" and "dollar amount". If my loop finds a customer called "greg" or "henry" I want to add his "dollar amount" to an array of an unknown size.
Can someone please help me?
272
VBA does not allow you to append items to an array. The standard method to work around this is to re-dimension to array to the required size and then add the required items. There are a number of problems with this method: Performance – The array is copied into a new array each time it is re-dimensioned.
To Fill a Dynamic ArrayOn the Tools menu, point to Macro and then click Macros. In the Macro dialog box, click fill_array, and then click Run.
In VBA you cannot append an item to an array. Instead you must re-dimension the array to be one index larger than the previous. You cannot check the Ubound (upper bounds) of an array if it does not have a dimension.
There are 3 ways to add items to the VBA Listbox: One at a time using the AddItem property. Adding an array/range using the List property. Adding a Range using the RowSource property.
If by unknown size, you mean that number of elements is unknown, you could use a dynamic array.
Dim aArray() As Single ' or whatever data type you wish to use
ReDim aArray(1 To 1) As Single
If strFirstName = "henry" Then
aArray(UBound(aArray)) = 123.45
ReDim Preserve aArray(1 To UBound(aArray) + 1) As Single
End If
Ubound(aArray) throws an error if the array hasn't been dimensioned, so we start by adding an element to it. That leaves us with an empty element at the end of the text, so your code should account for that. aArray(Ubound(aArray)-1) will give you the last valid element in the array.
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
