Effect of Screen Updating
If you want to see a fairly drastic example of why ScreenUpdating
is important, run the following code. It takes roughly 45 times longer in Excel 2011 for me to run this swap without ScreenUpdating = false
! This is a huge difference in time.
Sub testScreenUpdating()
Dim i As Integer
Dim numbSwitches As Integer
Dim results As String
'swap between sheets this number of times
numbSwitches = 1000
'keep track of time
Dim startTime As Double
startTime = Time
'swap between sheets 1/2 (need both sheets or this will crash)
For i = 1 To numbSwitches
Sheets(1 + (i Mod 2)).Select
Next i
'get results
results = "Screen Updating not disabled: " & Format(Time - startTime, "hh:mm:ss") & " seconds"
startTime = Time
'scenario 2 - screenupdating disabled
Application.ScreenUpdating = False
'swap between sheets 1/2 (need both sheets or this will crash)
For i = 1 To numbSwitches
Sheets(1 + (i Mod 2)).Select
Next i
Application.ScreenUpdating = True
'get results for part two
results = results & vbCrLf & "Screen Updating IS disabled: " & Format(Time - startTime, "hh:mm:ss") & " seconds"
'show results
MsgBox results
End Sub
Also, while we're on the topic of ways to increase efficiency, another key point is that Select
, Selection
, and Activate
are rarely (if ever) necessary. When you record macros it will always use these but there are very few situations when you need to actually use them in code. Likewise, anything with Active
in title (such as ActiveCell
) normally is an indication you will have slower code because you presumably are selecting cells.
You can almost always refer to cells/worksheets specifically and avoid select. For example:
msgbox (Worksheets(1).Range("A1").value)
will work regardless of whether you are currently on the first worksheet. A common new VBA mistake is to do something more like:
Worksheets(1).Select
msgbox (Range("A1").value)
which is an unneeded step.
This adds significant time to code runtimes.
Turning off screen updating will only make a difference to execution time if the code interacts with Excel in a way that causes changes to the screen content. The greater the amount of screen changes the bigger the impact will be. The other posted answers aptly demonstrate this.
Other application settings that can make a difference to execution time are Calculation and Event handling. Use this code template as a starting point (the error handler ensures that these properties are turned back on at the end of the sub, even if it errors)
Sub YourSub()
On Error GoTo EH
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' Code here
CleanUp:
On Error Resume Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
EH:
' Do error handling
Resume CleanUp
End Sub
Other techniques exist that can provide even greater improvement in execution speed.
The most useful include
- Avoid
Select
,Activate
andActiveCell/Sheet/Workbook
as much as possible. Instead declare and assign variables and reference those. - When referencing large ranges, copy the Range data to a variant array for processing and copy the result back to the range after.
- Use
Range.SpecialCells
,Range.Find
andRange.AutoFilter
to limit the number of cells referenced.
There are plenty of examples of these techniques on SO.