returning more than one value from a VBA function

I have a function that returns multiple values. How can I return these
values to spreadsheet and how can I call this function from another
function? Is an array the best way to go? Also, can I use 2 (or more)
dimensional arrays as parameters for functions? ParamArray seems to limit
to one-dimensional arrays.

thanks

This is a do nothing example that shows how to pass back an array and that
you can pass multi dimension arrays as arguments to a function that uses a
ParamArray variable.

Function MyFunc(ParamArray vArr())
Dim varr1(), i As Long, k As Long, dimensions As Long
ReDim varr1(1 To UBound(vArr) - LBound(vArr) + 1)
For i = LBound(vArr) To UBound(vArr)
If TypeName(vArr(i)) = "Range" Then
Debug.Print "Range"
ElseIf IsArray(vArr(i)) Then
dimensions = 0
On Error Resume Next
Do
ub = UBound(vArr(i), dimensions + 1)
' Debug.Print i, dimensions, ub
dimensions = dimensions + 1
Loop While Err.Number = 0
Err.Clear
On Error GoTo 0
k = k + 1
varr1(k) = dimensions - 1
End If
Next
MyFunc = varr1
End Function


Sub Tester2()
Dim v1 As Variant, v2() As Variant
Dim v3(1 To 3, 1 To 5, 1 To 2) As String
Dim v4() As Variant, vArr As Variant
ReDim v1(1 To 10)
ReDim v2(1 To 2, 1 To 2)
ReDim v4(1 To 1, 1 To 3, 1 To 5, 1 To 2)
vArr = MyFunc(v1, v2, v3, v4)
For i = LBound(vArr) To UBound(vArr)
Debug.Print vArr(i)
Next
End Sub

--
Regards,
Tom Ogilvy

"MJH" <[email protected]> wrote in message
news:[email protected]...
> I have a function that returns multiple values. How can I return these
> values to spreadsheet and how can I call this function from another
> function? Is an array the best way to go? Also, can I use 2 (or more)
> dimensional arrays as parameters for functions? ParamArray seems to limit
> to one-dimensional arrays.
>
> thanks
>
>

to array enter into cell, select D5:G5, in the formula bar put

=myFunc({1,2},{1,2;3,4;4,6},{10,20},{50,100;1,2;1000,2000})
enter with Ctrl+Shift+enter

to call it from another function

=SUM(myFunc({1,2},{1,2;3,4;4,6},{10,20},{50,100;1,2;1000,2000}))

Both of these worked.

if you selected D6:D9, you would do

=Transpose(myFunc({1,2},{1,2;3,4;4,6},{10,20},{50,100;1,2;1000,2000}))
entered with Ctrl+Shift+enter

This worked as well.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:OkA%23IO2%[email protected]...
> This is a do nothing example that shows how to pass back an array and that
> you can pass multi dimension arrays as arguments to a function that uses a
> ParamArray variable.
>
> Function MyFunc(ParamArray vArr())
> Dim varr1(), i As Long, k As Long, dimensions As Long
> ReDim varr1(1 To UBound(vArr) - LBound(vArr) + 1)
> For i = LBound(vArr) To UBound(vArr)
> If TypeName(vArr(i)) = "Range" Then
> Debug.Print "Range"
> ElseIf IsArray(vArr(i)) Then
> dimensions = 0
> On Error Resume Next
> Do
> ub = UBound(vArr(i), dimensions + 1)
> ' Debug.Print i, dimensions, ub
> dimensions = dimensions + 1
> Loop While Err.Number = 0
> Err.Clear
> On Error GoTo 0
> k = k + 1
> varr1(k) = dimensions - 1
> End If
> Next
> MyFunc = varr1
> End Function
>
>
> Sub Tester2()
> Dim v1 As Variant, v2() As Variant
> Dim v3(1 To 3, 1 To 5, 1 To 2) As String
> Dim v4() As Variant, vArr As Variant
> ReDim v1(1 To 10)
> ReDim v2(1 To 2, 1 To 2)
> ReDim v4(1 To 1, 1 To 3, 1 To 5, 1 To 2)
> vArr = MyFunc(v1, v2, v3, v4)
> For i = LBound(vArr) To UBound(vArr)
> Debug.Print vArr(i)
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "MJH" <[email protected]> wrote in message
> news:[email protected]...
> > I have a function that returns multiple values. How can I return these
> > values to spreadsheet and how can I call this function from another
> > function? Is an array the best way to go? Also, can I use 2 (or more)
> > dimensional arrays as parameters for functions? ParamArray seems to
limit
> > to one-dimensional arrays.
> >
> > thanks
> >
> >
>
>