Link to home
Start Free TrialLog in
Avatar of Roger
RogerFlag for United Kingdom of Great Britain and Northern Ireland

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
 
ASKER CERTIFIED SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually line 16 should be ReDim arrItems(9)
Avatar of Gustav Brock
You can use:

Function doMakeArray(m As Long) As Variant

That will allow you to return an array.

/gustav
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Roger

ASKER

Thanks!
Kelvin