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

returning more than one value from a VBA function
 

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" wrote in message
...
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

returning more than one value from a VBA function
 

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;10 00,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" wrote in message
...
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" wrote in message
...
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