Menu
I have a rather large workbook, and some users prefer to have automatic calculation on, while others prefer having it off.
Some macros that interact with the sheets are attatched to the workbook. To speed up my VBA code, I set
Application.Calculation = xlManual
In the beginning of each workbook.
My question is, how do I revert it back to the way it was in the end of macro?
Right now i'm using
Application.Calculation = xlAutomatic
but that overrides the users choice. It's pretty annoying having to change it back to manual every time the macro has run. (Or the other way around, if leaving it at manual.)
485
Open the Visual Basic editor (Developer > Visual Basic). Double-click on Sheet1 and paste the code above, i.e. the full code of “Public Sub ForceFullCalculation()”. In the “Assign Macro” dialog, create a new click event handler, or select the method “Sheet1. ForceFullCalculation”.
Calculate Worksheet Method in VBA is used to calculate all open workbooks or a specific worksheet in a workbook or a specified range in a worksheet.
To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by pressing F9), in the Calculation options section, under Workbook Calculation, click Manual. Note: When you click Manual, Excel automatically selects the Recalculate workbook before saving check box.
Store the setting before starting, then restore when finished:
Sub calctest()
Dim calcsetting As Integer
calcsetting = Application.Calculation
' Put code here
Application.Calculation = calcsetting
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
