VBA performance

Guide to Improving VBA Performance. Faster Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (11 votes, average: 4.55 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.

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 performanceTo increase VBA speed of execution be sure to set the Calculation mode to xlCalculationManual so that no Excel formula calculations are carried out within the Excel Workbook until the Calculation mode is changed back to xlCalculationAutomatic or by running Application.Calculate:

Many case you will see a boost in VBA speed.

Turn Off Screen Updating

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

Avoid Range Copy/Paste Functions

vba performanceThe Excel Range.Copy procedure is a memory hog. Unfortunately in VBA we have limited control over the Garbage Collector – the mechanizm by which Excel will free up internal memory (declared unused variables, tables, objects etc.). The Excel Garbage Collecter unfortunately runs seemingly at specific intervals which means that if you create a lot of objects in a short amount of time you may run out of memory resulting in slowing down of Excel or simply crashing the application altogether

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 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:

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

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

Use vbNullString vs “”

vba performanceInstead of comparing an empty string use thge vbNullString constant:

Disable VBA Events

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

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.

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:

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.

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.

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

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