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!