Continuous Calculation - Won't stop!

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
295
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?
 
Just a wild guess, are there any hard-coded max row numbers or the sort in the code?
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
The time has come for wild guesses and shots in the dark. ;)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
What about system resources. Any change/increase?
What about variables cleaning up. Any code executing BeforeSave? Refreshing any complicated Pivots or PowerQuery?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top