Moving cell in Excel 2010 causes function with named range inputs to recalculate - big performance inpact!

[easperhe]

When I move a cell in excel 2010, any function on that same sheet that has a named range as one of it's inputs will get recalculated. This is a big performance impact for me as I have a sheet with a lot of formulaes that have named ranges as inputs. Why is this happening??!! If I replace the named range with a cell name, e.g. A1, the recalculation does NOT occur.

I've read info about how excel recalculates: http://msdn.microsoft.com/en-us/library/bb687891.aspx#Y1595

and I still don't see why this is happeing. And, I've tried adding
Application.Volatile (False)
and this doesn't help. Any suggestions would be greatly appreciated!

Example:
Function etest(etest_var As String)
etest = 1
End Function

In cell A1, I have formula
=etest(etest_var)
Cell A2 is named "etest_vav"

When I move a cell ANY WHERE on the sheet, it seems excel thinks the named range "etest_var" has been modified and therefore recalculates the etest function. If I change cell A1 formula to this
=etest($A$2)
Then moving a cell on the sheet does not cause the recaculation.

 

[easperhe]

ping - anyone have any ideas here?

Thanks!

 

[shg]

If a named range is defined using a volatile function (like OFFSET), then all dependents will be volatile. If that's the case, try defining it with INDEX instead.

 

[easperhe]

Agree that defining a range with a volatile function could cause this behavior. But in my case, I'm not doing that. In my example, the cell A2 is defined directly, i.e. statically, as etest_var. So, a volatile range definition is not the issue here.

 

[shg]

Got it. Yes, the behavior is the same in Excel 2003 and 2007; the UDF recalculates when an unrelated cell is moved (but not when copied), though other non-volatile formulas do not.

 

[shg]

And it's not because it's a UDF; any formula that references a named range recalculates when cells are cut.

 

[easperhe]

shg,

I'm sure this is a dump question. But, how do you know any formula that references a named range is getting recalculated? With a UDF, I can set a breakpoint. But with a regular formula, how do you know it's getting recalculated?

Eric

 

[shg]

I use Charles Wiliams's CalcSeqCountRef function from the workbook VolatileFuncs available to download (as VolatileFuncs.zip) at http://www.decisionmodels.com/calcsecretsi.htm