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 don't know much about this but following on from bobsand42 comment.


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 ?

I've tried that, and no...

So this morning, I opened my misbehaving model - worked fine.
Ran a few macros - worked fine.
Saved it to trigger the status bar flashing -- which triggered the problem

Tried Alex's suggestion to set the EnableEvents to false -- didn't work
Came back here to report back

Went back into the model and it had stopped...

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

Went on a hunt for UDFs per Bob's suggestion and found one I didn't realize I had:

one UDF formula:

=IFERROR(username(),"error")

referring to this function:

Public Function UserName()
UserName = Application.UserName
' UserName = Environ$("UserName")
End Function


In previous years' models, I used the Environ$ version without incident, but switched to the Application version this year. The Application version worked fine for a few weeks, but stripping it out entirely (comment out code, erase formula that used it) then switching back to the Environ$ version seems to have fixed the problem.

It's looked "fixed" before though...

Does the above look like it would have caused the problem?

 

[SusaninHouston]

So just valuing out the =IFERROR(username(),"error") formula appears to stop the problem -- but at a cost of a high-value formula that logs the name of the user making changes to the file.

I switched formulas in the function because the Environ$ version gave me the Windows user name (C:\Users\___) and I wanted the Excel version. I've found a formula that gets me the Windows username and can use it, but does anyone know of a formula that can get the Excel version?

 

[bobsan42]

It doesn't seem like much of a trouble maker...
I had a somewhat similar problem involving a username UDF. But only if it was used as a function in a cell. Is it possible using Application object methods for cell function to be a problem?

 

[SusaninHouston]

Seems I spoke too soon -- The file was behaving, but now it's misbehaving again. The whole UDF function is commented out and the cell with the formula has been erased.
Still misbehaving. The 'Save' command is the trigger - I'm going to try two things --

If the problem is in the VBA, disabling macros ought to solve it, right? Or maybe just save as .xls instead
If the problem is in the formulas, valuing them out should resolve it.

Neither of these tests will yield an acceptable solution - just trying to ID source of the problem.

 

[SusaninHouston]

Well, I tried stripping out the macros and the file didn't misbehave...
Then I went to the misbehaving copy, kept all the macros, and valued out the formulas, and it also didn't misbehave.

I tried various odds and ends -- each of which worked for a while, but then stopped working.

Removed the UDF to get UserName
Application.StatusBar = ""
Application.DisplayStatusBar = False

So I guess I'm back to it's either the macros, the formulas, or the inter-action between the macros and formulas.

If it was just my PC, I'd reinstall Excel. but it's not...

 

[Alex Blakenburg]

Just reading up on circular references. I know you need it on but does turning off iterative calculations stop the issue ?

 

[SusaninHouston]

Thanks for the suggestion Alex --

Turning it off did stop the 'Calculate' from flashing on the status bar, but resulted in a circular calculation warning every few seconds. Then the status bar started flashing again but without the 'Calculate' on the left flashing with it.

 

[Alex Blakenburg]

Apparently enabling iterative calculations, effectively stops the error checking on circular references for new formulas as well.
If turning it off helps but it was working before with it on, is there any chance that you now have more circular references than before (ie unintended ones).
Can you compare it to an old copy of the model. For that matter does an old copy have the same issue ?

 

[SusaninHouston]

Other models with the exact same VBA code are fine, as are other versions of this model with the same code.

I started from a misbehaving model and undid all of my recent changes, working backwards until it started behaving again, then adding them back one by one. For the misbehaving model, commenting out this bit of code seems to make a lasting improvement . The code is stored in the sheet model and runs a macro if the user changes a 'rows to show' setting in one of several cells formatted in the "Editable" style.

Several other models use this same exact code without problems and the 'SH_Row_button' macro has been in wide use for several years, including from this model via a button. The only thing new is calling the macro based on the Worksheet_Change(ByVal Target argument --

Can anyone see anything wrong with this code snippet?

Option Compare Text
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Offset(-1, 0).Style = "Editable" Then
Call SH_Row_button
End If
End Sub

 

[Gokhan Aycan]

Why don't you only call the macro if target match one of those editable cells, if they are few?

Dim rngEditable as Range

Set rngEditable = Union(Range(),Range(),etc.)

If Not (Intersect(Target,rngEditable) is Nothing) Then
    Call SH_Row_Button
End If