Good Practices

Save time and work efficiently in Excel tool thanks to VBA.

A VBA macro needs to be clear, simple, and faster as much as possible using the appropriate objects.

How many times did you search to optimize code execution? How many times have you been in your work face to an unintelligible VBA module because of a developer who has not let his work easy to take over?

You are in the right place the practices below will help you optimize your macro modules and the execution time. Your code will be more professional and cleaner. It also gives VBA programmers better maintainability. Your code will be simple to understand and quick to read to automate complex processes. We need to find the fastest ways to accomplish a task.

Object Application

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

To save time, at the beginning of your VBA procedure, set at False the objects below, and 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. If you leave the automatic calculation option, Excel will re-calculate all formulas in your sheet during the code execution, which will be a waste of time.

Sub OptimizeTimeCode() 

    '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 will have to declare all variables (Long, Integer, Boolean, Decimal, String…) in your module. It is a good and useful habit to develop in VBA.

The reading of your code will be simpler Excel will not have to guess the type of your variables, which will improve the execution time. Without the declaration, Excel considers the variable as a Variant.

Option Explicit ' To be write at the beginning of the module

Sub UseOptionExplicit ()
'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, names of modules, procedures, variables, functions.

The more explicit you are, the more your code will be easier to maintain and understandable for everyone. Procedures, subroutines, modules, functions need a name that describes as best the task they 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), and it is useful to activate the Auto Indent.

When your code is too long for a line, don’t hesitate to use the line break with the following character “_”. It will avoid scrolling horizontally for reading.

Sub UseLineBreak()
'Use 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 need to continue despite this bug, you will be blocked by a debug window. It is, therefore, preferable to save time to deactivate this function in the VBA option window.

IntelliSense Menu

It 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, you need to check the box “Auto List Member” in the VBA Editor options.

Optimal Algorithm

Improving the efficiency of your code is, above, all finding the solution that best suits your problems. Ask yourself the right questions before starting.

Minimize the use of loops to browse your data.

Choose the right objects according to the amount of information to be processed like custom type objects, arrays, class modules, and dictionaries. 

Avoid working directly on the cells of the sheet, prefer to work on values stored in memory.

An optimal algorithm will have a low time complexity.

Comments

Comments allow improving the understanding of code and maintenance for the next developers. Suppose that you need to modify your code one year later. You will be happy to retrieve yourself in what you wrote. It’s 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 WriteComments ()
'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.

Don’t hesitate to declare several variables on the same line and write an if loop on the same line when it is possible.

Sub IfLine ()
    
    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 see the number of rows, depending on the cursor position in the VBA editor.

Time measurement

Know the time consumed by your code allows you to know if your solution is optimal. To know it, you can use the time function of your machine. Calculate the difference between the time at the beginning and the end of your module.

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 don’t 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, even if the screen update is deactivated. It is better to work directly with the object.

Sub SetReferences()
    
    '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 the Value2 method in your code.

Sub UseValueTwo()
    
    '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 vbNullString. Write 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 the Application.WorksheetFunction

As much as possible, call the functions already existing in VBA. They will be more efficient than re-creating the methods on objects. You will save reflection time as well as lines. It is essential to structure your code well.

Sub UseWorksheetFunction()

    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) named the Zen of Python gives useful advice on development to provide a basic set of guidelines and standards to code in Python. Most of the advice can be applied for development in general, including the VBA language.

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