Creating macros in Excel is a fantastic way to automate Excel processes and save loads of time. However as your macros get bigger, you may need to speed up Excel macros execution.
This blog post looks at 6 ways that you can speed up your macros. You really need to know this stuff to create efficient macros in Excel.
If you are a user of Excel VBA already, then you may know all, or most of these. Please feel free to comment with the techniques that you use.
Watch the Video – Speed Up Excel Macros
Switch Off Automatic Calculations
Whenever a cell being used by a formula is changed, Excel recalculates the entire worksheet.
Obviously such actions can slow down your macros, especially if you have many formulas.
This behaviour is normally unnecessary, as we only need to see the effect the macro has had when it has finished executing. So it does not need to recalculate until then.
By typing the line below somewhere near the start of the macro code, this will switch Excel calculations to manual. Meaning they will not recalculate as values are changed.
Application.Calculation = xlCalculationManual
Then at or near the end of the macro, they would be switched back to automatic. Excel will immediately recalculate the entire sheet at this point.
Application.Calculation = xlCalculationAutomatic
Stop the Screen Updating
Another very common setting to switch off whilst the macro runs is screen updating.
As the macro runs Excel is constantly updating the screen to show what is happening. However during the macro we do not need this.
We can turn this off in a similar way to the previous technique.
Application.ScreenUpdating = False
Make sure you switch it back on at the end of the macro.
Application.ScreenUpdating = True
Disable Status Bar Updates
Updates to the Status Bar is something most users will not think about, or even notice. But Excel is constantly updating this to provide totals, counts, filtered records, zoom level and other useful information.
During a macro though, we do not need it updated and it is taking up resources.
The code below will disable the updates.
Application.DisplayStatusBar = False
And then enable them again at the end of the macro.
Application.DisplayStatusBar = True
Prevent Excel Events from Running
This setting is one you may need to consider a little more, and it is to disable Events.
As you use Excel, various events are triggered. For example, when you close a workbook, the BeforeClose event is triggered. And when a value on a worksheet is changed, the WorksheetChange event is triggered.
Now these also take up resources, so can be disabled with the line below.
Application.EnableEvents = False
Be aware that if you created macros that run on these events, and you disable Excel events they will not execute.
If you do disable them. Make sure you enable them again at the end.
Application.EnableEvents = True
These four different settings mentioned so far to improve macro performance, are all found on the Application object.
Instead of referring to the Application object 4 different times, they could have been wrapped in a With construct like below.
With Application .Calculation = xlCalculationManual .ScreenUpdating = False .DisplayStatusBar = False .EnableEvents = False End With
And then again at the end of the macro to switch them back.
Stop Selecting Objects when Performing Operations
One thing you definitely want to stop doing, is selecting objects to perform operations on them.
You rarely need to do this. You can change values, test values and format objects without selecting them first. This will greatly speed up Excel macros.
Take an example of copy and pasting data from one worksheet to another. If I record myself doing this. The following code is created.
Range("A1:B5").Select
Selection.Copy
Sheets("ToSheet").Select
Range("B2").Select
ActiveSheet.Paste
It has generated 5 lines of code. Totally unnecessary to select the ranges and the worksheet when performing these actions.
That procedure could be simplified to the code below.
Range("A1:B5").Copy Destination:=Worksheets("ToSheet").Range("B2")
Now this is one example, and the options available to you will depend what you are doing.
By doing some research you can probably optimise your code a little better. And selecting objects unnecessarily is common among beginners to macros in Excel.
Use Data in Memory and Avoid Trips to the Worksheets
Limiting the number of trips you make to the cells of your worksheets will greatly speed up Excel macros.
This can be done by storing data in memory using variables instead. Referring to, and testing the values in variables is far more efficient than accessing the values on worksheets.
Take the code below for example;
Sub UseVariables()
Range("A2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = Worksheets("Sheet5").Range("B3").Value Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value * 1.1
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
MsgBox "Complete"
End Sub
In this code there are many interactions with the worksheets. There is the selection of cell A2. The IF statement compares the value in B3 of the sheet called Sheet5. And then the constant references to the ActiveCell and the cells around it.
The code below is an improvement on this;
Sub UseVariables()
Dim RowNum As Long
Dim SalesName As String
RowNum = 2
SalesName = Worksheets("Sheet5").Range("B3").Value
Do Until Cells(RowNum, 1).Value = ""
If Cells(RowNum, 1).Value = SalesName Then
Cells(RowNum, 2).Value = Cells(RowNum, 2).Value * 1.1
Exit Do
End If
RowNum = RowNum + 1
Loop
MsgBox "Complete"
End Sub
It stores the value in B3 of Sheet5 in a variable and then test that each time, instead of keep going back to look at that sheet.
It also uses a variable to increment the row number so that we do not need to physically move to each cell.
These 6 techniques will stop many of the unnecessary actions that macros perform, and will therefore greatly increase macro speed.
Leave a Reply