Adding an element to the end of an array
I would like to add a value to the end of a VBA array. How can I do this? I was not able to find a simple example online. Here's some pseudocode showing what I would like to be able to do.
Public Function toArray(range As range)
Dim arr() As Variant
For Each a In range.Cells
'how to add dynamically the value to end and increase the array?
arr(arr.count) = a.Value 'pseudo code
Next
toArray= Join(arr, ",")
End Function
I solved the issue by using a Collection and copy it afterwards to an array.
Dim col As New Collection
For Each a In range.Cells
col.Add a.Value ' dynamically add value to the end
Next
Dim arr() As Variant
arr = toArray(col) 'convert collection to an array
Function toArray(col As Collection)
Dim arr() As Variant
ReDim arr(0 To col.Count-1) As Variant
For i = 1 To col.Count
arr(i-1) = col(i)
Next
toArray = arr
End Function
Try this [EDITED]:
Dim arr() As Variant ' let brackets empty, not Dim arr(1) As Variant !
For Each a In range.Cells
' change / adjust the size of array
ReDim Preserve arr(1 To UBound(arr) + 1) As Variant
' add value on the end of the array
arr (UBound(arr)) = a.value
Next