Why Does Custom Function in Name Get Called Twice?

[johnywhy]

Hi

My VBA code looks like this:

Function TestInfo(R As Range) As String    
    MsgBox "alert"    
    TestInfo = R.Address
End Function

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:

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
?[TestRef]

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)