VBA control on worksheet calculations

Heinrich Morf 0 Reputation points
2026-08-09T11:35:45.31+00:00

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

Developer technologies | Visual Basic for Applications
0 comments

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-08-09T11:36:06.84+00:00

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

    Key options:

    1. Set calculation mode Excel supports three modes:
      • Automatic
      • Automatic Except Tables
      • Manual
      In Manual mode, recalculation happens only when explicitly triggered.
    2. 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
    3. 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
    4. 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
    5. 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:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Barry Schwarz 6,106 Reputation points
    2026-08-09T15:19:20.96+00:00

    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

    Was this answer helpful?

    0 comments
  3. Taki Ly (WICLOUD CORPORATION) 4,440 Reputation points Microsoft External Staff Moderator
    2026-08-10T03:06:36.3866667+00:00

    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.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.