VBA control on worksheet calculations

Question

VBA control on worksheet calculations

Heinrich Morf 0

I woud like to have VBA control over when a work sheet is calculated

0 comments

3 answers

Your answer

Answer 1

Hello @Heinrich Morf ,

Thank you for reaching out.

It is possible to control when a worksheet calculates by adjusting the Excel calculation mode via VBA. To achieve this, it might be beneficial to set the application's overall calculation mode to "Manual". Once that is done, you can use VBA to trigger the calculation only for a specific worksheet exactly when you need it.

Below is a brief example of how you might set this up in your code:

Sub ControlWorksheetCalculation()
    ' 1. Turn off automatic calculation to prevent it from recalculating on every change
    Application.Calculation = xlCalculationManual
    ' ... perform your data entry, formatting, or other VBA tasks here ...
    ' 2. Trigger calculation for a specific worksheet
    ' Replace "Sheet1" with the actual name of your worksheet
    ThisWorkbook.Worksheets("Sheet1").Calculate
    ' (Optional) If you want to calculate all open workbooks instead, you would use:
    ' Application.Calculate
    ' 3. Turn automatic calculation back on when your tasks are complete (if desired)
    Application.Calculation = xlCalculationAutomatic
End Sub

For more detailed context, you can refer to the official Microsoft documentation here:

I hope this informamation is helpful for your scenario. Please let me know if you are trying to implement this in a specific workflow or if you need any further details. If you found my response helpful or informative, I would greatly appreciate it if you could follow this guide for your confirmation.

Thank you.

Taki Ly (WICLOUD CORPORATION) 4,440 Reputation points Microsoft External Staff Moderator

2026-08-13T10:38:25.85+00:00

Hi @Heinrich Morf ,

Have you seen my answer in this post? If you have any question, feel free to reach out. I'm happy to support you.
Taki Ly (WICLOUD CORPORATION) 4,440 Reputation points Microsoft External Staff Moderator

2026-08-17T03:19:47.1066667+00:00

Hi @Heinrich Morf ,

As we haven’t heard back from you for a while, we’ll go ahead and close this thread for now. If you still need assistance later, feel free to reach out.

Thank you for your understanding.

Answer 2

Barry Schwarz 6,106

You can turn automatic calculation off with Application.calculation = xlCalculationManual

You can turn it back on with Application.calculation = xlCalculationAutomatic

You can initiate a calculation while in manual mode with ActiveSheet.Calculate

0 comments

Answer 3

Yes. VBA can control both when Excel recalculates and what gets recalculated.

Key options:

Set calculation mode Excel supports three modes:
- Automatic
- Automatic Except Tables
- Manual
In Manual mode, recalculation happens only when explicitly triggered.

Trigger calculation from VBA Use these methods depending on scope:

   Application.Calculate          ' all open workbooks
   Worksheets(1).Calculate        ' one worksheet
   Worksheets(1).Rows(2).Calculate ' a specific range

Run code after recalculation If logic should run when a sheet finishes recalculating, use the worksheet event:
```
   Private Sub Worksheet_Calculate()
       Columns("A:F").AutoFit
   End Sub
```

Run code when any sheet in a workbook recalculates Use the workbook-level event:

   Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
       With Worksheets(1)
           .Range("A1:A100").Sort Key1:=.Range("A1")
       End With
   End Sub

Application-wide recalculation event To respond after any worksheet recalculates or chart data changes, use Application.SheetCalculate.

Notes:

In Automatic Except Tables, data tables are not recalculated automatically; they are recalculated by pressing F9 or by an equivalent programmatic calculation operation.
For performance tuning, Range.Calculate resolves dependencies within the range, while Range.CalculateRowMajorOrder calculates left-to-right, top-to-bottom and ignores dependencies.
Conditional format evaluation can also be controlled with Worksheet.EnableFormatConditionsCalculation.

A practical pattern is:

switch Excel to Manual calculation,
make VBA changes,
call Worksheet.Calculate or Application.Calculate only when needed.

References: