There are many different articles and post on improving Excel performance. What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works bla bla bla.
That is why I gathered all VBA performance guidelines into this single post which contains all the known ways of Improving VBA Performance, organized by impact on VBA Performance.
Turn Off Automatic Calculation
Set the Calculation mode to xlCalculationManual so that no calculations are carried out within the Excel Workbook until the Calculation mode is changed back to xlCalculationAutomatic or by running Application.Calculate:
Application.Calculation = xlCalculationManual
Turn Off Screen Updating
Prevent Excel from updating the Excel screen until this option is changed to True:
Application.ScreenUpdating = False
Use For Each on Collections
The For and For Each Loops are not equivalent and should be used in different situations. ALWAYS use the For Each Loop when looping through Collections, Ranges etc.
This is slower:
Dim tmpCell as Range For i = 1 to 10 tmpCell = Cells(i,1) tmpCell.Value = 1 Next i
Dim tmpCell as Range For Each tmpCell in Range("A1:A10") tmpCell.Value = 1 next i
This is because looping through Objects of a Collection is slower.
Use Option Explicit
It is not so much the presence of this macro that benefits your performance but the impact of the behavior it requires from you. Option Explicit requires that you declare ALL VARIABLES. Add this option at the very beginning of your VBA project module or class. This will force you to explicitly declare the types of your variables. VBA is a dynamic typed language therefore you do not normally have to declare the type of your variable or declare variables in that matter. This causes some additional overhead when your VBA code is executed especially if this variable is used often in your code:
Option Explicit '...your module code below...
The XLSB format vs. XLSM
A known way to improve Excel VBA speed and efficiency, especially fore large Excel files, is to save your Workbooks in binary XLSB format. To read more on the benefits read my post on the XLSB format.
Use Early binding of Object
When working with OLE objects remember that it is more efficient to declare the OLE object type explicitly. Use:
Dim wb as Excel.Application
Dim wb as Object
Use vbNullString vs “”
Instead of comparing an empty string like this
emptyStringVar = ""
do it like this:
emptyStringVar = vbNullString
Disable VBA Events
Prevent Excel from firing Excel Events while processing your macro:
Application.EnableEvents = False
Use With…End Clause
Use the With…End Clause when working with Object such as Ranges, Worksheets, Workbooks, Collections etc. It is not only more efficient but also much easier to read, and simplifies your code.
This is better:
With Range("A1") .Value = 1 .Font.Interior.Color = vbBlue End With
Range("A1").Value = 1 Range("A1").Font.Interior.Color = vbBlue
Disable Excel PageBreaks
Disabling PageBreaks in Excel can increase Excel and VBA Performance. Read here why this helps your VBA performance: MSDN PageBreaks and Performance. You can disable PageBreaks like this:
ActiveSheet.DisplayPageBreaks = False
Use VBA Constants
Constants are precompiled variables that can’t be modified during VBA untime. Because of this they are more efficient than regular variables defined with the Dim statement. This is slower:
Dim tmpVar as Integer tmpVar = 1
Const tmpVar as Integer tmpVar = 1
Procedure for increasing VBA performance
It is good to handle turning off some of the optimizations mentioned above. I usually use a procedure I defined which I named “OptimizeVBA”. I turn optimizations before running my macro and then turn them off after completion.
Option Explicit Sub OptimizeVBA(isOn As Boolean) Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic) Application.EnableEvents = Not(isOn) Application.ScreenUpdating = Not(isOn) ActiveSheet.DisplayPageBreaks = Not(isOn) End Sub 'Some macro Sub ExampleMacro() OptimizeVBA True 'Your code here OptimizeVBA False End Sub
Calculations and Screen updating
Turning ScreenUpdating and Calculations off although improves VBA performance can sometimes causes some issues.
In case calculations are turned off do not expect that Excel with recalculate its formulas based on any changes made to your Worksheets. In case you want to control these recalculations use (in this order):
- ActiveSheet.Calculate – this will recalculate only the “dirty” cells within your ActiveSheet
- ActiveWorkbook.Calculate – this will recalculate all the “dirty” cells within your ActiveWorkbook
- Application.CalculateFull – this will recalculate all open Excel Workbooks
- Application.CalculateFullRebuild – this will recalculate all open Excel Workbooks and rebuild all dependencies
When turning ScreenUpdating off you will not be able to access or see what is happening on the Excel Worksheet. In some rare cases Excel may crash assuming that the Application is not responding. So how to respond to this? How to control when the Screen is Updated and when it is not? Use the DoEvents procedure. See the example below
'Some macro Sub ExampleMacro() OptimizeVBA True 'Your code here DoEvents 'Update screen etc. 'Your other code here OptimizeVBA False End Sub
If you still aren’t satisfied with your VBA performance try Compiling your VBA to a .NET COM DLL or multithreading VBA. VBA code can be mulithreaded although this makes sense for macros running more than approx. 10 seconds (as there is an overhead).
Measuring VBA execution time
When working on improving VBA performance you will certainly want to verify how some optimization will reduce your total execution time. There are several known methods for measuring VBA code execution time. The Performance Module of the VBA Time Saver contains at least 2 methods for measuring execution time as well as other way for Improving VBA Performance. There is also a hack to Create an Additional VBA Thread.
The Performance Module contains:
- 2 Methods for Calculating VBA Execution Time (up to 1/1’000’000 of a Second)
- Turn Optimizations On/Off
- Create Additional VBA Thread (unstable on some environments)
Before you start optimizing your VBA macro…
Are you sure you need VBA (Visual Basic for Applications) to solve your problem? Really? Check out my Do you really need Visual Basic for Applications macro for that? article to make sure you aren’t wasting your time writing VBA macros for something available as a feature in Excel.