Title:VBA, return an array from a function
Author:Mark Kiehl
Category:VBA
'Return an array from a function.

Sub TestArrFn()
Dim lSize as Long, l as Long

lSize = UBound(arrList)
MsgBox "The size of the returned array is " & str(lSize)

For l = 1 to lSize
debug.print arrList(l)
Next l

End Sub

Function arrList()
'This function returns an array of anything.
Dim lArrRows as Long, lRow as Long
Dim arrTmp() as Variant

lArrRows = 0
For lRow = 1 to 15
lArrRows = lArrRows + 1
ReDim Preserve arrTmp(1 To lArrRows)
arrTmp(lArrRows) = "MyArrayValue" & trim(str(lRow))
Next lRow

arrList = arrTmp
erase arrTmp
end function