MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom array functions with indeterminate number of values


Posted by Yevgen on August 21, 2001 6:46 AM

Hi,


I have a couple of custom-written VBA functions which return variant arrays - ideal for use with formula arrays. The only problem is that I don't know in advance how many values these functions will return (it depends on the arguments). Is there a way to autoexpand/autocontract the area covered by the formula array whenever recalculation occurs?

I thought about using the calculate event, but this will require me to check the formula of the dependent cells, which is cumbersome. Is there a way to determine from within a custom worksheet function which range it covers?

Thanks for your help


Posted by Aladin Akyurek on August 23, 2001 1:38 AM

Yevgen,

Why not use the so-called named dynamic ranges?

Aladin