How do I output an array from a function?

[Mightyginger]

I remember seeing this asked once before, many months ago, but can't find the thread.

Simple question.

How do I make my function return an array? So normally a function just returns one result in the one cell. But if I want to have three results generated by the function and, say, have them appear in cell A1,B1,B2 where I would have in each of the cells {thefunction(var1,var2...)}

Hope this make sense, any help would be appreciated guys.


Neil.

 

[TonyJollans]

Hi Mightyginger,

Provided you have Excel 2000 you can define your function like ..

Function thefunction(var1, var2, ... ) As String()

Note that any attempt to use thefunction(index) inside thefunction is interpreted as a recursive call so the way to do it is to define and build your array as you wish and at the end assign it to be passed back ..

thefunction = builtarray

Enjoy,
Tony

 

[Mightyginger]

top man, cheers mate.

 

[Mightyginger]

Hmm, having a little trouble with this.

I have my function function_name(var1,var2) As String() as you said
I've then defined an array e.g. arrayname(1) as double
then at the end I have put function_name=arrayname but I get the error Cant Assign To Array - any ideas?


Neil.

 

[TonyJollans]

Hi Mightyginger,

If your array is type double, your function must be type double as well ..

Function whatever() as Double()

Enjoy.
Tony

 

[Mightyginger]

thanks again Tony. I'd assumed that using variant would solve the problem, which it didn't. Seems obvious nw to use double.

Thanks again mate,



Neil.