Creating macros in Excel can be a difficult task, especially if you’re just starting out. However, Excel thankfully has several features that are designed to help developing macros. For example, calculations automatically update after every action, the status bar gives you details about processes, and page breaks will be automatically set again every time something is updated.
Although these features typically help you do some things faster and can contain useful information, they may not be necessary when a macro is done building and is in production. In many cases, they can slow down the execution of your application unnecessarily. In this article, we’re going to show you different methods for improving VBA macro performance by manipulating some settings.
Switch to Manual Calculation
By default, Excel runs calculations after a cell is updated or workbook structure is changed (i.e. adding a new sheet). This is a good thing when working with relatively simple applications, however it may be one pesky performance bottleneck when you are copying data in bulk or updating cells via VBA code. Imagine copying 1000 rows of data, where Excel is calculating everything in your workbook, every time. If you don't need the result of a formula when copying data, it might be a good idea to stop calculations from running until you’re done.
You can alter Excel's calculation setting from Application.Calculation to xlCalculationManual.
Application.Calculation = xlCalculationManual
Add this code block before the rest of your code. And when you need the result of a formula at any other point in your code, use
- Calculate or Calculate to calculate all open workbooks.
- Worksheets("Sheet1").Calculate to calculate a specific workbook.
- Worksheets("Sheet2").Cells(3,2).Calculate to calculate a specific range or cell.
Calculate
Remember to revert the calculation type back to automatic, unless you prefer to use it on manual outside of VBA.
Application.Calculation = xlCalculationAutomatic
Turn Off Screen Updating
Turning off screen updating and setting manual calculations work in conjunction. By default, Excel redraws the screen after every update and this can have an impact on VBA macro performance. Typically, it’s not imperative to see what the code is doing at every step, unless you want to see a wall of characters flowing on your worksheet. You can turn off screen updating by adding the following into your code:
Application.ScreenUpdating = False
Like in the auto-calculate case, remember to turn the setting on after your code is finished. Otherwise, you won't see the changes your code has made.
Application.ScreenUpdating = True
Turn Off Status Bar
The Status Bar is the section on the bottom left corner of your Excel window, and is used to give information about active processes. You can modify it to show your own messages as well. However, if you do not need this information, you can turn off to improve VBA macro performance.
To turn this feature off, use the following code:
Application.DisplayStatusBar = False
To re-enable it after the code runs, use the following:
Application.DisplayStatusBar = True
Choose the right loop: For vs For Each
Both For and For Each loops can handle similar scenarios and usually you’re not going to feel a difference in performance. However, this doesn’t apply to all cases.
Use the For Loop when working with arrays. Another advantage of the For Loop is that it allows you to replace array items, while a For Each Loop doesn’t.
For i = LBound(myArray) To UBound(myArray) j = myArray(i) Next i
Use For Each Loop when working with collections and ranges.
For Each ws in Activeworkbook.Worksheets MsgBox ws.Name Next ws
Turn Off Events
Events are actions that are triggered by user input or macros. For example, Worksheet_Change event is triggered when a cell in a certain worksheet is updated. Workbook_Open event is triggered when opening a workbook that contains macros. They are useful milestones with which you can run a code right after or before a user or macro action. However, if you do not need these types of events to trigger, disabling them may improve the performance of your application significantly if you have code that is dependent on them. You can disable events using the following code:
Application.EnableEvents = False
Once again, remember to activate them after your code is finished. Otherwise events will not be triggered again in the workbook. You can use this code to do this:
Application.EnableEvents = True
Turn Off Page Breaks
If your code doesn't have any print functions, disabling Page Breaks will improve workbook performance. This is because Excel recalculates Page Breaks after every workbook update, to accommodate for updates in cell size or values that may affect cell size. Use the following code to disable this:
ActiveSheet.DisplayPageBreaks = False
Page Breaks are worksheet based, and not application based, like other settings we’ve covered so far. So you may need to turn this off for other sheets as well. Use this code to enable them again once your code finishes:
ActiveSheet.DisplayPageBreaks = True
All-in-One
Below is a code block that contains some of the improvements we’ve covered in this article. You can use this code, replacing 'Place your code here’ with your own code.
Sub CodeOptimizer() Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False 'Note: sheet-level setting. ActiveSheet.DisplayPageBreaks = False 'Place your code here Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True 'Note: sheet-level setting. ActiveSheet.DisplayPageBreaks = True End Sub
