Sub Main()
Dim v As Variant
Dim x As Long, s As String
Dim i As Long
x = 10
v = MyFunc(x)
For i = LBound(v) To UBound(v)
s = s & v(i) & ", "
Next
s = Left(s, Len(s) - 2)
MsgBox s
End Sub

Public Function MyFunc(n As Long)
Dim vv As Variant
Dim i As Long
ReDim vv(0 To n - 1)
For i = LBound(vv) To UBound(vv)
vv(i) = Int(Rnd() * 100 + 1)
Next
MyFunc = vv
End Function

You can only pass an array to a variant. So to receive the array back, you
need to use a variant to equate to the function name

v = MyFunc(x)

You can also array enter that function

Select

A1:J1 and in the formula bar enter

=MyFunc(10)

and the do Control+Shift+enter to enter it as an array formula

one dimensional arrays are horizontal by default.

--
Regards,
Tom Ogilvy



"biosci" wrote:


I am sure this a simple question.

I know how to use functions to return a single value (value as function
name), but I do not know how to use them to return multiple variables
and/or arrays of numbers.

I need to use a function, but the function needs to return and array of
numbers (eg A(1 to X), where X is defined by sub calling the function)
as well as another value.

Do I need to specify these at the start of the function and if so,
how?

Do I need to use the ByRef and/or ParamArray keywords?

Help would be appreciated


--
biosci
------------------------------------------------------------------------
biosci's Profile: http://www.excelforum.com/member.php...o&userid=32827
View this thread: http://www.excelforum.com/showthread...hreadid=537702