5 best practices to make your VBA macro great

21 May, 2020

Although VBA can be considered as an obsolete technology, VBA macros are still widely used across millions of organizations in the world. VBA Engine is part of several office applications such as MS Word and MS Excel, most popular CAD applications, such as SOLIDWORKS, Autodesk Inventor, etc.

VBA macros are small applications and need to be considered as such. It means macro stability, reliability, performance, maintainability relies on the proper programming practices applied.

Following MS Excel macro allows to rename (move) files based on the data in 2 columns, where the cells of the first column are an input file paths and the cells of the second column are their respective destinations. It will be used as an example and will be improved step-by-step to demonstrate how the best practices, based on Best Practices for developing VBA applications can be applied.

Dim xRange As Excel.Range

Sub RenameFiles()
        
    On Error Resume Next
    
    Set xRange = Application.Selection
    
    ProcessRange
    
End Sub

Sub ProcessRange()

    Dim i As Integer
    
    For i = 1 To xRange.Rows.Count
        
        Dim xRow As Excel.Range
        Set xRow = xRange.Rows(i)
                
        RenameFile xRow.Cells(, 1), xRow.Cells(, 2)
        
    Next
    
End Sub

Sub RenameFile(cell1 As Range, cell2 As Range)
    
    'create directories
    Dim fileDir As String
    fileDir = Left(cell2.Value, InStrRev(cell2.Value, "\") - 1)
    
    Dim pathParts As Variant
    pathParts = Split(fileDir, "\")
    
    Dim i As Integer
    Dim curPath As String
    
    For i = 0 To UBound(pathParts)
        curPath = curPath & pathParts(i) & "\"
        If Len(Dir(curPath, vbDirectory)) = 0 Then
            MkDir curPath
        End If
    Next
    
    Name cell1.Value As cell2.Value
    
End Sub

The above macro will be used to reorganize the invoice files below

Those files use the specific naming convention in the following format: YYYY-MM-dd_Number.xml and need to be placed in the corresponding folders by date. So the result would look like this:

The video below demonstrates how the original macro can be improved and what benefits those improvements provide. Although Excel VBA macro is used in the demonstration, the same practices could be applied to any other VBA macro or application (i.e. SOLIDWORKS VBA Macro, Autodesk Inventor VBA Macro, MS Word VBA Macro etc.).

Below are the summary bullet points based on the above video demonstration.

As a developer you need to aim to create applications which not only can be used by the user without your help but can be troubleshot by themselves

Hiding Of Errors

On Error Resume Next declaration in the main function would effectively swallow all the exceptions raised in the macro and will provide no feedback to the user.

Sub RenameFiles()
        
    On Error Resume Next

To address this issue

Use 'fail fast' approach
Do not hide errors
Catch and process errors from centralized function

Sub RenameFiles()
    
try_:
        
    On Error GoTo catch_
    
    Set xRange = Application.Selection
    
    ProcessRange
    
    GoTo finally_
catch_:
    MsgBox Err.Description, vbOKOnly
finally_:
    
End Sub

Apply The Version Control To The Code

Most VBA macros would be stored in the binary format which makes it unpractical to use macros in the version control systems like GIT or SVN. But it is still extremely important to revision the code to be able to track changes and regression issues. It is recommended to copy the source code of the macro in the text format and add this to version control.

Use the version control for the macro code
Commit changes regularly
Do not commit test code to the main branch

Use Parameterless Functions For Entry Points

By default, all functions in the VBA macro without parameters (parameterless) would be considered as the macro entry point candidate. This means that the macro user can potentially run any function as the start function which could lead to an unpredictable result.

To prevent this, add parameters to any but entry point function. If the parameter is not required, add optional dummy parameter, so none of the code calling this function needs to be changed, but it won't be considered as entry point anymore:

Sub ProcessRange(Optional dummy As Variant = Empty)

Reduce Dependencies Between Functions And Module Variables

ProcessRange function depends on xRange variable declared in the module. Instead, it should expect this as the parameter.

Sub ProcessRange(xRange As Excel.Range)

RenameFile function can be separated from the Excel context and provide generic functionality for renaming files.

Sub RenameFile(srcFilePath As String, destFilePath As String)

Keep functions independent from global variables and environments
Try not to mix different responsibilities within one function
Use descriptive names for functions, variables, and modules

Documentation

Consider adding the header to your macro which will show the author of the macro, contact details, license and brief description of the functionality

'-------------------------------------------------------------------------------------------------------------------------------------
'Created By: Artem Taturevych (info@xarial.com)
'Macro renames files using the cell values in the first column as the source and the cell values in the second column as the target
'Macro will automatically create directories if not exist
'-------------------------------------------------------------------------------------------------------------------------------------

Sub RenameFiles()

Final Macro

Below is a code for the final version of the macro after all the practices have been applied.

'-------------------------------------------------------------------------------------------------------------------------------------
'Created By: Artem Taturevych (info@xarial.com)
'Macro renames files using the cell values in the first column as the source and the cell values in the second column as the target
'Macro will automatically create directories if not exist
'-------------------------------------------------------------------------------------------------------------------------------------

Sub RenameFiles()
    
try_:
        
    On Error GoTo catch_
    
    Dim xRange As Excel.Range
    
    Set xRange = Application.Selection
    
    RenameFilesInRange xRange
    
    GoTo finally_
catch_:
    MsgBox Err.Description, vbOKOnly
finally_:
    
End Sub

Sub RenameFilesInRange(xRange As Excel.Range)

    Dim i As Integer
    
    If xRange.Columns.Count <> 2 Then
        Err.Raise vbError, "", "Please select 2 columns: source and destination"
    End If
    
    For i = 1 To xRange.Rows.Count
        
        Dim xRow As Excel.Range
        Set xRow = xRange.Rows(i)
                
        RenameFile xRow.Cells(, 1).Value, xRow.Cells(, 2).Value
        
    Next
    
End Sub

Sub RenameFile(srcFilePath As String, destFilePath As String)
    
    On Error GoTo err_
    
    Dim fileDir As String
    fileDir = Left(destFilePath, InStrRev(destFilePath, "\") - 1)
    
    CreateDirectories fileDir
    
    Name srcFilePath As destFilePath
    
    GoTo exit_
    
err_:
    Err.Raise vbError, "", "Failed to copy " & srcFilePath & " to " & destFilePath
exit_:

End Sub

Sub CreateDirectories(fileDir As String)
    
    Dim pathParts As Variant
    pathParts = Split(fileDir, "\")
    
    Dim i As Integer
    Dim curPath As String
    
    For i = 0 To UBound(pathParts)
        curPath = curPath & pathParts(i) & "\"
        If Len(Dir(curPath, vbDirectory)) = 0 Then
            MkDir curPath
        End If
    Next
    
End Sub