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?

 

[SusaninHouston]

I'm reluctant to do that for client confidentiality reasons -- But I did just complete a line-by-line comparison of the VBA code in a well-behaved model and the continuous-calculating model and the VBA code in the ThisWorkbook module all 9 code models (except for a few rows that are commented out) is identical. So I don't think it's the VBA --

If it helps at all, after I copied the code out of the misbehaving model and pasted it alongside the code from the well-behaved model, I was working in the 'Comparison' file with the bad one open but minimized in the background, and the Comparison model was continuously flashing Calculate. I could still work in it, it had no macros -- just two columns of pasted VBA code in columns A & C and a formula comparing Col A to Col C in between. But the Calculate continued to flash.

I did just find one thing that stops it -- From the toolbar at the bottom of the screen, clicking on the red X to close the file brings up the 'Save, Don't Save, Cancel' box -- Pressing Cancel to 'never mind' the close process stops the continuous calculation.

 

[bobsan42]

do you have any cells with a UDF?

Next time try to use Application.CalculateFullRebuild in the immediate window to stop the code. See if it works for you. It is not a solution to the problem unfortunately.

 

[SusaninHouston]

Thanks for the suggestions and I do appreciate your sticking with me --

I've got some UDFs in the code modules, but not in cells, but they're not new.

And I tried running the Application.CalculateFullRebuild command as shown below. It spun for a few seconds and I got the "Done" message, but it didn't stop the 'Calculating' flicker. (I ran the macro several times, and the 'spin time' got down to pretty negligible times.

Sub UseCalculateFullRebuild()
Application.CalculateFullRebuild
MsgBox "Done"
End Sub

 

[SusaninHouston]

Wonder if this new tidbit changes anything... I was working on another issue and used the macro recorder to record a bit of code. Now the status bar has a notification I've never seen before saying that "No macros are currently being recorded" (which is true, ok...) But why am I even seeing this notification when I've never seen it before. Makes me wonder if the problem might not be in the Status bar...

 

[bobsan42]

The statusbar is a static object. It only displays messages from running procedures.
Application.statusbar = "" should clear it and leave it displaying Ready.

 

[Joe4]

I encountered an issue something like this few years ago. I don't know if this is what may be going on, but I will pass it along.
If you have a lot of data and a lot of calculations, it might explain what the issue is.

Basically, Excel tracks all your formula dependencies. That way when data is updated, it only needs to re-calculate the formulas dependent on that data, and not every formula in the entire workbook.

However, there is a limit to how may dependencies Excel can track (it may be something like 1 million). After that. Excel cannot track the dependencies, so if there is any data change, it will recalculate EVERY single formula EVERY time! If your workbook keeps growing, it may be that you have now exceeded the formula dependency limit, and this is what is going on.

If that is the case, this is often a sign that you may be using the wrong tool for the project. If you have that many formula dependencies, there is a good chance that what you really have is a Relational Database model, and as such, you should be using a Relational Database program that was built for that kind of stuff, like Microsoft Access or SQL,

 

[SusaninHouston]

Thanks for the suggestion, but I don't think that's it. If it were, wouldn't it be slow to calculate? Because it really isn't. I also have substantially larger models on the same platform that don't misbehave and are also quick to calculate. Plus, the model keeps on working, even though the flashing is ongoing and annoying...

 

[SusaninHouston]

Rebooting my PC stopped the problem, even through some massive calculation tests and a few Saves (the usual trigger). But I thought that "cured" the problem last week before I sent a copy of the updated model to a client, and she experienced the same issue on her PC, so I don't think it's 'gone for good'

Does that imply it has something to do with memory? And does that inspire any new thoughts?

 

[Alex Blakenburg]

I don't know much about this but following on from bobsand42 comment.

or events are enabled whwn they shouldn't be which starts a routine on changing a cell ....

If you can get to the immediate window while it is calculating and copy in

Application.EnableEvents = False

and hit enter. Does that stop it ?

 

[bobsan42]

I would still bet on a naughty UDF burried in a cell somewhere.