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

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

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

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

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

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

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