Universal solution for Excel 2013 & 2016 screen flicker due to ScreenUpdating = FALSE not working

Question

There have been dozens of questions about screen flickering in Excel 2013 and 2016, as Application.ScreenUpdating = False does not work as it used to.

Screen Flickering VBA Excel
Excel ScreenUpdating False and still flickering screen
ScreenUpdating = False fails in Excel 2013 and 2016

One partial solution involves disabling worksheet calculations, but they can be necessary in some models. Another partial solution involves making workbooks invisible, which is either confusing for the user (if all workbooks disappear), or still sees the screen flickering (if you make all workbooks except one disappear).

Ideally, I am looking for a solution that allows DoEvents to be run (so that it doesn't look like Excel is hanged), the allows the StatusBar to be visible (for reporting on progress), and prevents all other screen flicker.

Why can't Microsoft just sort this out :(

Here is some sample code which requires two blank workbooks (Book1 and Book2) and induces a horrible screen flicker.

Sub Macro1()
    ' this worked fine in Excel 2010
    Application.ScreenUpdating = False

    ' this clears all flicker, but the whole of excel disappears
    ' Application.Visible = False
    ' this changes the flicker between Book1 and blank white screen
    ' Application.Workbooks("Book2").Windows(1).Visible = False

    ' some flickery code
    For i = 1 To 10
        Windows("Book2").Activate
        Call Jiggle("Red")
        Windows("Book1").Activate
        Call Jiggle("Yel")
    Next i

    ' restore normality
    Application.ScreenUpdating = True
    Application.Visible = True
End Sub


Sub Jiggle(c As Variant)
    For i = 1 To 100
        ActiveCell.Offset(1, 0).Select
        If (c = "Yel") Then Selection.Interior.Color = 255
        If (c = "Red") Then Selection.Interior.Color = 65535
    Next i
End Sub

I *think* your problem is that you're relying on `Select` and `Activate`, which [should generally be avoided](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). I suspect that if you refactor the code to avoid those methods, you won't see any flickering. — David Zemens, Aug 15 '18 at 13:22

score 1 · Answer 1 · edited Aug 15 '18 at 13:35

The flickering comes most likely from using .Select and .Activate:

It's the environment screen that's updating to reflect the activation of the respective workbooks. Application.ScreenUpdating applies only to the application instance, it doesn't disable your entire monitor from refreshing as the various Workbook windows gain/lose focus from the Activate method. As you'll observe, while the screen flickers, you don't see the cell color changes until the procedure has finished. This suggests that Application.ScreenUpdating is working exactly as its intended to work, and that to resolve this, you should read and apply How to avoid using Select in Excel VBA.

Using .Activate or .Select is only necessary in very rare cases, so avoid it where ever possible.

I'd also note that if it is a strict requirement to rely on the Selection or ActiveCell as user input, it won't be possible to fully eliminate the window flickering, because Selection or ActiveCell only exist within scope of the ActiveSheet in the ActiveWorkbook.

I recommend to give the worksheet as parameter in Jiggle and then to address the cells absolutely. This makes your code more reliable and it runs in the background without selecting and without activating anything.

Option Explicit 

Public Sub Macro1()
    Application.ScreenUpdating = False

    ' some flickery code
    Dim i As Long
    For i = 1 To 10
        Call Jiggle("Red", Workbooks("Book2").Worksheets(1))
        Call Jiggle("Yel", Workbooks("Book1").Worksheets(1))
    Next i

    ' restore normality
    Application.ScreenUpdating = True
End Sub

Public Sub Jiggle(c As Variant, ws As Worksheet)
    Dim i As Long
    For i = 1 To 100
        With ws.Cells(i, 1) 'address your cells absolutely
            Select Case c
                Case "Yel":
                    .Interior.Color = 255
                Case "Red":
                    .Interior.Color = 65535
            End Select
        End With
    Next i
End Sub

Note that this example is to show how not to use .Select and @DisplayName showed a shorter/faster way to achieve the same.

Added some detail as paragraph 2 :) Cheers. – David Zemens Aug 15 '18 at 13:29 — David Zemens, Aug 15 '18 at 13:29

DisplayName · Answer 2 · 2018-08-15T13:14:34.237

1

it seems to me that your whole code could boil down to:

Public Sub Macro1()
    Application.ScreenUpdating = False 
    Workbooks("Cartel2").Activate
    ActiveCell.Offset(1).Resize(1000).Interior.Color = 65535
    Workbooks("Cartel3").Activate
    ActiveCell.Offset(1).Resize(1000).Interior.Color = 255
    Application.ScreenUpdating = True
End Sub

edited Aug 15 '18 at 13:14

answered Aug 15 '18 at 08:04

DisplayName

13,283
2
11
19

It's not clear how this answers the question, as the question was not "how to optimize my code", and further while this *reduces* the flicker, it doesn't eliminate it :) – David Zemens Aug 15 '18 at 13:19
@DavidZemens, I showed a way to reduce flickering due to unnecessary workbook/worksheet jumping and cell by cell acting. Furthermore as long as you don't have any other `Range` reference than `ActiveCell`, you _have_ to `Activate` its `Workbook` in order to reference it. Finally, in my test my code didn't flicker at all. – DisplayName Aug 15 '18 at 13:31
In Excel 2016, I do see the windows toggle focus, I edited the other answer with an explanation for this (paragraph 2-4) and why I suspect this isn't controlled by the `ScreenUpdating` property of the Application instance. Of course if you're relying on `ActiveCell` you must `Activate`. A good answer ought to include some explanation in addition to "here's code that works". Cheers. ([*do try to mention any limitations, assumptions or simplifications in your answer. Brevity is acceptable, but fuller explanations are better*](https://meta.stackexchange.com/help/how-to-answer)) – David Zemens Aug 15 '18 at 13:37
@DavidZemens, suit yourself – DisplayName Aug 15 '18 at 13:38
@DavidZemens, BTW, from the Help center: _"When should I vote down? Use your downvotes whenever you encounter an answer that is clearly and perhaps dangerously incorrect."_ – DisplayName Aug 15 '18 at 13:48

Universal solution for Excel 2013 & 2016 screen flicker due to ScreenUpdating = FALSE not working

2 Answers2