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

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel VBA . 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 macro.

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS