Array Formula question

Hi,

I have a user defined function in an xll add-in that is returning an array.
But the dimensions of the array returned can differ from call to call.

Is there a way to produce this scenario with Excel C API.

* the user select a single cell
* the user enter a function name and args
* the user press CTRL+SHIFT+ENTER
* the user defined function returns and array of x by y and the user can see
it

p.s. Then only way I can output an array is by selecting many cells and
entering the formula, but I can't select the right number of cells because I
don't know the size of the array that is returned.

I know I can do this by pressing F2 and then CTRL+SHIFT+ENTER, but is there a way to entirely do this with the SDK?

Thanks

Nic

No. the behavior is that you have to enter the formula in the number of
cells. You can enter it in the largest area that might be required - in
that case, the cells not used would return #N/A

--
Regards,
Tom Ogilvy

"nicgendron" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I have a user defined function in an xll add-in that is returning an
> array.
> But the dimensions of the array returned can differ from call to call.
>
> Is there a way to produce this scenario with Excel C API.
>
> * the user select a single cell
> * the user enter a function name and args
> * the user press CTRL+SHIFT+ENTER
> * the user defined function returns and array of x by y and the user
> can see
> it
>
> p.s. Then only way I can output an array is by selecting many cells and
>
> entering the formula, but I can't select the right number of cells
> because I
> don't know the size of the array that is returned.
>
> I know I can do this by pressing F2 and then CTRL+SHIFT+ENTER, but is
> there a way to entirely do this with the SDK?
>
> Thanks
>
> Nic
>
>
> --
> nicgendron
> ------------------------------------------------------------------------
> nicgendron's Profile:
http://www.excelforum.com/member.php...o&userid=25151
> View this thread: http://www.excelforum.com/showthread...hreadid=397178
>

You have now asked this question on each of the last 3 days (that I
know of). Commendable persistence but the answer won't change.

About the best you can do is what I do when returning an array as a
function result -- and this is predicated on you having access in the
XLL to the equivalent of what in a VBA function would be
Application.Caller.

Now, check how many cells the user has selected. Then, if the shape of
the selection is not correct, return an error instead of the array.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> Hi,
>
> I have a user defined function in an xll add-in that is returning an
> array.
> But the dimensions of the array returned can differ from call to call.
>
> Is there a way to produce this scenario with Excel C API.
>
> * the user select a single cell
> * the user enter a function name and args
> * the user press CTRL+SHIFT+ENTER
> * the user defined function returns and array of x by y and the user
> can see
> it
>
> p.s. Then only way I can output an array is by selecting many cells and
>
> entering the formula, but I can't select the right number of cells
> because I
> don't know the size of the array that is returned.
>
> I know I can do this by pressing F2 and then CTRL+SHIFT+ENTER, but is
> there a way to entirely do this with the SDK?
>
> Thanks
>
> Nic
>
>
> --
> nicgendron
> ------------------------------------------------------------------------
> nicgendron's Profile: http://www.excelforum.com/member.php...o&userid=25151
> View this thread: http://www.excelforum.com/showthread...hreadid=397178
>
>