To demonstrate the principle of this technique we'll need a small example procedure that makes lots of visible changes to the Excel workbook.
You can either download the example here, or create a new blank workbook, add around five worksheets to it and then copy the code shown below into a new module.
To achieve this we need to add a single line of code to the subroutine, just below the variable declarations.
It's probably also worth changing the colour that we're using so that we can see when things have changed. This time you won't be able to see the macro carrying out its tasks; once the subroutine has finished the screen will update once at the end to show you the final result.
If we want to quantify how much time we've saved we can add some code to create a basic timer system.
The general structure of our timer is shown below: This isn't the most accurate timer system you can create in VBA but it's good enough for our example.
One quick and easy technique to make your code run faster in Excel VBA is to simply prevent the screen from being redrawn each time something changes.
You should be able to see the Excel screen flickering in the background as the macro carries out its tasks until, eventually, it finishes.
The next time we run the procedure we don't want to be able to see the screen flickering in the background.