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.
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.
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 firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates