This slows your computer's processing power down and in turn decreases your computer's ability to run your code as fast as it can. Your computer is simply trying to do too many things at once. Let's look at these settings in detail to appease those of you who want to know why this code works. Screen Updating The first macro I ever ran was a co-worker's that took 30 minutes to finish executing it did a lot of stuff and was very poorly written. I would always say she was trying to brainwash me through the macro because there was non-stop screen-flickering for 30 straight minutes!
Little did I know that a single line of code would have saved my eyes and mind from deterioration and at the same time speed up the code's runtime dramatically. The only scenario I can think of where you might want it on is if your code is displaying some sort of animation on screen. Displaying Page Breaks I have a personal hatred for the visualization of page breaks, but I suppose there are a few of you out there who might like them. You could image the time consumption that might take place if you are running VBA code that is deleting or adding thousands of rows to a spreadsheet.
Because Page Breaks need to constantly recalculate, it is good to play it safe and shut them off while your code is being executed Now Page Breaks are different from the other settings I cover in this article.
A Page Break is a worksheet-level setting rather than an application-level setting. This means there is a "light switch" in each worksheet of your workbook. To keep this article's code clean, I assumed the macro would only affect the ActiveSheet. If your code will be modifying multiple worksheets, you may want to turn off the displaying of Page Breaks for all the sheets getting affected.
If you want to be an overachiever, you can add some VBA that turns off every single Page Break currently in existence during your code's runtime.
You can find this code over at the Code Vault section of my website. Calculation Modes If your code is affecting cells that feed cell formulas, Excel will try to recalculate the values in real time. Significant runtime efficiency can be made if you turn automatic calculations off while your VBA code is executing. But make sure you turn the calculation setting back to automatic or semi-automatic or you will cause extreme confusion to yourself or your user.
These "events" can range from activating a specific worksheet to being trigger by any change made on the spreadsheet. Events will still be triggered and run while your VBA code is executing.
This could lead to extra lines of code being executed and at a worst-case scenario, your spreadsheet being altered and screwing up what your VBA code was intending to do.
Turning off these event triggers temporarily will make your code run much more smoothly if you or your user happens to be using VBA code that is kicked off via events. Go Forth And Optimize!
So now you have a very simple way to make your code run much more efficiently and you hopefully understand why you need to temporarily shut off these settings! Let me know how much time this method shaved off your code's runtime in the comments section below. I'd love to hear your story! Is it as crazy as my minute macro experience? Bonus points go to the winner for 'Most Time Saved'! Did you find this post helpful? Do you want to support this blog because you're just that awesome?!
Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation. Learning is the whole reason why this blog exists! Thank you so much for reading and I hope I can continue to provide you with great content in the future!