4

I have a excel function defined in a module which is called a total of two times as a worksheet functon. However when I do a shift+f9 and place a breakpoint on the function, i noticed that its getting called more than twice, around 5 times. This function however is not being called anywhere else, both programmatically or in any worksheet except for the two cells i mentioned above.

Furthermore, when I look at the call stack when execution hits my function, it shows just my function being the top level call. Any help regarding what may be causing this? Thanks.

HelpThis
  • 61
  • 2
  • 4
  • Check out `Volatile` method in the help section. For any UDF the default is `TRUE` meaning that a function will get called whenever **any** calculation occurs on the same worksheet. Maybe this is why it is firing 5 times as you are perfoming other calculations on the sheet? – Alex P May 08 '11 at 20:15
  • Hi Remnant, unfortunately my function has not been marked as volatile. – HelpThis May 08 '11 at 20:19
  • Maybe try `Application.Volatile (False)`? – Alex P May 08 '11 at 20:23
  • Are you referencing each cell containing the UDF within the code? You could be creating a cycle of UDF updates based on the previous update –  May 08 '11 at 21:37

2 Answers2

3

There are many possible reasons for this but probably the most likely is the UDF being called with uncalculated cells:
see http://www.decisionmodels.com/calcsecretsj.htm for details

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Hi Charles, indeed I have been reading that site you referenced, it is indeed quite a treasure trove you have there :) – HelpThis May 08 '11 at 22:06
0

Possibly it could be position related. I was experiencing same thing with my UDF

The source range was below my udf cell. So Excel was calculating first the udf (with a few empty values from the uncalculated source range) and then again when the range was calculated and propagated to dependant cells (now with right values).

Madson Germano
  • 41
  • 5