Application. Book Costing

Question

I run a book with over 200,000 formulas (some very complex array formulas), which means that I cannot let Excel automatically calculate all cells every time I click somewhere (it takes about 8 hours to calculate everything).

Instead, the calculation is set to manual mode, and when you run Calculation.xlsm, the following VBA code is executed:

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

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

I noticed that Excel tracks this setting in every workbook, which means that if I open my Calculation.xlsm, Excel remembers that the calculation is set to manual. If I open my Values.xlsx file, Excel remembers that the calculation is set to automatic. This was before I tried to copy the values ​​from Calculation.xlsm to Values.xlsx.

Now, since I use VBA in Calculation.xlsm to copy values ​​to Values.xlsx, Excel also applies the parameter Application.Calculationto this workbook , which means that if I open it using a new Excel instance, the calculation will still be set manually.

If I add Application.Calculation = xlCalculationAutomaticValues.xlsx with VBA before closing Calculation.xlsm in my workbook, it will work, but Excel will also start calculating 200k cells in my Calculation.xlsm workbook, which I obviously don’t want.

, , Excel , Application. , Excel , ( 2 .xlsx, , , Excel ).

, Worksheets.Range.Calculate Values.xlsx , - , Excel .

---

3:20 : , , . , Calculation.xlsm VBA Calculation, . Values.xlsx VBA, Calculation - . Values.xlsx VBA Calculation.xlsm, Excel Values.xlsx .

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

someFunction() Values.xlsx . . , ( VBA , Calculation.xlsm, ).

---

3:40 . Application.Calculation , , ( , ), ( VBA , , ?), Application.Calculation Auto ( ), , ( Excel , ?), , ( ) , ?

Answer 1

- Excel. , , , , / , , , :

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

Application.Calculation , .

, . , , .

( APplication).

:

, , ,

Workbook_BeforeClose Application.Calculation ( / ).

:

, cEventClass :

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

    Call ToggleCalculation(wb, ret)

End Sub

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

Workbook_Open , :

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

, Calculation, .

. "" - , , . , Workbook_Open, ThisWorkbook:

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