Excel 2010/Snippets/Application

ScreenUpdating
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.

Application.ScreenUpdating = False
 * Disable ScreenUpdating

Application.ScreenUpdating = True
 * Enable ScreenUpdating

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" 'Notes: ' - @val is passed in case of nested methods that use this method.
 * Example

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.

Application.Calculation = xlCalculationManual Application.Calculation = xlCalculationAutomatic 'For all open workbooks Application.Calculate
 * Set calculation to manual
 * Set calculation to automatic
 * Perform manual full recalculation

'For a specific worksheet Worksheets("Sheet1").Calculate

'For a specific range only Worksheets("Sheet1").Range("A1:D4").Calculate

'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
 * Full example functions

'Restore to original setting Function CalculationOn(appcalc As XlCalculation) Application.Calculation = appcalc If appcalc <> xlCalculationManual Then Application.CalculateFullRebuild End If End Function