I want to start off this post by thanking everyone who sent in their examples in response to my January request.
It is incredibly helpful to be able to look at what you all are doing with Excel!
There are tons of sites, pages, and people who are experts as well on this subject, have performed their own tests, and shared their results and ideas.
If you think I missed an important concept for how to optimize Excel VBA performance, or if you’ve got a valuable comment or link to share, please feel free to post here so everyone can benefit. Turn Off Everything But the Essentials While Your Code is Running This optimization explicitly turns off Excel functionality you don’t need to happen (over and over and over) while your code runs.
Note that in the code sample below we grab the current state of these properties, turn them off, and then restore them at the end of code execution. Display Page Breaks = False ‘note this is a sheet-level setting ‘: This setting tells Excel to not redraw the screen while False.
Quick Jump Introduction | Example | Tutorial | Applications | Comments Prevent your screen from updating until your Excel macro is finished with Application. It can be used for other applications, but I find it most useful for Excel.
Screen Updating property is useful when running macros that jump from cell to cell, sheet to sheet, and workbook to workbook. Screen Updating to False stops your screen from flickering (refreshing) as your macro runs.
in Line 2 to see what happens when you don’t disable screen updating.
Your screen will show each action your VBA code performs. It’s a good practice to disable screen updating to decrease total run time.
Don’t forget to set your Screen Updating property back to True when you’re done.
The silly code I presented in the Screen Updating Demonstration just numbers cells. Screen Updating=False are presented below: Ryan Wells is a Nuclear Engineer and professional VBA Developer.
He is the lead developer of several VBA applications, including CF Shapes - the premier Excel Add-in for enabling conditional formatting for shapes.
Discover more of his popular Excel Add-ins, like Mouse To Macro, at his dedicated Excel Add-ins page.