Todo for Excel 2010/Snippets/Application: edit · history · watch · refresh
  1. Add to a VSTO Library for general use.


Disabling ScreenUpdating stops any changes/operations from being displayed on-screen, which hugely increases performance during large operations.

To use this, disable ScreenUpdating at the start of your submodule or function, then enable it at the end. Note that message and error boxes will still be displayed.

Also see #Disable Calculation.

Disable ScreenUpdating

Application.ScreenUpdating = False

Enable ScreenUpdating

Application.ScreenUpdating = True


This example remembers the current value for ScreenUpdating, disables it, then later restores it back to the original value. It creates two functions: DisableScreenUpdating and EnableScreenUpdating.

'@val - Boolean value, The Previous Setting for "ScreenUpdating"
'   - @val is passed in case of nested methods that use this method.

Function DisableScreenUpdating(val As Boolean) As Boolean
   ' Disable ScreenUpdating, for seamless operation
   If val Then
      Application.ScreenUpdating = False
   End If
   DisableScreenUpdating = val
End Function
Function EnableScreenUpdating(val As Boolean)
   If val Then
      Application.ScreenUpdating = True
   End If
End Function

Manual Calculation

Disabling automatic calculations allows for large cell operations to be conducted, without the entire spreadsheet recalculating each time. For example, sorting many rows or copying large quantities of data into the spreadsheet.

This will increase the performance of the operation, and can also be disabled for the user to manually use the spreadsheet without triggering recalculations. Calculations can then be manually started with the F9 key, or by other code, or setting it back to manual.

Set calculation to manual

Application.Calculation = xlCalculationManual

Set calculation to automatic

Application.Calculation = xlCalculationAutomatic

Perform manual full recalculation

'For all open workbooks
'For a specific worksheet
'For a specific range only

Full example functions

'Disable AutoCalculation
'Store current setting and set calculations to manual
Function CalculationOff() As XlCalculation
   Dim appcalc As XlCalculation: appcalc = Application.Calculation
   Application.Calculation = xlCalculationManual
   CalculationOff = appcalc
End Function
'Restore to original setting
Function CalculationOn(appcalc As XlCalculation)
    Application.Calculation = appcalc
    If appcalc <> xlCalculationManual Then
    End If
End Function

Ad blocker interference detected!

Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.