VBA Efficiency

Efficiency is concerned with eliminating waste and thereby improving the speed and performance of code. There are a number of practices that help to improve the efficiency of VBA code.

Close the Visual Basic Editor

The Visual Basic Editor has a status bar which updates continuously while VBA code is running which can severely reduce speed and performance. When running VBA code that does a lot of processing and iterating, closing the Visual Basic Editor drastically improves performance and speed. In some cases, programs can become unresponsive if the Visual Basic Editor is open.

Avoid Variants

Variants require at least 16 bytes of memory which is more than other types require. Using appropriate data types can save space and make programs run faster. Especially avoid large arrays of Variants if possible.

Public Sub TimeVariantVsLong()

    Dim i      As Long
    Dim j      As Long
    Dim t      As Single
    Dim VArr() As Variant
    Dim LArr() As Long

    Const N As Long = 1000

    'Variant
    t = Timer
    For i = 1 To N
        ReDim VArr(0 To 9999) As Variant
        For j = LBound(VArr) To UBound(VArr)
            VArr(j) = j
        Next j
    Next i
    t = Timer - t
    Debug.Print "Variant: " & t

    'Long
    t = Timer
    For i = 1 To N
        ReDim LArr(0 To 9999) As Long
        For j = LBound(LArr) To UBound(LArr)
            LArr(j) = j
        Next j
    Next i
    t = Timer - t
    Debug.Print "Long: " & t

End Sub

Suppress External Activity

Running a VBA program can cause external activity which can slow down the program. It may improve efficiency to disable one or more of the below settings to avoid unnecessary external activity. Just be sure the program does not rely on any of the settings.

Public Sub Example()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'Code Here

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Screen Updating

While a VBA program is running, the application will continuously update the screen to capture the changes made by the running program. This is often unnecessary and inefficient. The screen normally only needs to be updated after the program has finished running to reflect all changes. To prevent the screen from continuously updating while the program is running set the Application.ScreenUpdating property to False.

Events

Events can be triggered by a program while it is running. Often times, it is not necessary or desirable to allow a running program to trigger events. Most often, events are meant to be triggered by user interactions only. If events do not need to be triggered by the program, set the Application.EnableEvents property to False to prevent events from being triggered.

Calculation

If an Excel spreadsheet contains volatile functions these functions will recalculate whenever a change is made anywhere in Excel. This may not be necessary and can slow down a program. If there is no need to recalculate after every change then set the Application.Calculation property to xlCalculationManual while the code is running. To revert back to the default calculation method set the property to xlCalculationAutomatic.

There are built-in volatile functions in Excel and user-defined functions can be made volatile. To make a user-defined function volatile, use the Application.Volatile method.

Built-In Volatile Functions:

NOW
TODAY
RANDBETWEEN
OFFSET
INDIRECT
INFO
CELL
SUMIF

Avoid String Reallocation

Reallocating strings can be very inefficient and unnecessary. Specialized string classes can be used to reduce string reallocation while providing a high level of functionality. The Mid function can also be used to avoid reallocating a string by assigning parts of a string instead of reallocating an entirely new string.

String Classes

To avoid unnecessary string reallocations, use the clsStringBuilder and clsArrayListString classes.

Public Sub TimeclsStringBuilder()

    Dim i As Long
    Dim j As Long
    Dim t As Single
    Dim S As String
    Dim SB As clsStringBuilder

    Const N As Long = 100000

    'Concatenation
    t = Timer
    For i = 1 To N
        S = S & "A"
    Next i
    t = Timer - t
    Debug.Print "Concatenation: " & t

    'clsStringBuilder
    Set SB = New clsStringBuilder
    t = Timer
    For i = 1 To N
        SB.Append "A"
    Next i
    t = Timer - t
    Debug.Print "clsStringBuilder: " & t

End Sub

Public Sub TimeclsArrayListString()

    Dim i As Long
    Dim j As Long
    Dim t As Single
    Dim S As String
    Dim AL As clsArrayListString

    Const N = 100000

    'Concatenation
    t = Timer
    For i = 1 To N
        S = S & "A" & vbNewLine
    Next i
    S = Left$(S, Len(S) - Len(vbNewLine))
    t = Timer - t
    Debug.Print "Concatenation: " & t

    'clsArrayListString
    Set AL = New clsArrayListString
    t = Timer
    For i = 1 To N
        AL.Append "A"
    Next i
    S = AL.JoinString(vbNewLine)
    t = Timer - t
    Debug.Print "clsStringBuilder: " & t

End Sub

Mid Function

A technique to make working with strings more efficient is to allocate a larger string than is needed and use the Mid function to change parts of the string.

