Problems with the Application.ScreenUpdating = False command in VBA
Some people are experiencing problems when trying to suppress screen updates while macros are running in Excel VBA, under Windows 10. This blog gives one possible solution.

Posted by Andy Brown on 17 June 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Problems with turning Application.ScreenUpdating off

It seems that there is an intermittent problem with this command in VBA:

'turn screen updating off

Application.ScreenUpdating = False

What this does (or rather, what it should do - and used to do) is (was?) to prevent the screen updating while a macro is (was?) running.  So in a macro like the following one you wouldn't see the screen flashing as each number was added: 

Sub ExampleOfProblem()

Dim i As Long

'start by turning screen updating off

Application.ScreenUpdating = False

'now do something which will take a while to execute

For n = 1 To 10000

Cells(n, 1).Select

ActiveCell.Value = n

Next n

Application.ScreenUpdating = True

End Sub

It appears that sometimes with Windows 10 and Excel 2016 or later the above command doesn't properly freeze the screen.  All that we can suggest is a fix ike this:

Dim n As Long

'turn screen updating off, but also make Excel invisible

Application.ScreenUpdating = False

Application.Visible = False

For n = 1 To 10000

Cells(n, 1).Select

ActiveCell.Value = n

Next n

'redisplay Excel and turn screen updating back on

Application.ScreenUpdating = True

Application.Visible = True

This may not be that a good solution, because it wiil look as if Excel has closed down! 

Can anyone throw any more light on this?

This blog has 0 threads Add post