User function for returning several results

[briansol]

I'm creating an Excel add-in with functions to access a database. I'v
got no problem returning one value, but I don't know how to return
resultset containing more than one instance like an array.

Example:

User calls: =Analysis("12/04/2004")

And Excels returns (to the cell which the function was called) all th
the analysis on the date based on mye resultset like this:

12,4
11,2
10,1

My main problem is returning several values and displaying them.

I've seen some examples using transpose, but I'm not sure how to us
it.

Many thanks in Advance

 

[keepITcool]

If your function returns an array of data ALL that data will be in the
cell unless you've called it from an 'arrayformula'

If you enter the function in your sheet as follows:

select a1:f10
in a1 type =Analysis("12/04/2004") then press CTRL-SHIFT-ENTER

you'll see {} around the formula, and you should see some more data in
your sheet... and probably quite a few Errors values too...

there are tricks so that the function returns an array of the same size
as the calling "array formula"

your function has to redim it's result array to the size of
application.caller.currentarray, and make sure it doesnt return
array elements as empty or 0 but as vbnullstring.





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[briansol]

Thanks for your help