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?
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

SusaninHouston

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

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

SusaninHouston

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

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

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

Forum statistics

Threads
1,147,566
Messages
5,741,864
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