How to Increase Macro Speed and Decrease Programming Time

Here are some tips to show you how to increase your macro’s running speed as well as how to save programming time when you’re creating a macro.

First, there are a few options in Excel you can automatically turn on or off to help increase your program’s speed. You may have heard of these before, but for those who haven’t here is a quick recap:

Screen Updating: You can turn screen updating off so that Excel does not update the screen image as your code executes to speed up your macro code. No, you won't be able to see what the macro is doing, but it will run faster. Use this code:

Application.ScreenUpdating = FALSE

Be sure to restore the setting to True at the end of your macro

Excel Calculation: You can actually prevent Excel from recalculating a workbook by using the statement:

Application.Calculation = xlCalculationManual

However, when the calculation mode is xlCalculationManual, Excel doesn't update values in cells, and I’ve also heard this could erase the Clipboard Memory or cause other problems with Excel “losing focus”.  If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

Display Alerts: Set this property to False to suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.

Enable Events: This property can be set to False to prevent the application from raising any of its events. Make sure you turn the events off at the start, and back on at all exit points of the procedure.

Now, we can take these a step farther and put them all in a public subroutine to be called upon as necessary. Here’s how to turn all of the above mentioned items on and off at the start or end of a program:

Public Sub SpeedOn()

With ThisWorkbook.Application

    .ScreenUpdating = False

       .Calculation = xlCalculationManual

    .DisplayAlerts = False

    .EnableEvents = False

End With

End Sub

Public Sub SpeedOff()

With ThisWorkbook.Application

    .ScreenUpdating = True

    .Calculation = xlAutomatic

    .DisplayAlerts = True

    .EnableEvents = True

End With

End Sub

Save these two functions in notepad or somewhere you have quick access to. This way, you do not have to type out this code every time you start a new macro, simply copy and paste the pre-written code.

Another macro speed tip I want to share is, notice how I used the With...End With in the above code? If you are using several statements in a row that apply to the same object, use a WITH statement, rather than fully qualifying the object each time, to help make your VBA macro run faster.

Delete Non-essential Sheets Example

Let’s take it a step further. For example, say you have a macro to delete all sheets in an Excel file except for a few important sheets you’re using that you want to keep. How might you increase the speed of this macro?

Public Sub DeleteAllSheets()

For Each ws In ThisWorkbook.Sheets

    If Not (ws.Name = "Important Sheet 1" Or ws.Name = "Important Sheet 2" Or ws.Name = "Important Sheet 3") Then

        SpeedOn

       ws.Delete

        SpeedOff

    End If

Next ws

End Sub

Notice how I call the SpeedOn and SpeedOff functions we created earlier?

Another tip to increase macro speed: when looping through a collection it is usually faster than the FOR EACH statement rather than using the index.  For example, in the code above I used FOR EACH. It would work but would run slower to use a FOR…NEXT loop:

Dim i as Integer

For i = 1 To Worksheets.Count

        If Not (ws.Item(i).Name = "Important Sheet 1" Or ws.Name = "Important Sheet 2" Or ws.Name = "Important Sheet 3") Then

        SpeedOn

        ws.Item(i).Delete

        SpeedOff

    End If

Next i

I hope these tips help increase the speeds of your macros and decrease your coding time. If you have any other tips you’ve implemented yourself I would love to hear about them!