Speed up your VBA Code

This article will demontrate how to speed up your VBA Code.

You might have a procedure in your code that takes a long time to run – it may refer back to a sheet continuously or do calculations that seem to put your cursor into a state of a perpetual hourglass. The are 2 main settings we can add to our code to speed up the code, both of which are part of VBA best practices when writing code.

Disable Screen Updating

By default, Excel will display changes to workbook(s) in real-time as VBA code runs.  This means that the screen will update as the macro runs.  This can slow down your code hugely, especially if a long procedure is running.

To prevent this, turn off the Screen Updating. Add this line of code at the top of the procedure:

Application.ScreenUpdating = False

At the end of your macro, you should turn back on Screen Updating:

Application.ScreenUpdating = False

Therefore your macro should look this this example below:

 

Switch off Automatic Calculation

As your macro runs and changes cell values, Excel recalulates any cells that are dependent on the cell whose value changed.  In addition, whenever a formula is changed, calculations are again performed. This can slow down the speed of your macro hugely.

To switch off automatic calculation, add this line of code to the top of your macro.

Application.Calculation = xlManual

and then to switch them back to automatic, make sure you put this line of code at the bottom of your macro.

Application.Calculation = xlAutomatic

Risks Involved when Changing these Default Settings

Excel by default has the Screen Updating set to TRUE and the Calculations set to Automatic.  Using the code to switch off these settings in the Application will speed up your code considerably.  However, if we forget to switch these setting BACK ON, or if the code stops running before it gets to the lines of code that reset these settings, you might end up with an unresponsive Excel Screen (as the Screen will not update) or calculations that will not calculate unless you manually do so.

To solve this problem, always make sure you have an error trap in your code so that if an error does occur to stop the macro, make sure the macro jumps down to the lines of code that reset these settings.

In the above example, we have created an Error Handler called EH.   If something goes wrong in the code, the macro will jump down to the EH and run the last 2 lines of code. As we have NOT put an Exit Sub in the macro above the error handler, if the macro runs perfectly, it will get to the 2 lines of code and reset these settings back to the default.