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?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
It would be interesting to see SH_Row_button code.
Potential problems with this sub:
- Target and Active cell are not necessarily the same thing
- changing multiple cells in a sequence by code without disabling application events may cause multiple code calls that may seem like a loop
- just for the sake of making things properly you should rule out row 1 - you probably don't expect editable cells there, but even so ...
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
295
RATS! -- It started misbehaving again ( with the code commented out)
I don't get it... I went through about 10 Saves (plus misc. macros) trying to get it to misbehave - finally relaxed a bit, closed the model. Had lunch. Re-opened, and now it's bad again.
 
Upvote 0

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Share the file. Or even better - two versions of it
 
Upvote 0

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
295
Share the file. Or even better - two versions of it

If it were my data in the model, I certainly would. But the information contained isn't mine and I've signed a confidentiality agreement with my client... Taking the data out changes so many things it might disguise the original problem or cause new ones...

I did find one other thing that might be it, and that's an invalid (but not visibly so) value in my "sheetlist" table that might cause macros to run multiple times to clear errors that appear each time the "sheetlist" is accessed (often)... I've fixed the sheetlist and so far, so good... It feels sorta logical with what I'm seeing, and best of all, is easy to cure.

Fingers crossed --
 
Upvote 0

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Sleep on it and check tomorrow ?
?
 
Upvote 0

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
295
Sleep on it and check tomorrow ?
?

You've been so helpful -- THANK YOU! --

So far, commenting out the sheet-level code and fixing the sheetlist have stabilized things longer than the other fixes, and the sheetlist issue could explain why other models with the same code aren't misbehaving. --
 
Upvote 0

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
295
This has to mean something... I went into the model that was behaving well after my last post and put it through it's paces three or four times. All good. Then NOT! and not it's bad every time. I didn't even change any data...

So it acts like something *cumulative* in the file that builds up. Does that trigger any ideas?
 
Upvote 0

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Have you tried moving all data and code to a new wb. But only raw data, not moving sheets, modules etc. Or to another healthy model - just the data if the code is identical.
 
Upvote 0

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,125
Office Version
  1. 365
Platform
  1. Windows
This has to mean something... I went into the model that was behaving well after my last post and put it through it's paces three or four times. All good. Then NOT! and not it's bad every time. I didn't even change any data...

So it acts like something *cumulative* in the file that builds up. Does that trigger any ideas?

Did you go into each sheet each time ? (ie is one particular sheet the trigger)
Perhaps even delete 1 sheet at a time and see if once you delete a specific sheet the problem goes away.
Or convert each sheet in turn to values only.
(the 1st will check the sheet and change event, the 2nd will just check calculations)
 
Upvote 0

SusaninHouston

Active Member
Joined
Jan 13, 2005
Messages
295
Thanks for the suggestion Alex -- I've done exactly that, and the problem seems to be fixed for a while, but then it re-appears. THAT's the part that's not making sense to me.
 
Upvote 0

Forum statistics

Threads
1,186,302
Messages
5,957,106
Members
438,287
Latest member
JDDrcar

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