.ScreenUpdating = True taking a long time to execute (7 seconds or more)

Question

I have an application that has been running fine for several months. Today it suddenly takes 7 seconds to execute application.screenupdating = True.

Has anyone observed this strange behaviour before and if so, please point me in the right direction? I've spent many hours trying to find out what's causing this and researching, to no avail.

The problem only occurs right after the application has scanned across columns and automatically hiding and unhiding columns - as needed. On conclusion it calls Sub SettingsOn, which only has the following:

Sub SettingsOn ()
    With Application 
       .EnableEvents = True
       .ScreenUpdating = True ' this is the problem command
       .DisplayAlerts = True
    End With
End Sub

I have also tried switching around the sequence with no effect.

This is the debug.print output of before and after:

• Screenupdate = False SettingsOn 1 11/03/2018 7:08:39 PM
• UsedRange $A$1:$ABP$66 11/03/2018 7:08:39 PM
• {this is where the .screenupdating = True executes}
• Screenupdate = True SettingsOn 2 11/03/2018 7:08:46 PM

The delay is consistently around 7 to 8 seconds after completion the column manipulation. At all other times it executes instantly. As I said earlier, it has never been a problem until now, it was always instant.

It's very strange, I've not seen anything like this before.

Answer 1

I'm not convinced that ScreenUpdating = True is the culprit.

EnableEvents is more likely to cause a delay of more than a split-second. Which events are you using?

To confirm your diagnosis, temporarily replace your SettingsOn procedure with the one below, then run your app like you were before, copy the result from the Immediate Window, and then edit your question to post the result. (If the Immediate Window is not showing the result when the code stops, hit Ctrl+G) :

Sub SettingsOn()
    Dim startTime As Single
    With Application
       startTime = Timer: .EnableEvents = True
       Debug.Print vbLf & vbLf & "EnableEvents =   " & Format(Timer - startTime, "0.000 sec")
       startTime = Timer: .ScreenUpdating = True
       Debug.Print "ScreenUpdating = " & Format(Timer - startTime, "0.000 sec")
       startTime = Timer: .DisplayAlerts = True
       Debug.Print "DisplayAlerts =  " & Format(Timer - startTime, "0.000 sec") & vbLf
       MsgBox "After clicking OK, hit CTRL+G to open immediate window, then Copy the last 3 lines."
       Stop
    End With
End Sub

More to consider:

• If there are queued events when EnableEvents is re-enabled, it could take some time to catch up.

• Another possible affecting factor is memory usage. If a coding bug like a memory leak is (or was present), that can slow things considerably, among other unpredictable behaviours. Have you rebooted recently? Are you working with any object that, perhaps, aern't getting .Closed properly?

Seemingly insignificant bugs could cause ScreenUpdating or EnableEvents to behave differently.

• If you add a .Repaint at the beginning of the test procedure, do the results change?

• What prompted you to noticed the 7-second delay? ie., How often are you disabling & re-enabling?

Other ways to increase speed:

• Disable automatic calculation by setting Application.Calculation = xlCalculationManual (and back to xlCalculationAutomatic afterwards)

• Disable status bar updates with Application.DisplayStatusBar = False

• Excel has to recalculate page breaks with each worksheet change, unless it's disabled with Activesheet.DisplayPageBreaks = False

• Prevent Pivot Table updates: ActiveSheet.PivotTables(“PivotTable1”).ManualUpdate = True

• If your code is copying-and-pastng cells, that takes a considerable amount if time (including recalculation), especially if you're using code like:

  Range("A1").Select Selection.Copy Range("A2").Select ActiveSheet.Paste

  Speed it up by skipping the clipboard: (apparently 25x faster)

  Range("A1").Copy Destination:=Range("A2")

More ideas here.

---

This function could be used to check memory usage during code execution: _(Source)

Function GetMemUsage()
    ' Returns the current Excel.Application Working Memory usage in KB
    Dim objSWbemServices: Set objSWbemServices = GetObject("winmgmts:")
    GetMemUsage = objSWbemServices.Get("Win32_Process.Handle='" & GetCurrentProcessId & "'").WorkingSetSize / 1024
    Set objSWbemServices = Nothing
End Function

Numerous other metrics can also be verified by replacing WorkingSetSize. Complete list here.