How to use function that returns array of variable size?

[Guest]

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).

 

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

A user defined function can only effect the cell that it
is in when called from a sheet.

Right. Just testing to see if you're awake ;-). Kidding!
My bad. I realized after posting that I really wanted a
macro (sub). ActiveCell.Offset suits my purposes.
Thanks for the quick response.