Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions that return multiple values
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 -- biosc ----------------------------------------------------------------------- biosci's Profile: http://www.excelforum.com/member.php...fo&userid=3282 View this thread: http://www.excelforum.com/showthread.php?threadid=53770 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions that return multiple values
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. -- Regards, Tom Ogilvy "biosci" wrote: I am sure this a simple question. I know how to use functions to return a single value (value as function name), but I do not know how to use them to return multiple variables and/or arrays of numbers. I need to use a function, but the function needs to return and array of 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 appreciated -- biosci ------------------------------------------------------------------------ biosci's Profile: http://www.excelforum.com/member.php...o&userid=32827 View this thread: http://www.excelforum.com/showthread...hreadid=537702 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions that return multiple values
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 ===================================== "biosci" wrote in message ... I am sure this a simple question. I know how to use functions to return a single value (value as function name), but I do not know how to use them to return multiple variables and/or arrays of numbers. I need to use a function, but the function needs to return and array of 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 appreciated -- biosci ------------------------------------------------------------------------ biosci's Profile: http://www.excelforum.com/member.php...o&userid=32827 View this thread: http://www.excelforum.com/showthread...hreadid=537702 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Multiple conditions and multiple return values | Excel Worksheet Functions | |||
Search multiple values to return single values | Excel Worksheet Functions | |||
Functions that return multiple calculated values | Excel Programming |