Continuous Calculation - Won't stop!

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
291
I've got a large model (~10 mb) that's been around for years with minor annual updates. A few days ago it began acting strangely after minor changes (inserted rows) and I'm hoping someone here can help. The model contains numerous macros, most of which begin and end with standard code blocks that turn automatic calculation on and off. Those code blocks haven't changed for years, and the problem is brand new. I do have iterative calculations enabled and there are a few necessary and intentional circular calculations that have been there forever.

What's happening is that -- after a clean start -- the model starts this continuous-calculation routine. It just keeps on calculating, even when nothing is happening. The model works fine when it opens (in AutoCalc mode) and usually through a few macros that turn AutoCalc off and on again. But at some point - not every time - it starts continuously calculating. Most often - but not always and not exclusively - this continuous-calculation behavior starts after I save the workbook. Sometimes I can do a lot of work before it starts; other times not. But once the continuous-calculation issue starts, it's persistent. Pressing the Esc key does interrupt the calculation, but it starts right back up again unless I switch to Manual Calc. Manual calc mode works fine until I run any of the macros, which then ends by re-enabling AutoCalc... I've been through each worksheet in the model and run error-checking to no avail.

I'm using Excel 2019 on a Windows 10 PC.

Ideas?
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Just a wild guess, are there any hard-coded max row numbers or the sort in the code?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
The time has come for wild guesses and shots in the dark. ;)
 

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
291

ADVERTISEMENT

The time has come for wild guesses and shots in the dark. ;)

You've got that right!

The part that has me most confused is that the problem seems to be "cumulative" -- meaning that it appears to work well up until some 'tipping point' after which it goes into 'blinky' mode and pretty much stays that way. When a file goes 'blinky' for the first time, I am generally able to put it into ManualCalc mode and keep working - running macros even - and occasionally even go back to AutoCalc until the next Save, at which point the file goes 'blinky' again. It could be just my frustration coloring my perceptions (I don't think so), but it seems the first 'blinky' is tougher to trigger, with subsequent problems getting getting easier to trigger and harder to recover from.

Does that suggest anything?

My latest attempts were hard-coding out some formulas that didn't need to be OFFSETs and stripping out most of my conditional formatting. There are very few volatiles, but a lot of CHOOSE and INDEX formulas - more than previous years.
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Are there anything in the call stack while this is happening? Any macro's at all? What if you click "Reset" in VBA editor when this happens? Or "End" in Immediate window.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

What about system resources. Any change/increase?
What about variables cleaning up. Any code executing BeforeSave? Refreshing any complicated Pivots or PowerQuery?
 

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
291
What about system resources. Any change/increase?

In Task Manager, when 'the blinkys' happen, CPU resource use jumps from <1-5% up to 50-52% and stays there.

What about variables cleaning up. Any code executing BeforeSave? Refreshing any complicated Pivots or PowerQuery?

No pivots or power queries in here. There is BeforeSave and AfterSave code, though it hasn't changed in several years

What if you click "Reset" in VBA editor when this happens? Or "End" in Immediate window.

That does stop the blinking. Is there a VBA command equivalent to that I could put in a user-triggered "Reset" button?
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
You can try this then. Next time instead of "reset", try "Break" (pause button) and then check the call stack (it is under View menu in VBA editor). See whatever is in the list, if there is anything that is.

As for your question: "End" is the VBA command, just like "Stop" is break. Mind you, when you End the code, any global variables will be losing their values.
 

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
291
Could this be it? Acts like it...

In the Auto_Open menu I added a new subroutine to load the startup position for the model's various forms. I didn't actually load the forms, so didn't think it really mattered, but commenting out the 'Call FormLocations' from Auto_Open seems to make a difference and reactivating the command restarts the problem

Sub FormLocations()
' New for 2022 - Sets form display locations at center
On Error Resume Next
frmBudget.startupposition = 3
frmChangeFBRev.startupposition = 3
frmChangeLaborRatios.startupposition = 3
frmChangeRatios.startupposition = 3
frmDailyorMonthly.startupposition = 3
frmEXorPDF.startupposition = 3
frmFV.startupposition = 3
frmGoTo.startupposition = 3
frmGraph.startupposition = 3
FrmImport.startupposition = 3
frmNudge.startupposition = 3
frmPreLoad.startupposition = 3
frmPrintMenuPL.startupposition = 3
frmQI.startupposition = 3
frmRowHeight.startupposition = 3
frmSH_Details.startupposition = 3
frmTech.startupposition = 3
frmTinyTip.startupposition = 3
End Sub
 

Forum statistics

Threads
1,147,571
Messages
5,741,887
Members
423,693
Latest member
Excelquestion35

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