VBA UDF array fn (ctl-shf-ent) - finding size of spec'd return range

[electricpete]

When a spreadsheet user invokes an "array" or "matrix" function, he has to highlight the entire output range and press ctrl-shift-enter.

VBA must return an array with the exact dimensions matching that output range, otherwise an error occurs.

Typically the problem parameters make it obvious how big it should be. BUT it would be nice to be able to check the size of the range (and possibly add padding to the output if needed to make it less burdensome on user to define exact correct size of output).

The question is how the vba function will determine the size of the output array that it has to deliver back to the spreadsheet.

During intitial call to the function, we could use "selection". But that is not guaranteed to work later on for example if user alters a cell which provides an input to the function, the function will recompute but it cannot look at "selection" to determine (check) size of output range.

Thanks

 

[SkipVought]

Hi,

How about the UBound function?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

There is no array passed TO the function. It is returning an array to the spreadsheet.

To what variable will apply the Ubound function?

 

[SkipVought]

Well then how about posting your UDF code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

Thanks Skip. I think my question is answered here. Let me study that and get back if I have any further questions.

http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx

 

[DougAJ4]

I didn't read what Chip had to say about it, but when you return an array as a UDF return value it doesn't have to be the same size as the selected range.

If the selected range is smaller then the excess values just won't be displayed (they are still available to the =INDEX function), or if it is too big the cells outside the array range will display NA.

Either way, you won't get an error.

A similar thing happens when returning an array to a specified range from a sub by the way.

Doug Jenkins

http://newtonexcelbach.wordpress.com/

 

[DougAJ4]

and hi electricpete

Doug Jenkins

http://newtonexcelbach.wordpress.com/