CODEWITHSUNDEEP
vbaexcel
Ramon
Ramon

Reputation: 35

VBA - turn on auto calculation AFTER Workbook is closed

I have an excel file that automatically disables autocalculation when starting. It's a huge file with lots of rows of data and formulas. I usually filter for what I need and then calculate only the filtered data.

So far so good. When closing the Workbook it automatically enables automatic calculation again, so that it's active when working i different workbooks.

I use these Workbook events to do so

Private Sub Workbook_Open()

Application.Calculation = xlCalculateManual
Application.CalculateBeforeSave = False

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.Calculation = xlAutomatic


End Sub

The problem is, that as soon as I hit the X button to close the workbook the Workbook_BeforeClose event starts and the whole file starts to calculate. It takes up to five minutes and only after the calculation is finished, I can save and close the workbook.

I would like to have automatic calculation reactivated AFTER the Workbook is closed, so that excel does not calculate the whole Workbook any longer but that autocalculation is enabled again in any other workbook.

Is there a way to do so?

Upvotes: 0

Views: 7114

Answers (3)

CoolMagma
CoolMagma

Reputation: 55

You just use the wrong value, is not xlAutomatic it's just xlCalculationAutomatic

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.Calculation = XlCalculation.xlCalculationAutomatic
    End Sub

Upvotes: 0

Brandon Barney
Brandon Barney

Reputation: 2392

Note: Skip to the second solution. After reading the comments, the below approach will work but it is less than ideal. The second approach is more efficient and effective given the task. I am keeping the original solution here solely to keep edits and updates apparent.

Initial Solution

I think I have a solution for you (and in the process I learned something new).

While EnableEvents belongs to the Application object, EnableCalculation belongs to the worksheet object. This allows us to be very precise in which sheets we want to calculate.

Option Explicit
Public AppCalcSetting As Long
Public AppEventSetting As Long

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Remember the application events setting
    AppEventSetting = Application.EnableEvents

    ' Turn off Calculation for the specified worksheet
    ThisWorkbook.Sheets(1).EnableCalculation = False

    ' Prevent other events from firing
    Application.EnableEvents = False

    ' Turn Application level calculation back to what it was previously set to
    Application.Calculation = AppCalcSetting

    ' Restore EnableEvents
    Application.EnableEvents = AppEventSetting

    ' Turn Calculation ability back on
    ThisWorkbook.Sheets(1).EnableCalculation = True
End Sub
Private Sub Workbook_Open()
    ' Remembers user preferences for calculation mode.
    AppCalcSetting = Application.Calculation

    ' Turn off Automatic Calculation
    Application.Calculation = xlCalculationManual
End Sub

I ran a few tests and I was successfully able to suppress a worksheet from calculating, while still changing the application.calculation mode.

Keep in mind that while Worksheet.Calculation mode is set to false the worksheet cannot be calculated, so this option must be restored if the user intends to calculate new values.

Second Solution

Per A.S.H here's another approach that avoids the application setting entirely (note that this only performs on the first sheet and this would need to be edited accordingly):

Public Sub Calculate()

    With ThisWorkbook.Sheets(1)
        .EnableCalculation = True
        .Calculate
        .EnableCalculation = False
    End With
End Sub

You can bind this to a hotkey or a button. Just make sure you also have this event in the workbook:

Private Sub Workbook_Open()
    ThisWorkbook.Sheets(1).EnableCalculation = False
End Sub

I havent tested how this would work on filtered data, but it will solve the calculation problem without needing to worry about any application-level events.

Upvotes: 2

Charles Williams
Charles Williams

Reputation: 23520

One somewhat hacky solution could be to run a sub to set calculation back to automatic on a time delay so that it runs after the workbook has finished closing.

Really what is needed is a way to set calculation mode for individual workbooks: see my suggestion on Uservoice

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10601079-workbook-level-calculation

Upvotes: 0

Related Questions