Speeding up VBA code Execution

I've seen this covered by many people, but I wanted to cover the eight lines of code to add that will allow you to see a very marked improvement in VBA code performance. If you do a lot of cell modification or tab changes using VBA then using this code will greatly improve your codes execution. This first set turns everything off

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Your code section would go here

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

The last section turns everything back on. I have seen several flavors of the above code. One was built to check the current state then shut everything off and then return to the original state at the end which would be a better user experience, but for the code I was building I wanted to keep everything simple and just shut everything down and turn it all back on. I did see a marked improvement in code performance using this (approximately 60% speed increase).

A few things to note regarding using these code segments. Think about how you want to utilize this, setting ScreenUpdating to "False" essentially freezes the Excel screen that the user sees until you return the status to "True". If you have a DB pull that takes 5 or 10 minutes to execute you may want to find some way to show the user that things are still working. I built a small tool that connected to an Analysis Services cube and pulled down dimension and hierarchy data into separate tabs and tables. I used this code on the table generation code so I could have an easy way for the user to see that things were still working, but it still cut out a lot of processing time by not showing the table being created and sorted.

Also, do yourself another favor and comment this code out until you are finished with the code segment you are working on that is going to get the speed boost. Being able to see each thing happen as it is called by the VBA code is a quick way to resolve potential bugs that may not be related to code.