Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use function that returns array of variable size?
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use function that returns array of variable size?
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use function that returns array of variable size?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use function that returns array of variable size?
"Jim Thomlinson" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array function that returns values within several intervals | Excel Worksheet Functions | |||
Using Array variable in FIND function | Excel Programming | |||
Using Array variable in FIND function | Excel Programming | |||
Define an array with variable size | Excel Programming | |||
How to find out the size of a variable-size array ? | Excel Programming |