Fandom

Microsoft Wikia

Excel 2010/Snippets/Application

< Excel 2010 | Snippets

860pages on
this wiki
Add New Page
Talk0 Share
Todo for Excel 2010/Snippets/Application: edit · history · watch · refresh
  1. Add to a VSTO Library for general use.

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 #Disable Calculation.

Disable ScreenUpdating

Application.ScreenUpdating = False


Enable ScreenUpdating

Application.ScreenUpdating = True


Example

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.

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
Application.Calculate
 
'For a specific worksheet
Worksheets("Sheet1").Calculate
 
'For a specific range only
Worksheets("Sheet1").Range("A1:D4").Calculate


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
        Application.CalculateFullRebuild
    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.