---

Excel VBA: Stop Screen Flickering in Recorded Macros and Speed up Your Code

Got any Excel/VBA Questions? Free Excel Help

One drawback with recorded macros in Excel is that the code produced is often very inefficient. This can mean that what should take a matter of 1 to 3 seconds, often takes a lot longer. It also means that we cannot tell the macro recorder to not record our steps of selecting cells, sheets and scrolling around, even though the selecting of cells, sheets and scrolling is not needed in true VBA code. As a result, of all this selecting and scrolling, the screen flickers whenever your recorded macro is played back.

Application.ScreenUpdating

Those that are familiar with VBA code may also be aware of the term  Application.ScreenUpdating. Unfortunately those that really need to know about this term (those that can only record macros) are often not aware of it. By setting ScreenUpdating to False at the Start of the macro, you will not only stop the constant screen flickering associated with recorded macro, but also greatly speed up the execution of the macro. The reason it speeds up code is because Excel no longer needs to repaint the screen whenever it encounters such commands as Select, Activate, LargeScroll, SmallScroll and many others.

The inclusion of Application.ScreenUpdating=False should be placed at the Start of your macro like shown below

Sub a()

'

' a Macro

' Macro recorded 1/12/2003 by OzGrid.com

'



'

	Application.ScreenUpdating = False

	'YOUR CODE

	Application.ScreenUpdating = True

End Sub

Note how we have set the ScreenUpdating back to True on completion. While Excel will set this back to True whenever focus is passed back to Excel (your macro finishes) in most cases, it pays to play it safe and include the code at the end.

You may even find in some cases that ScreenUpdating is set back to true before your recorded macro completes. This can happen with recorded macros that have the word Select used frequently. If this does happen, you may need to repeat the line: Application.ScreenUpdating = False in other parts of your macr

 

See also:

Excel VBA: Number of Specified Days in a Specified Month

Excel VBA: Code to Locate Two Matches in 2 Separate Columns

Excel VBA: Does Cell Have Formula

Excel VBA Macro: Determine Which Button, Control or Command Button Was Clicked

Excel VBA: Macro Code To Run Macros On Protected Worksheets & Sheets

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•