Functions that return multiple values

[biosci]

I am sure this a simple question.

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

I need to use a function, but the function needs to return and array o
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 appreciate

 

[Guest]

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.

 

[Rick Hansen]

Good Afternoon Biosci,
After reading Tom return reply to question this morning it got me to
thinking. There is another way to pass back multiple answers in a Function.
You use what they call a "User Defined Varible" also know as a structure in
C, C++, and Records in Pascal. Any you can define this variable have
multiple data types, so multiple answer can be returned under one variable
name. I've attached some same example code that can be easily adapted for
Ranges, Strings, other items in Excels VBA .

Enjoy. Rick, (Fairbanks, Alaska)

==============================
Option Explicit

Type Xrec
Ar() As Integer
Str As String
End Type

Sub testvar()
Dim X As Xrec
Dim t As Integer

X = Fillvar(15)
Debug.Print X.Str
For t = LBound(X.Ar) To UBound(X.Ar)
Debug.Print X.Ar(t)
Next t

End Sub

Function Fillvar(arCnt As Integer) As Xrec
Dim t As Integer
Dim X As Xrec
ReDim X.Ar(0 To arCnt - 1)
For t = LBound(X.Ar) To UBound(X.Ar)
X.Ar(t) = Int(Rnd() * 100 + 1)
Next
X.Str = "We Be Done"
Fillvar = X
End Function
=====================================