On Mon, 13 Feb 2006 08:45:14 -0800, "
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).

1. AFAIK, you can't just highlight the upper-left corner with a UDF any more
than you could with a built-in worksheet function that returns an array.

2. You could highlight an area larger than the conceivable return, and "white
out" the error messages with conditional formatting.

3. You could use the INDEX function in a similar manner, and use an IF
function to get rid of the error messages.

e.g. =IF(ISERR(INDEX(MYTEST(),3,3)),"",INDEX(MYTEST(),3 ,3))



--ron