Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function returning 0 instead of cell value | Excel Worksheet Functions | |||
INDIRECT function returning REF# | Excel Worksheet Functions | |||
function returning array | Excel Programming | |||
Returning by Function | Excel Programming | |||
Returning value from function | Excel Programming |