6. VBA Tips - ScreenUpdating - Make your code run faster

Nick's picture


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:

screenupdating-excel-1 

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:

screenupdating-excel-2

This code for me takes 0.64 seconds to run

faster-VBA-code 

Now, lets turn off Screen Updating and see the difference:

screenupdating-excel-3

faster-VBA-code

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:

faster-VBA-code

... which although it looks whacky is likely to annoy your users.

Download Spreadsheet to see how turning off Screen Updating speeds up VBA code

 

Training Video on how to make your code run faster by turning off ScreenUpdating: