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]

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 ...

 

[SusaninHouston]

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.

 

[bobsan42]

Share the file. Or even better - two versions of it

 

[SusaninHouston]

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

 

[bobsan42]

Sleep on it and check tomorrow ?
?

 

[SusaninHouston]

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. --

 

[SusaninHouston]

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?

 

[bobsan42]

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.

 

[Alex Blakenburg]

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)

 

[SusaninHouston]

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.