VBA performance

Guide to Improving VBA Performance. Faster Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

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

vba performanceSet 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

vba performancePrevent Excel from updating the Excel screen until this option is changed to True:

Application.ScreenUpdating = False

Use For Each on Collections

vba performanceThe 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

than this:

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

vba performanceIt 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

vba performanceA 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

vba performanceWhen working with OLE objects remember that it is more efficient to declare the OLE object type explicitly. Use:

Dim wb as Excel.Application

instead of

Dim wb as Object

Use vbNullString vs “”

vba performanceInstead of comparing an empty string like this

emptyStringVar = ""

do it like this:

emptyStringVar = vbNullString

Disable VBA Events

vba performancePrevent Excel from firing Excel Events while processing your macro:

Application.EnableEvents = False

Use With…End Clause

vba performanceUse 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

than this:

Range("A1").Value = 1
Range("A1").Font.Interior.Color = vbBlue
With…End Clause on this MSDN Article here

Disable Excel PageBreaks

vba performanceDisabling 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

vba performanceConstants 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

than this:

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.

Manual recalculation

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

Screen Updating

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

Still slow?

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

VBA Time Saver KitWhen 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.

Related Posts

3 thoughts on “Guide to Improving VBA Performance. Faster Excel VBA”

Leave a Reply