Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
I've written a function that evaluates a column of values and creates an
array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
Function ReturnArray()
dim varr(1 to 10) as Long for i = 1 to 10 varr(i) = i^2 Next ReturnArray = varr End Function Sub Testit() Dim vArr as Variant Dim sMsg as String vArr = ReturnArray() for i = lbound(varr) to ubound(varr) sMsg = sMsg & cstr(varr(i)) & ", " Next sMsg = Left(sMsg,len(sMsg)-1) msgbox sMsg End Sub -- Regards, Tom Ogilvy "Raul" wrote in message ... I've written a function that evaluates a column of values and creates an array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
Raul,
Here is a very simple example Sub test() Dim myAry Dim i As Long myAry = aryFunction For i = LBound(myAry, 1) To UBound(myAry, 1) Debug.Print myAry(i) Next i End Sub Function aryFunction() Dim i As Long Dim ary ary = [{1,2,3}] aryFunction = ary End Function -- HTH RP (remove nothere from the email address if mailing direct) "Raul" wrote in message ... I've written a function that evaluates a column of values and creates an array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
Tom and Bob,
Thanks a bunch, both your examples really helped; and both worked perfectly in Excel version 10. In your examples you use the following: ReturnArray = vArr (Tom's example) or aryFunction = ary (Bob's example) It looks like you're assigning one array to another. I may be missing something. If you are assigning one array to another, and versions of Excel that are earlier than version 10 don't have that capability, how do you handle this type of situation in the earlier versions of Excel? Thanks in advance (again), Raul "Raul" wrote: I've written a function that evaluates a column of values and creates an array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
We didn't declare the function as any particular type, so it is a variant.
A variant can hold an array at least as far back as xl97. So we are assigning an array to a variant. -- Regards, Tom Ogilvy "Raul" wrote in message ... Tom and Bob, Thanks a bunch, both your examples really helped; and both worked perfectly in Excel version 10. In your examples you use the following: ReturnArray = vArr (Tom's example) or aryFunction = ary (Bob's example) It looks like you're assigning one array to another. I may be missing something. If you are assigning one array to another, and versions of Excel that are earlier than version 10 don't have that capability, how do you handle this type of situation in the earlier versions of Excel? Thanks in advance (again), Raul "Raul" wrote: I've written a function that evaluates a column of values and creates an array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
Raul,
To add to Tom's comment, we both used a function to build an array, and used an array local to that function to store it as we built it. To return the array as you wanted, we have to pass that temporary array to the variant that the function returns. -- HTH RP (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... We didn't declare the function as any particular type, so it is a variant. A variant can hold an array at least as far back as xl97. So we are assigning an array to a variant. -- Regards, Tom Ogilvy "Raul" wrote in message ... Tom and Bob, Thanks a bunch, both your examples really helped; and both worked perfectly in Excel version 10. In your examples you use the following: ReturnArray = vArr (Tom's example) or aryFunction = ary (Bob's example) It looks like you're assigning one array to another. I may be missing something. If you are assigning one array to another, and versions of Excel that are earlier than version 10 don't have that capability, how do you handle this type of situation in the earlier versions of Excel? Thanks in advance (again), Raul "Raul" wrote: I've written a function that evaluates a column of values and creates an array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return an array of values from a function
Thanks for the responses. I really appreciate the help.
Thanks again, Raul "Raul" wrote: Tom and Bob, Thanks a bunch, both your examples really helped; and both worked perfectly in Excel version 10. In your examples you use the following: ReturnArray = vArr (Tom's example) or aryFunction = ary (Bob's example) It looks like you're assigning one array to another. I may be missing something. If you are assigning one array to another, and versions of Excel that are earlier than version 10 don't have that capability, how do you handle this type of situation in the earlier versions of Excel? Thanks in advance (again), Raul "Raul" wrote: I've written a function that evaluates a column of values and creates an array of values and some other variables that are to be passed back to the procedure that calls the array. The function creates the desired array and variables but I don't know how to get these back to the procedure that called the function. Thanks in advance, Raul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return values from an array based on more than one criteria | Excel Discussion (Misc queries) | |||
How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
Use array to return array of values | Excel Worksheet Functions | |||
VBA Syntax for VLOOKUP to return array of return values | Excel Programming |