Roger
asked on
Return vba array from user defined function
I'm having difficulties with the basics of passing an array from a function in VBA.
To be used in vba macros in MS Office (Excel & Visio), but without reference to excel ranges.
I start by making a simple dynamic array:
Sub makeArray()
Dim arrItems() As Long
Dim i As Integer
Dim m As Long
m = 2
For i = 0 To 9
ReDim Preserve arrItems(i + 1)
arrItems(i) = i * 10 * m
Next i
For i = 0 To UBound(arrItems) - 1
Debug.Print "arrItems(" & i & ") = "; arrItems(i)
Next i
End Sub
How do I convert the above to a function to be called from this Sub():
Sub doMakeArray()
myArray() as long
Dim m as long
m = 2
myArray =doMakeArray(m)
end sub
I would start off:
Function doMakeArray(m as long)
I would seek to conclude:
doMakeArray = arrItems
I wish to return arrays of either strings or long - data types
Thanks
Kelvin4
To be used in vba macros in MS Office (Excel & Visio), but without reference to excel ranges.
I start by making a simple dynamic array:
Sub makeArray()
Dim arrItems() As Long
Dim i As Integer
Dim m As Long
m = 2
For i = 0 To 9
ReDim Preserve arrItems(i + 1)
arrItems(i) = i * 10 * m
Next i
For i = 0 To UBound(arrItems) - 1
Debug.Print "arrItems(" & i & ") = "; arrItems(i)
Next i
End Sub
How do I convert the above to a function to be called from this Sub():
Sub doMakeArray()
myArray() as long
Dim m as long
m = 2
myArray =doMakeArray(m)
end sub
I would start off:
Function doMakeArray(m as long)
I would seek to conclude:
doMakeArray = arrItems
I wish to return arrays of either strings or long - data types
Thanks
Kelvin4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually line 16 should be ReDim arrItems(9)
You can use:
Function doMakeArray(m As Long) As Variant
That will allow you to return an array.
/gustav
Function doMakeArray(m As Long) As Variant
That will allow you to return an array.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
Kelvin
Kelvin