Continuous Calculation - Won't stop!

[SusaninHouston]

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]

Just a wild guess, are there any hard-coded max row numbers or the sort in the code?

 

[SusaninHouston]

Just a wild guess, are there any hard-coded max row numbers or the sort in the code?

There are some hard-coded row numbers, though not many. And not MAX row numbers since that can vary. How would that be problematic?

 

[Gokhan Aycan]

It may be, not has to be

 

[bobsan42]

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

 

[SusaninHouston]

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]

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]

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

 

[SusaninHouston]

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]

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]

Could this be it? Acts like it...

In the Autpen 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 Autpen 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