Add-in changes my default calculation Mode

Question

My default calculation mode is Manual.  However, one of my add-in calculation mode is Automatic so whenever I open a workbook either new or old, my calculation mode is changed to automatic.

Is there a way to stop the add-in from changing my calculation setting? We need to use different calculation mode on different computers. 

Thank you.

Answer 1

U,

re: where/when to alter calculation mode

Not when the add-in is loaded.

A NEW EVALUATION FOLLOW...

The proposed code (1) saves the existing calculation setting, (2) changes the calc mode to manual, (3) code runs, (4) changes the calc mode back to the saved mode.  If the calc mode was manual when the code starts it remains manual when code execution completes.  In that case, nothing is calculated, even if some values were changed (the worksheet would be wrong).

Your best bet may be to just add one line to each appropriate sub or function.

It would be located just before the sub or function exits.

It would look like...

If Application.UserName = "uyentule01" Then Application.Calculation = xlCalculationManual

(requires your actual user name)

That would allow the existing code to automatically calculate, but if the code were run on your machine,

it would switch back to manual calc when the code completes.

Note that not all sub/functions should have that line added.  If you can't make that decision comfortably then don't do it.

Please reconsider and look again at adding the button to the QAT to toggle calculation.

[Edit - added picture below]

I created a calc option for my Extras for XL add-in (xl97 thru xl2010)

'---

Jim Cone

https://goo.gl/IUQUN2 (Dropbox)

Answer 2

Hi Jim,

Where exactly in the add-in must this code be? Should this code be run whenever the add-in is loaded?

Answer 3

u,

re:  calculation mode

If you have access to the add-in's code then you can use code like the following.

If you don't have access to the code then contact the programs author.

  'xlCalculationAutomatic -4105 'Excel controls recalculation.

  'xlCalculationManual -4135     'Calculation is done when the user requests it.

[Edit]... Or you could add the "Calculation Options" button to the Quick Access Toolbar and change

            the calculation status as needed with a couple of clicks.

'---

Sub AddInSubName()

 Dim CalcStatus As Long

 CalcStatus = Application.Calculation

 Application.Calculation = xlCalculationManual

 'CODE

 Application.Calculation = CalcStatus

End Sub

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)