Returning an Array from a called function within a macro

[brujolito]

How do I get a function to return an array. [I know that it should be
doable since the various matrix functions return an array.]

To be more precise I need to do this within a macro. I know how to do
it at the spreadsheet level.

I am running Excel 2004 on a MAC [OSX]

Thanks
Brujolito

 

[JE McGimpsey]

One way:

Public Function foo() As Variant
foo = Array(1,2,3,4,5)
End Function

You can then array-enter the function into, say A1:E1,

=foo()

to get the values 1->5 into those cells, or use

=SUM(foo()) ===> 15

You can obviously get much more complicated...