A user defined function can only effect the cell that it is in when called
from a sheet. There is no way around that. If called from ithin code it can
effect whatever cells it want to, but not when called froma cell.
--
HTH...

Jim Thomlinson


" wrote:

I have a VBA function that returns an array of variable size.
How do I use it in the spreadsheet without knowing the size
(and perhaps not even the shape) a priori?

Consider the following trivial example:

function mytest()
dim myarr(3,1)
myarr(0,0) = 1: myarr(1,0) = 2: myarr(2,0) = 3
mytest = myarr
end function

Normally, I use it by highlighting 3 cells in a column, enter
=mytest(), then press ctrl-shift-Enter.

But what if I did not know the function returned 3 elements,
but (a) I knew the shape, or (b) I did not even know the
shape?

Ideally, I would like to highlight just the upper-left corner
(like cut-and-paste of multiple cells), enter =mytest(), and
press ctrl-shift-Enter. Of course, that does not have the
desired effect (namely, filling in adjacent with cells with the
entire array result).