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

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

'Set calculation mode to manual (on demand). Default value is xlCalculationAutomatic.
Application.Calculation = xlCalculationManual

Many case you will see a boost in VBA speed.

Turn Off Screen Updating

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

'Turn off automatic ScreenUpdating (on demand). Run ApplicationDoEvents procedure to manually update the screen
Application.ScreenUpdating = False

Avoid Range Copy/Paste Functions

The 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

'This uses up a lot of memory as it copies the everything associated with the Range
'e.g. the value, formatting, data validation etc. Avoid copy at all costs!
Range(...).Copy Range(...)

'Doing Range.Copy in a large loop will surely quickly increase Excel RAM Memory usage or 
'result in crashing Excel altogether
For ....
    Range(...).Copy Range(...)
Next 

'Instead if you want to copy just values do it directly
For ....
    Range(...).Value =  Range(...).Value
Next 

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

'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

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...
Dim x as Long
x = 10 'OK!
'....
y = 20 'Compile ERROR! Variable not declared using Dim statement

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

When 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 “”

Instead of comparing an empty string use thge vbNullString constant:

'Instead of:
emptyStringVar = ""

'Do this:
emptyStringVar = vbNullString

Disable VBA Events

Prevent Excel from firing Excel Events while processing your macro:

'Disables Excel events during the runtime of the VBA 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

'than this:
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:

'Disable page breaks in Excel spreadsheets to save time screen refreshing
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

'Than this:
Const tmpVar as Integer
tmpVar = 1

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

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.