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?

 

[bobsan42]

Check again. Maybe something turns on autocalc when not needed, or events are enabled whwn they shouldn't be which starts a routine on changing a cell ....
Many things can go wrong.
Since you have the chance to stop the code, debug it step by step (F8) and see why it restarts or why it doesn't end when it should. Narrow it down to the looping procedure(s) and try to figure out the problem.
Also comment out any statements like On error resume next.
It's a pure tedious debug task.
Also remove jic any addins, esp. new ones
Does this happen on other machines as well?

 

[SusaninHouston]

Yes, I'm afraid it does happen on other machines...

And for what it's worth, I don't think it's the VBA code because virtually identical code appears in models that don't misbehave like this.

 

[bobsan42]

Then it maybe something with the data.
If you are more than a 100 % there are no code differences. May be a small hidden problem somewhere. Still - debugging. Or transfer all the data in batches to a new workbook and see if/when the problem reappears. There must be a pettern if it is not a damaged workbook.

 

[SusaninHouston]

I should clarify -- The CODE ends before the problem starts, so it's not that the macro keeps looping. Also, I WANT AutoCalc turned on. I just don't want it running all the time when the user doesn't change anything.

 

[bobsan42]

Well, no chance of helping imho unless someone puts their hands on it to investigate first hand.
You say the code ends, but before you said pressing esc stops it. It can't be both ways.

 

[SusaninHouston]

Pressing Esc interrupts the continuous calculation long enough to switch it over to manual calc. The macros complete their various missions before the continuous-calc problem starts

 

[bobsan42]

Sharing your wb can help speed things up. Actually without it I don't know how i can help.

 

[SusaninHouston]

Is there a way to share a 9 mb file here, then remove it alter?

 

[bobsan42]

Upload to some cloud storage and share a link.