Why Does Custom Function in Name Get Called Twice?

Hi

My VBA code looks like this:

Please Login or Register  to view this content.

My worksheet has two defined names:

TestCell refers to "=$E$7"

TestRef refers to "=TestInfo(TestCell)"

To duplicate the unexpected double-call, enter the following in the VBA immediate pane:

Please Login or Register  to view this content.

as expected, the address of TestCell is printed in the immediate pane once.

However, the msgbox alert is unexpectedly displayed TWICE.

Why?

Note, the alert is displayed twice with or without setting EnableEvents and Calculation-- they make no difference.

Thx.

(also posted here)

It only does it once for me...do you have a worksheet change procedure that is perhaps causing the function to run again?

hrm... no worksheet change procedures.
i'm on 2010, are you?

That UDF is run every time that TestRef is calculated.

If I have two cells with the formula =TestRef, the UDF will run twice if I change E7, triggering a calculation of TestRef.

I see the same thing in 2010. I don't know why, but it is known that UDFs may be calculated more than once per workbook calculation.

A slight mod will show that A1 and TestCell are passed to it.

Please Login or Register  to view this content.

I assume that trying to read the names content causes it to be evaluated twice although it only returns the value once.

This is explained here
Excel first tries to calculate named ranges. The first attempt to calculate TestRef fails because the R parameter is Dirty. TestRef is scheduled for re=calculation after the input parameter has been calculated. The sheet is then calculated and then TestRef is calculated again. So TestInfo is hit twice.

This one is only hit once...

Please Login or Register  to view this content.