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

TomCon

Active Member
Joined
Mar 31, 2011
Messages
309
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 31, 2011
Messages
309
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,138
Messages
5,835,619
Members
430,371
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top