VBA Best Practices

Last Updated on February 9, 2022 – 7 min read

Save time and work efficiently in Excel with VBA.

A VBA macro needs to be clear, simple, and faster by using the appropriate developing objects.

How optimize a VBA macro? How can you improve your code? How do you speed up your VBA execution?

In this article, you will discover best practices to optimize the execution time of your VBA macro. Your code will be more professional and cleaner. It also gives VBA programmers better maintainability. 

Your code needs to be simple to understand and read to automate complex processes.

Object Applications

How do you make your Excel VBA code run 1000 times faster?

The deactivation of the Excel screen or any pop-up blocks the screen from updating during the code execution.

To save time, at the beginning of your VBA procedure, set at False the objects below. Remove the automatic calculation of your workbook.

Don’t forget to set these objects at True at the end of the program and re-activate the automatic calculation of formulas if necessary.

Sub Optimize_Time_Code() 

    'Remove the update of Excel screen
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'Remove pop-up
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    'Remove the automatic calculation
    Application.Calculation = xlCalculationManual
    
    '... your code
    
    'To be re-activated at the end of the process
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Option Explicit

By using the Option Explicit, you have to declare all variable types such as Long, Integer, Boolean, Decimal, String in your module. It is a good practice to develop in VBA. 

The reading of your code is simpler when Excel does not need to guess the type of your variables. Without the declaration, Excel considers the variable as a Variant.

Option Explicit ' write at the beginning of the module

Sub Use_Option_Explicit ()
'Procedure to show how works the Option Explicit
 
   'This variable is declare using Dim
   Dim i As Integer

   i = 10
   MyResult = i*10 'code error as MyResult is not declared

End Sub

The memory allocated for each object is different.

Declarations

All VBA objects must be explicit, including the module’s name, procedures, variables, functions.

The more explicit you are, the more your code is easier to maintain. Procedures, subroutines, modules, functions need a name that describes as best the task perform.

Indentation

In VBA code, unlike Python, the code indentation is not mandatory, however, it gives better readability and better maintenance. It is essential to think about the future developers that will have the opportunity to revisit your work.

Sub Indentation()
'Name of the module
    
    'Declaration
    Dim i As Long 'indentation in the module
    
    'Loop from 1 to 10 Step 1
    For i = 1 To 10
        
        If i = 2 Then 'indentation in the loop
            i = i + 1 'indentation in the if
            MsgBox "Hello World !" 'Display
        End If

    Next i
    
End Sub

You can define the length of your tab indentation in the options. By default, it is set to 4 spaces. You can activate the Auto Indent.

When your code is too long on a line, use the line break with the underscore character _. It avoids scrolling horizontally for reading.

Sub Use_Line_Break()
'Example of a line break
    
   ' The character "&" is used to concatenate many strings
    MsgBox "Education is the most powerful weapon " & _
        " you can use to change the world. " & _
           "Nelson Mandela"
                
End Sub

Auto Syntax Check

VBA checks the consistency after each word writes in the code. In case of any issue, the anomaly is highlighted in red. If you continue to write code despite this bug, you will be blocked by a debug window. It is, therefore, preferable to save time by deactivating this function in the VBA option window.

IntelliSense Menu

The IntelliSense is a dropdown menu that appears after typing a point. The list contains the members of the active references, including objects, properties, methods, constants.

To activate the IntelliSense menu, check the box Auto List Member in the VBA Editor options.

Optimal Algorithm

Finding the solution that best suits your problems will improve the efficiency of your code. Ask yourself the right questions before starting.

Minimize the use of loops to browse your data. 

Choose objects according to the amount of data to process as custom types, arrays, class modules, or dictionaries. 

Avoid working directly on the cells of your sheet. It is better to work on values stored in memory.

An optimal algorithm will have a low time complexity.

Comments

Comments improve the understanding of your code and the maintenance for developers. Suppose that you need to update your code in one year. It is a good practice to comment code for later use.

