[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous|Disable-time-wasters) | Next

---

Disabling screen updating the code will usually run much faster.

1. Description

While the code runs, the screen updates quickly, and it is usually not necessary for the user to see each update.

End-users typically don’t want to see the behind-the-scenes actions of your procedures (especially when the procedure runs slow). Also, you may not want end-users to see some functionalities (ex., hidden worksheets).

Often, a single screen update after the code executes is all that is necessary.

Disabling screen updating, you won't be able to see what the macro is doing, but:

• it will run faster (in particular when VBA interacts with Excel);
• it will stop the screen from flickering;
• it will make your work appear more professional.

2. How to use

It is a property of Application.

If Application.ScreenUpdating = False, Excel does not redraw the screen.

After your code executes, restore the functionality to its original state.

• Application.ScreenUpdating = False at the beginning of your code
• Application.ScreenUpdating = True (or original state) before the ending of your code

It is better to store the pre-program property value to change the calculation style back to the one the user initially had. This would be part of best practices for UX (user experience) since the user will never even know the property was changed. Generally, setting it back to True after your program finishes (or aborts from an error) is a safe bet.

Example

Sub Stop_ScreenUpdating ()
    Application.ScreenUpdating = False
        '... your code here...
    Application.ScreenUpdating = True
End Sub

3. Performance test

The test example hides every even column on Sheet3.

The first time the example hides the columns, screen updating is turned on; the second time, screen updating is turned off.

Sub Test_ScreenUpdating()
'------------------------------------------------------------------------------
'Declare
    Dim Col
    Dim cPM             As cPerformanceMonitor      'Performance tracker
'------------------------------------------------------------------------------
'Initialize
    Set cPM = New cPerformanceMonitor
    Worksheets("Sheet3").Activate
'------------------------------------------------------------------------------
'Test1
    Application.ScreenUpdating = True
    cPM.StartTimer (t_Timer)
    For Each Col In ActiveSheet.Columns
        If Col.Column Mod 2 = 0 Then Col.Hidden = True
    Next Col
    Debug.Print cPM.ElapsedTime(t_Timer) & " - ScreenUpdating = True"
'------------------------------------------------------------------------------
'Test2
    Application.ScreenUpdating = False
    cPM.StartTimer (t_Timer)
    For Each Col In ActiveSheet.Columns
        If Col.Column Mod 2 = 0 Then Col.Hidden = True
    Next Col
    Debug.Print cPM.ElapsedTime(t_Timer) & " - ScreenUpdating = False"
'------------------------------------------------------------------------------
'Exit
    Application.ScreenUpdating = True
    Set cPM = Nothing
End Sub

4. Results

• 00:00:22 - 910 ms - 156 µs - 250 ns - ScreenUpdating = True
• 00:00:02 - 539 ms - 062 µs - 500 ns - ScreenUpdating = False

Disabling screen updating in the above example is almost 10 times faster!

5. Conclusions

I recommend disabling (and re-enabling) ScreenUpdating in virtually all your procedures.

There is normally no benefit to having the screen continuously updating while performing procedures:

Points to consider:

• Make sure to have the screen updating “ON” when using a user form.
• In debugging code, it is better to have a screen updating “ON”.
• In some cases, you may want the screen to refresh.
• Notice that this is an application-level property. It’ll be set for all open workbooks unless you’ve initialized a second instance of Excel (which is uncommon).

---

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous|Disable-time-wasters) | Next