Intermittent error for Application.Calculation

[Dr. Demento]

I like the idea of using a optimization sub instead of re-typing all the off/on switches for calculation, screen updating, etc (see Procedure for increasing VBA performance).

It usually works great, except that every once in a while I get an error on this line:

Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)

[RTE 1004: Method "Calculation of Object" _Application failed] I have this code in Personal.xlsb, ThisWorkBook, in a Private Sub Workbook_Open macro so that all of these items get turned on when I first open Excel and in Private Sub Workbook_BeforeSave to turn them off and then on surrounding a Save of personal.xlsb.

I 'm trying to replicate the error message but it's working fine right now (like a car at the mechanics!!), although it seems most prevalent when I open Excel for the first time. Any thoughts why that would throw an error??

 

[svendiamond]

Maybe when you first open the file, the "isOn" variable hasn't been defined yet?

 

[Dr. Demento]

Thanks for the thought. It got me thinking that perhaps it's because the sub is in an independent module; I copied it to ThisWorkbook and made it Private. Perhaps that will "fix" it.

Following your logic, I would think that at the first time where IsOn is used, an error would arise. Additionally, it's a passed variable so I would think that that would be definition enough

My original thought was that it was something to do with the IIf statement being used to assign an Application variable, but that's just a wild guess.

Again, thanks for the suggestion.

 

[svendiamond]

Ah, that could be it. Could try changing it to:

If isOn Then
     Application.Calculation = xlCalculationManual
Else
     Application.Calculation = xlCalculationAutomatic
End If

 

[svendiamond]

Just read up on the IIF function and it seems:

"IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True."

So your code may be switching the automatic calculation off, then on, then back off again.

 

[Dr. Demento]

Good thought, but now I'm getting RTE 13, Type mismatch. For some reason, when the workbook is first opened, it has a cow and throws an error. Once the application is fully running (including past link update msgbox), I can hit F5/F8 and it completes just fine.

It hangs up on the Application.Calculation = xlCalculationAutomatic; when I start the workbook, the calculation is already set to automatic and it hiccups when evaluating an already set flag (although why it should hang up on calculation and not any of the others is beyond me).

I thought about moving this function to a _BeforeClose event, but that won't help if the last thing that happens is I have to abruptly quit and the calculation is left at manual. It would be cool if there was an application event that would fire once after the startup is complete.

I guess I could always suppress the error using On Error Resume Next at the beginning and On Error Goto 0 at the end of the event.

Thanks again for your suggestions.

 

[svendiamond]

It hangs up on the Application.Calculation = xlCalculationAutomatic; when I start the workbook, the calculation is already set to automatic and it hiccups

If it's already automatic by default, and you only want it to change to manual if your variable "isOn" is true, upon opening the workbook, you could just get rid of that part:

If isOn Then
     Application.Calculation = xlCalculationManual
End If

Good luck and your error handling should probably help.