Do you see a problem with non-Volitile UDF (default) executing when they don't need to?

[TomCon]

My UDFs are all non-volatile (as i understand it, the default. They do NOT contain the statement Application.Volatile)

But, they seem to be executing when there is no need to, none of the input arguments are changing. I can see this because if the VBA window is open, it is constantly flashing in the title bar as though a function is being executed. For instance i just typed some text into a cell on which no cell in the workbook is dependent, and it seems that the UDFs are being called, as the title bar in VBA is flashing as though VBA is executing. No breakpoints are set anywhere in any module.

This is a problem for me as my WB is ~115MB and there are 100,000s of thousands of calls to a UDF. Excel is essentially frozen for many minutes (going on 10 now). I can't get the focus on the VBA window to close it (sometimes i can manage to get focus on it to close it and sometimes not). If the VBA window is closed, time is not an issue, it takes just seconds. But with it open, it seems to me (my guess) that the title bar is being updated for every call to a UDF, and with 100,000+ it takes a really long time.

Further evidence for UDFs being called when they do not need to be is when i am working on a new UDF and debugging it, the call to this particular UDF exists in only one single cell in the Workbook, and that cell is not within the range that any other UDF is dependent on. And yet the same phenomena occurs, that the VBA window title bar is flashing as though it is executing code many many times.

Questions.
1. Do you find that non-volatile VBA UDFs are being called when they do not need to be?
2. If so is there any way to prevent that?
3. Any other suggestions on how to avoid this situation?

 

[Jeffrey Mahoney]

I see my UDFs getting called every time a workbook or worksheet calculation occurs. I struggle with the same thing.
1. Find a way to build a cell formula to replace it.
2. Use a macro to put the result from the UDF into the cell at the times you want.
3. Add global variables that count the number of times the UDF gets called and only run it on an interval.

 

[TomCon]

Thanks, good to know i'm not the only person experiencing this. I presume MSFT is aware...and no fix has ever been hinted at.

Idea #2 may be the way i go. Its cumbersome and unsatisfying, but it may be better than current behavior. At least when code is under development. Once you consider it "final" or "nearly final and not changing code much" (don't have VBA window open) maybe you could change back to UDFs. It seems like with #2 you have to "make yourself the Excel recalc engine" and make sure you run that macro every time a value changes that could affect the result.

Thanks...if any other comments or ideas out there please post!