Public Sub TimeMid()

    Dim i As Long
    Dim j As Long
    Dim t As Single
    Dim S1 As String
    Dim S2 As String

    Const N As Long = 100000

    'Concatenation
    t = Timer
    For i = 1 To N
        S1 = S1 & "A"
    Next i
    t = Timer - t
    Debug.Print "Concatenation: " & t

    'Mid
    t = Timer
    S2 = String$(N, " ")
    For i = 1 To N
        Mid$(S2, i, 1) = "A"
    Next i
    t = Timer - t
    Debug.Print "Mid: " & t

End Sub

Use For Each Loops with Collections

For Each loops are more efficient for looping over Collections because accessing Collection items using the Item method is inefficient. Because For Each loops access each item in the Collection via the control variable, there is no need to use the Item method when using a For Each loop.

Public Sub TimeForEachVsForWithCollection()

    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim v As Variant
    Dim C As Collection
    Dim t As Single

    Const N As Long = 10000

    Set C = New Collection
    For i = 1 To 100
        C.Add i
    Next i

    'For
    t = Timer
    For i = 1 To N
        For j = 1 To C.Count
            k = C(j)
        Next j
    Next i
    t = Timer - t
    Debug.Print "For: " & t

    'For Each
    t = Timer
    For i = 1 To N
        For Each v In C
            k = v
        Next v
    Next i
    t = Timer - t
    Debug.Print "For Each : " & t

End Sub

Use Arrays Over Ranges

When doing operations on Excel spreadsheet data it can be more efficient to copy the values from a Range into an array, do the operations on the array values, and then copy the array back to the Range.

Public Sub TimeArrayVsRange()

    Dim i As Long
    Dim j As Long
    Dim t As Single
    Dim Arr As Variant

    Const N As Long = 1000

    Application.ScreenUpdating = False

    'Range
    t = Timer
    For i = 1 To N
        Range("A1:A10") = [{1;2;3;4;5;6;7;8;9;10}]
        For j = 1 To 10
            Cells(j, 1).Value = _
            Cells(j, 1).Value * Cells(j, 1).Value
        Next j
    Next i
    t = Timer - t
    Debug.Print t

    'Array
    t = Timer
    For i = 1 To N
        Range("A1:A10") = [{1;2;3;4;5;6;7;8;9;10}]
        Arr = Range("A1:A10").Value
        For j = 1 To 10
            Arr(j, 1) = Arr(j, 1) * Arr(j, 1)
        Next j
        Range("A1:A10") = Arr
    Next i
    t = Timer - t
    Debug.Print t

    Application.ScreenUpdating = True

End Sub

Avoid Selecting

When working with Ranges in Excel VBA it is usually unnecessary to select a Range. Selecting Ranges is inefficient and can slow down a VBA program. It is more efficient to refer directly to a Range using the Range property or to use the Cells property to retrieve the Range than to select a Range using the Select method and then access the Range through the Selection property.

Public Sub TimeSelectVsRangeOrCells()

    Dim i As Long
    Dim t As Single

    Const N As Long = 1000

    Application.ScreenUpdating = False

    'Select
    t = Timer
    Range("A1").Select
    For i = 1 To N
        Selection.Value = i
        Selection.Offset(1, 0).Select
    Next i
    t = Timer - t
    Debug.Print "Select: " & t

    'Cells
    t = Timer
    For i = 1 To N
        Cells(i, 1).Value = i
    Next i
    t = Timer - t
    Debug.Print "Cells: " & t

    Application.ScreenUpdating = True

End Sub

Objects

Allocating and deallocating objects can be inefficient. To improve efficiency, avoid creating objects inside procedures that will be called frequently. In the example below it might appear that it is more efficient to look up a value from a Dictionary because elements of a Dictionary are searched in constant time (O(1)) whereas elements of an array are searched in linear time (O(n)). However, because of the overhead of allocating and deallocating the Dictionary object for each function call, the less efficient array lookup is actually more efficient for small list sizes.

Option Explicit

Public Sub TimeDictionaryVsArrayForSmallList()

    Const N As Long = 100

    Dim i As Long
    Dim t As Single
    
    'Dictionary (more efficient search)
    t = Timer
    For i = 1 To N
        LookupValueFromDict "D"
    Next i
    t = Timer - t
    Debug.Print "Dictionary: " & t

    'Array (less efficient search)
    t = Timer
    For i = 1 To N
        LookupValueFromArray "D"
    Next i
    t = Timer - t
    Debug.Print "Array: " & t

End Sub

Public Function LookupValueFromDict(Key As String) As Variant
    
    'Allocating and deallocating a Dictionary object
    'with each function call hurts performance

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

    Dict.Add "A", 1
    Dict.Add "B", 2
    Dict.Add "C", 3
    Dict.Add "D", 4

    If Dict.Exists(Key) Then
        LookupValueFromDict = Dict(Key)
    End If

End Function