Be explicit and concise as possible in your comments to explain what your code is trying to accomplish. In VBA, explanations are written in green using apostrophes.

Sub Write_Comments ()
'Write a comment to explain the purpose
'of the Sub 
    
    'Declarations
    Dim i As Integer 'i will be a counter
    Dim a As Long
    Dim sh As Sheet
    
    'Initialisation
    a = 2
    
    For i = 1 To 10 
       ... 'Explain the purpose of loop
    Next i 

End Sub

Clarity

Limit the number of lines in your VBA modules. Keep your code short and straight to the point. 

You can declare several variables on the same line and write an if loop on the same line when possible.

Sub If_Line ()
    
    Dim i, a, b As Integer
    
    i = 2

    If i = 2 Then a = 2 'If with one action
    
   'If with several actions to perform
    If i = 3 Then 
       a = 3
       b = 3
    End If

End Sub

You can check the number of code lines, depending on your cursor position in the VBA editor.

Time measurement

Knowing the time consumed by your code allows you to know if your solution is optimal.

You can use the time of your machine to calculate the difference between the time at the beginning and the end of your macro.

Sub TimeMeasurement()
    
    'Declaration
    Dim x, y, z As Double
    
    x = Timer
    
    '... code to estimate ...
    
    y = Timer
    z = y - x
    MsgBox "Time elapsed : " & z & " sec."

End Sub

Activate & Select

Try to not use the select and activate method for sheet, workbook, range, cell, column, row as much as possible. Your code will be faster without these methods. It is better to work directly with the object.

Sub Set_References()
    
    'Declaration
    Dim ShData As Worksheet
    Dim ShResults As Worksheet
    Dim WbMacro As Workbook
    Dim WbResults As Workbook
    Dim RngData As Range

    'Initialisation
    Set WbMacro = Workbooks("Macro.xlsx")
    Set WbResults = Workbooks("Results.xlsx")

    Set ShData = WbMacro.Sheets("Data")
    Set ShResults = WbResults.Sheets("Results")

    Set RngData = WbMacro.Sheets("Data").Range("A1:Z26")
    
End Sub

Value2

Instead of using the Value method, it is faster to use Value2  in your code.

Sub Use_Value_Two()
    
    'Declaration
    Dim a, b As Double
    
    a = Range("A1").Value

    'Value2 method is faster than Value
    b = Range("A1").Value2

End Sub

Empty or blank

If you want to set a variable as empty, it is better to use the double quotes “” instead of the vbNullString method. Writing the double quotes is faster than vbNullString.  

Sub UseDouble Quotes()
    
    'Declaration
    Dim a, b As String
    
    a = "weather"
    b = ""
    c = vbNullString

    If b = "" Then c = "" 'if condition on one row

End Sub

Use Existing Functions

As much as possible, call the functions already existing in VBA. It is more efficient than re-creating the methods on objects. It is essential to structure your code well.

Sub Use_Worksheet_Function()

    Dim TotalAmount As Long
    
    With Application.WorksheetFunction
        TotalAmount = .Sum(Sheets("Calculator").Range("D1:D500"))
    End With
    
End Sub

The Zen of Python

The PEP 20 (Python Enhancement Proposals) called the Zen of Python gives pieces of advice on development to provide a basic set of guidelines and standards to code in Python. 

Beautiful is better than ugly
Explicit is better than implicit
Simple is better than complex
Flat is better than nested
Sparse is better than dense
Special cases aren’t special enough to break the rules
Although practicality beats purity
Errors should never pass silently
Unless explicitly silenced
In the face of ambiguity, refuse the temptation to guess
There should be one — and preferably only one — obvious way to do it
Although that way may not be obvious at first unless you’re Dutch
Now is better than never
Although never is often better than right now
If the implementation is hard to explain, it’s a bad idea
If the implementation is easy to explain, it may be a good idea;
Namespaces are one honking great idea — let’s do more of those

It can be applied for development in general, including the VBA language.