Return an array of values from a function

[Guest]

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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

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)

 

[Guest]

Thanks for the responses. I really appreciate the help.

Thanks again,
Raul