Public Function LookupValueFromArray(Key As String) As Variant
    
    Dim KeyArr(0 To 3) As String
    KeyArr(0) = "A"
    KeyArr(1) = "B"
    KeyArr(2) = "C"
    KeyArr(3) = "D"

    Dim ValueArr(0 To 3) As Variant
    ValueArr(0) = 1
    ValueArr(1) = 2
    ValueArr(2) = 3
    ValueArr(3) = 4

    Dim i As Long
    For i = LBound(KeyArr) To UBound(KeyArr)
        If KeyArr(i) = Key Then
            LookupValueFromArray = ValueArr(i)
            Exit Function
        End If
    Next i

End Function

User-Defined Types Vs Classes

It is more efficient to use an array with a user-defined type than to use an array or Collection with a class. Classes need to be instantiated whereas user-defined types do not. Allocating and deallocating objects is costly and should be avoided where possible. In the example below, because there are no methods for the class, it is better to use a user-defined type instead.

'Class Module: clsExample

Option Explicit

Public Message As String
Public Flag    As Boolean

'Standard Module: Module1

Option Explicit

Public Type TExample
    Message As String
    Flag    As Boolean
End Type

Public Sub TimeUserDefinedTypeVsClass()

    Const N As Long = 10000

    Dim i As Long
    Dim t As Single

    Dim TEArr() As TExample
    Dim TE As TExample

    Dim CEArr() As clsExample
    Dim CEColl As Collection
    Dim CE As clsExample

    'User-Defined Type and Array
    t = Timer
    ReDim TEArr(1 To N) As TExample
    For i = 1 To N
        TEArr(i).Message = "Hello, World!"
        TEArr(i).Flag = True
    Next i
    t = Timer - t
    Debug.Print "User-Defined Type and Array: " & t

    'Class and Array
    t = Timer
    ReDim CEArr(1 To N) As clsExample
    For i = 1 To N
        Set CEArr(i) = New clsExample
        CEArr(i).Message = "Hello, World!"
        CEArr(i).Flag = True
    Next i
    t = Timer - t
    Debug.Print "Class and Array: " & t

    'Class and Collection
    t = Timer
    Set CEColl = New Collection
    For i = 1 To N
        Set CE = New clsExample
        CE.Message = "Hello, World!"
        CE.Flag = True
        CEColl.Add CE
    Next i
    t = Timer - t
    Debug.Print "Class and Collection: " & t

End Sub

Use DLLs

DLLs can be created using faster lower level languages like C++ and C# which can be used in VBA. Using a DLL function may carry its own overhead so may not be suitable for relatively small tasks. In the example below, calling a VBA function outperforms calling a C++ DLL function for small array sizes but calling the C++ DLL function outperforms calling the VBA function for larger array sizes. DLL functions can also be multi-threaded whereas VBA functions are single-threaded. Multi-threading can drastically improve speed.

// C++ ExcelFunctions.dll

#define DLLExport __declspec(dllexport)

extern "C" {

    DLLExport int __stdcall CppSum(int values[], int arraySize) {

        int theSum = 0;

        for (int i = 0; i < arraySize; ++i) {
            theSum += values[i];
        }

        return theSum;
    }
    
}

Option Explicit

Private Declare PtrSafe Function CppSum Lib "C:\ExcelFunctions.dll" ( _
ByRef values As Long, ByVal arraySize As Long) As Long

Private Function VBASum(values() As Long, arraySize As Long) As Long
    Dim i As Long
    For i = 0 To arraySize - 1
        VBASum = VBASum + values(i)
    Next i
End Function

Public Sub TimeDLLVsVBA()

    Dim Result As Long
    Dim i      As Long
    Dim t      As Single
    Dim Arr()  As Long

    Const N As Long = 10000

    Const ARRAY_SIZE As Long = 100
    'C++: 0.078125; Result: 5050
    'VBA: 0.0078125; Result: 5050

    'Const ARRAY_SIZE As Long = 1000
    'C++: 0.109375; Result: 500500
    'VBA: 0.109375; Result: 500500

    'Const ARRAY_SIZE As Long = 10000
    'C++: 0.0859375; Result: 50005000
    'VBA: 1.328125; Result: 50005000

    ReDim Arr(0 To ARRAY_SIZE - 1) As Long
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = i + 1
    Next i

    'C++
    t = Timer
    For i = 1 To N
        Result = CppSum(Arr(LBound(Arr)), ARRAY_SIZE)
    Next i
    t = Timer - t
    Debug.Print "C++: " & t & "; Result: " & Result

    'VBA
    t = Timer
    For i = 1 To N
        Result = VBASum(Arr, ARRAY_SIZE)
    Next i
    t = Timer - t
    Debug.Print "VBA: " & t; "; Result: " & Result

End Sub