using a Named Range (formula) in a UDF

[Sean Maguire]

I am setting up a workbook which will contain hundreds of
calls to a user defined function. The function requires
some column numbers to work, which I originally obtained
via a Match() call against a header row. However, I am
hoping to avoid making Excel run the match code to get the
same column numbers, for each cell that calls the function
when the sheet is recalculated.

Hence, I thought I could set up a named range which
contains the match formula, and then reference the named
range within the formula to get the column numbers. The
formula takes a range also, and I would not have to worry
about Excel failing to recalculate when needed because the
named range would never change alone.

So far, however, I have found no way to get the number
from the named range/formula without using the evaluate
function on the Value or RefersTo property of the named
range. I believe this completely defeats the purpose,
since it evaluates the code rather than just taking the
result, as would a reference to the range in a cell.

So, is there any way to call a Named Range which is really
a function from within a UDF without evaluating it
separately? Or is there some other way to accomplish what
I am trying to do without either hard-coding the column
numbers or adding arguments to my UDF? Perhaps a regular
function instead of a named range? Basically I just want
to do whatever is most efficient but still readable.

Thanks,
Sean Maguire
email maguires
domain newschool.edu

 

[Charles Williams]

Hi Sean,

I would just cache the numbers in a module level variant array
Dim vCols as variant ' at module level

then in your UDF something like

if IsEmpty(vCols) then vCols=[NamedRange]

this should evaluate the named range formula the first time the UDF gets
executed but not subsequently



regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm