6. VBA Tips - ScreenUpdating - Make your code run faster
What can you do to make VBA code run faster? Well, when you run VBA code in Excel, you will notice that the screen flickerseach time an action is performed. Lets take a simple example:
If you run this, each time the cell below is selected, you will see that the screen has updated. This is fine as long as your macro is small, but if it is big, Screen Updating can take a long time, and turning it off can make VBA code run faster. Let’s prove that:
This code for me takes 0.64 seconds to run
Now, lets turn off Screen Updating and see the difference:
0.64 seconds vs 0.078 seconds The faster VBA code runs 8 times the speed of the slow code. If you run the same procedure many times a day, this can add up quickly, and allow you to go home earlier.
NOTE: I turned ScreenUpdating back on before displaying the messagebox... If you don't do that, you get something like this:
... which although it looks whacky is likely to annoy your users.
Training Video on how to make your code run faster by turning off ScreenUpdating: