Search code examples
vbaexcelexcel-2010

Application.Calculation depending on workbook

I'm managing a workbook with more than 200 000 formulas (some really complicated array formulas) which means that I can't let Excel automatically calculate all the cells every time I click somewhere (it takes around 8 hours to calculate everything).

Instead of that, the calculation is set to manual and I have the following VBA code executed when Calculation.xlsm is opened:

With Application
    .CalculateBeforeSave = False
    .Calculation = xlCalculationManual
End With

I use custom buttons to calculate only some parts of the 200k cells when needed.

I noticed that Excel does keep track of that setting in each workbooks, which means that if I open my Calculation.xlsm, Excel remembers that the calculation is set to manual. If I open my Values.xlsx, Excel does remember that the calculation is set to automatic. This was before I tried to copy values from Calculation.xlsm to Values.xlsx.

Now, because I'm using VBA in Calculation.xlsm to copy values to Values.xlsx, Excel does apply the Application.Calculation setting to that workbook too, which means that if I open it with a new instance of Excel, the calculation will still be set to manually.

If I add a Application.Calculation = xlCalculationAutomatic before closing the Values.xlsx with VBA in my Calculation.xlsm workbook, it will work, but Excel will also start to compute the 200k cells in my Calculation.xlsm workbook, which I obviously don't want.

So my question is about how to actually set the calculation of Excel based on a specific workbook instead of with the Application object. This is based on the fact that Excel does keep track of that setting depending on which workbook is opened (you can just do the test and create 2 different .xlsx files, one with the calculation enabled and the other with the calculation disabled and Excel will remember these settings).

I know I could use the Worksheets.Range.Calculate method to calculate my Values.xlsx workbook before closing it, but the calculation will still be set to manual if I open it in a new instance of Excel after that.

EDIT 3:20pm: Not sure if I was clear enough, English isn't my native language. In short, I have Calculation.xlsm with VBA and Calculation set to manual. I have Values.xlsx with no VBA and Calculation set to automatic. If I open Values.xlsx with the following VBA code in Calculation.xlsm, Excel will automatically convert my Values.xlsx workbook to manual calculations.

Calculation.xlsm code:

Private Sub Workbook_Open()
    With Application
        .CalculateBeforeSave = False
        .Calculation = xlCalculationManual
    End With
End Sub

Sub someFunction()
    Set WB = Application.Workbooks.Open("Values.xlsx")
    Set WBws = WB.Sheets("mySheet")
    DoEvents
    wb.Save
    WB.Close
End Sub

After the execution of someFunction(), Values.xlsx calculation is set to manual. That's the problem. I would like it to stay on automatic (and I can't add VBA to that file, it must be all done from Calculation.xlsm like above).

EDIT 3:40pm: Could I just have my big workbook with Application.Calculation set to manual, put all the data I need in the clipboard (I only need the values, not the formulas), close it (will the VBA still continue to execute even if I close the workbook from which it is executed?), set Application.Calculation to Auto (since there is no open workbook), then open the destination workbook to paste the values (will Excel still keep the data in the clipboard since the other workbook is closed?), then save and close that workbook, set back the calculation to manual (no workbook opened) and reopen the original workbook from which the code was executed?

Solution

  • One way to do this would be to create a new instance of Excel. While this is probably slower, and might be more difficult to work with in cases where you don't close the book/application within the function, but for simple case like your example, it may be easiest to implement:

    Sub someFunction()
Dim newExcel as Excel.Application
Set newExcel = CreateObject("Excel.Application")
    
    Set WB = newExcel.Workbooks.Open("Values.xlsx")
    Set WBws = WB.Sheets("mySheet")
    DoEvents
    wb.Save
    WB.Close
    newExcel.Quit
    Set newExcel = Nothing
End Sub

    The Application.Calculation property is relative to that instance of the application, not other instances.

    Alternatively, you can use an application-level event handler. I suspect this might be faster but I have not tested it for speed.

    Modified slightly from this very similar question (which also asks about conditionally disabling an Application-level property).

    If:

    I was just worrying about if the code would still be executed if I close the workbook from which it is launched

    Then just use the normal Workbook_BeforeClose event handler to restore the desired Application.Calculation property (for the entire application/all other open workbooks).

    The rest of the answer:

    Create an application-level event handler, create a class module named cEventClass and put this code in it:

    Public WithEvents appevent As Application
Dim ret
Private Sub appevent_WorkbookActivate(ByVal wb As Workbook)

    Call ToggleCalculation(wb, ret)
    
End Sub

    Use the following in a standard module named mod_Caclulate:

    Option Explicit
Public XLEvents As New cEventClass
Sub SetEventHandler()

If XLEvents.appevent Is Nothing Then
    Set XLEvents.appevent = Application
End If

End Sub

Sub ToggleCalculation(wb As Workbook, Optional ret)
    If wb.Name = ThisWorkbook.Name Then
        ret = xlCalculationManual
    Else
        ret = xlCalculationAutomatic
    End If
    Application.Calculation = ret
End Sub

    Put this in the Workbook_Open event handler of the workbook which you always want to be manual calculation:

    Option Explicit
Private Sub Workbook_Open()
    'Create the event handler when the workbook opens
    Call mod_Caclulate.SetEventHandler
    Call mod_Caclulate.ToggleCalculation(Me)

End Sub

    This will create the event handler only when the specific workbook is opened, and the handler will toggle the Calculation property whenever you switch views to a different workbook.

    Note: If you "end" run-time or do anything while debugging which would cause state loss, you will lose the event handler. This can always be restored by calling the Workbook_Open procedure, so an additional safeguard might be to add this also in the ThisWorkbook code module:

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' Additional safeguard in case state loss has killed the event handler:
' use some workbook-level events to re-instantiate the event handler

    Call Workbook_Open
End Sub