adding values to variable array VBA
95,182
Solution 1
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.
Solution 2
Private Sub ArrayMy(DataRange)
Dim DataIndex() As String
i = 0
On Error Resume Next
ReDim DataIndex(0)
For Each c In DataRange
DataIndex(i) = c
i = i + 1
ReDim Preserve DataIndex(i)
Next
End Sub
Author by
Trung Tran
Updated on January 22, 2021Comments
-
Trung Tran over 3 years
I am trying to loop through a
table
that has acolumn
for"customers" and "dollar amount"
. If my loop finds acustomer
called"greg" or "henry"
I want to add his"dollar amount"
to an array of an unknown size.Can someone please help me?