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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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?
 
Last edited:

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
291
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Active Member
Joined
Jan 13, 2005
Messages
291

ADVERTISEMENT

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-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Active Member
Joined
Jan 13, 2005
Messages
291

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Sharing your wb can help speed things up. Actually without it I don't know how i can help.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Upload to some cloud storage and share a link.
 

Forum statistics

Threads
1,147,568
Messages
5,741,874
Members
423,692
Latest member
Bhanu1988

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