Results 1 to 6 of 6

20021120, 11:51 #1
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
returning values from functions (XL97 sr2 on Win 2000 sp2)
Is it possible to get a user defined VBA function to return 2 different values?
For example if I entered =myFunction(arg_list) as an array formula over two cells could the code return two different numbers, one in each cell?
stuck

20021120, 12:02 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: returning values from functions (XL97 sr2 on Win 2000 sp2)
Yes, by making the return value an array.
For example:
Function myFunction(ParamArray arg_list())
myFunction = Array(WorksheetFunction.Sum(arg_list), WorksheetFunction.Average(arg_list))
End Function
Of course, you must use this in an array formula (confirm with Ctrl+Shift+Enter).
For instance:
=myFunction(1, 2, 3, 4, 5) entered in two cells next to each other as an array formula will return 15 in the first cell and 3 in the second cell.
You can also use
=myFunction(A1, B3, C5, D99)

20021120, 13:04 #3
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: returning values from functions (XL97 sr2 on Win 2000 sp2)
I think see what you mean Hans, so what is wrong with my implementation?
The worksheet contains =myFunction(a range of cells, a count) entered as an array formula over two adjacent cells, say B1 and B2.
Then the code is:
myFunction(myRange,myCell)
Dim aryReturnValues(2) ' NB I've got Option Base = 1 set
...code to derive two values from the data in myRange and the count in myCell...
...the values end up in two variables, say A and B...
myFunction = aryReturnValues(A , [img]/forums/images/smilies/cool.gif[/img]
End Function
I know the code in the middle works correctly, I can get the two values out one at a time by using the code twice in two separate functions that only differ in their names and the last line where the varible is assigned to the function name.
stuck

20021120, 13:26 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: returning values from functions (XL97 sr2 on Win 2000 sp2)
Try
Function myFunction(myRange,myCell)
Dim aryReturnValues(2) ' NB I've got Option Base = 1 set
...code to derive two values from the data in myRange and the count in myCell...
...the values end up in two variables, say A and B...
aryReturnValues(1) = A
aryReturnValues(2) = B
myFunction = aryReturnValues
End Function
or
Function myFunction(myRange,myCell)
...code to derive two values from the data in myRange and the count in myCell...
...the values end up in two variables, say A and B...
myFunction = Array(A , [img]/forums/images/smilies/cool.gif[/img]
End Function

20021120, 13:44 #5
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: returning values from functions (XL97 sr2 on Win 2000 sp2)
The function produces a horizontal array, so if you select B1 and C1 you should be OK.
Otherwise select B1 and B2 and use
=TRANSPOSE(myFUNCTION(arg1,arg2))Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20021120, 14:14 #6
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: returning values from functions (XL97 sr2 on Win 2000 sp2)
YESSSS!
Both of Hans' suggestions work now that Jan has got the horizontals and veritcals sorted out. What a team!
<img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>
(un) stuck