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?

 

[Gokhan Aycan]

Not sure if it would matter here but when you On Error Resume Next, either return back to your error handler or On Error GoTo 0 after the code piece where it is needed.

You might be trying to set those properties before Excel is fully loaded btw. Possibly causing unwanted effects. You could, if needed, use Application.OnTime with a few seconds to the above sub.

 

[SusaninHouston]

Not sure if it would matter here but when you On Error Resume Next, either return back to your error handler or On Error GoTo 0 after the code piece where it is needed.

You might be trying to set those properties before Excel is fully loaded btw. Possibly causing unwanted effects. You could, if needed, use Application.OnTime with a few seconds to the above sub.

The FormLocation code is run only the one time when the model is first opened and there's an On Error GoTo 0 shortly afterward in the AutoOpen -- But your point about setting properties before fully loaded sounds possible. I'm wondering if somehow my Before- or After-Save code might be triggering it even though it's not explicitly called. The way the performance measures jump in big blocks on the Task Manager screen is consistent with something like that. Maybe the forms somehow get loaded into memory and retained...?

 

[bobsan42]

Maybe the forms somehow get loaded into memory and retained...?

This is true: once you refer to a form object or its property (frmBudget.startupposition = 3) - the object is then loaded until you unload it or reset your project (State loss - At this point ALL variables are reset and any values lost)

 

[SusaninHouston]

This seems to be it - for real this time! It makes sense technically and timeline-wise, and is even an easy fix.
Thank you so much for sticking with me through this!

Guess the useful takeaway is to not load a bunch of forms at once or you'll risk getting some really nasty and confusing problems --

 

[bobsan42]

This seems to be it - for real this time! It makes sense technically and timeline-wise, and is even an easy fix.
Thank you so much for sticking with me through this!

Guess the useful takeaway is to not load a bunch of forms at once or you'll risk getting some really nasty and confusing problems --

Wow, really?
I'll keep my fingers crossed for a while JIC

 

[SusaninHouston]

Just a final update -- So I had some models that loaded a bunch of user forms in the AutoOpen that were still working fine before I encountered this problem in another very similar model for another client. (Same VBA code for both - that's how similar) Anyway, that client's models all worked fine before, but are now starting to act up here and there.

And the same 'fix' (moving that load-forms code out of AutoOpen) now fixes them -- so that was definitely it!