Utility for inserting VBA Error Handler Code

Introduction

So far, we have explored several examples of working with the VBA Module object’s properties and methods. We learned how to insert a subroutine into a form module using the InsertLines() method, how to upload VBA code from a text file into a form module with the AddFromFile() method, and how to generate a list of functions and subroutines from a specific module programmatically.

Now, we’ll move on to creating a very practical utility program that can automatically insert standard error-handling code into VBA functions and subroutines. Before we begin, let’s first look at a typical error-trap routine that we usually add to a subroutine. Such a routine helps us manage unexpected errors and ensures the program exits gracefully—without abruptly halting execution or inconveniencing the user during normal operations.

Sample Error Handler.

Private Sub cmdRunReport_Click()
On Error Goto cmdRunReport_Click_Error
.
.
.
.
cmdRunReport_Click_Exit:
Exit Sub

cmdRunReport_Click_Error:
MsgBox Err & " : " & Err.Description,,"cmdRunReport_Click()"
Resume cmdRunReport_Click_Exit

End Sub

The blue-colored lines in the earlier example represent the error handler code, while the dotted section holds the actual logic of the procedure. Normally, as developers, we focus first on writing the core logic of the program (the dotted section), and only later—during the finishing stage—do we add the error-handling routines. In some cases, such as file-handling programs or procedures with heavy validation checks, setting up error-trap routines gets higher priority right from the start.

Our goal here is to automate the insertion of error-handling lines at both the beginning and the end of a procedure. Every serious program benefits from structured error handling, but writing these lines manually for each subroutine or function can be time-consuming. If some of your existing code lacks proper error-handling routines, you can easily fix that now using the utility program we are about to build.

Notice in the example that lines such as _Exit: and _Error: are suffixed with the program name, e.g., cmdRunReport_Click_Exit:. These labels are automatically derived from the subroutine or function name. The first error-handling line will be inserted immediately after the procedure declaration line (e.g., Sub … or Function …), while the rest of the error-handling block will be placed at the end of the procedure.

To achieve this, we first need to extract certain details about the procedure itself:

• The starting line number of the procedure.

• The ending line number of the procedure.

• The procedure name.

With these details, we can then insert the error-trap lines in the right places. Fortunately, VBA exposes all this information through the Module object, which provides access to the required property values of any line of code within a function or subroutine.

A plan to find the specific location in the Module.

To make the approach clearer, let’s outline a simple plan for our program:

1. Locate a unique text string inside the VBA module, somewhere within the target Function or Subroutine.

   • For this step, we will use the Find() method of the Module object.

2. Identify procedure details once the search lands on the desired line inside the Function or Subroutine.

   • The Find() method not only stops on the target line but also provides important information, such as:

     • The line number of the match (modules are internally numbered line by line, including blank lines).

     • The starting column number where the search text is found.

     • The ending column number where the search text ends.

With this information in hand, we can determine the exact position of the procedure within the module, which is essential for inserting our error-handling lines at the right places.

The syntax of the Find() Method is as follows:

1. Modules(ModuleName).Find strSearchText, lngStart_Line, lngStart_Column, lngEnd_line, lngEnd_Column, [[WholeWord], [MatchCase], [PatternSearch]]

   Sample Code:

   Set mdl = Modules("Form_Employees")
   
   With mdl
     .Find “myReport”, lngStart_Line, lngStart_Column, lngEnd_line, lngEnd_Column, False
   End With

• The first parameter of the Find() method is simply the search text you want to locate.

  The next four parameters define where to begin and end the search:

  • StartLine → the line number where the search begins.

  • StartColumn → the column position on the start line.

  • EndLine → the line number where the search ends.

  • EndColumn → the column position on the end line.

  For example, suppose you want to locate the second occurrence of the text "myReport" that appears somewhere after line 25 in the module. In that case, you might set:

  lngStart_Line = 25
  lngStart_Column = 0   ' start from the beginning of the line
  

  If "myReport" is part of a command like:

  DoCmd.OpenReport "myReport"
  

  then setting lngStart_Column = 10 would be sufficient, but leaving it at 0 is perfectly fine.

  Once the search text is located, the Find() method automatically updates all four parameters with the actual positions of the match. In other words, you’ll get:

  • lngStart_Line → the line number where the match begins.

  • lngStart_Column → the exact column where the match starts.

  • lngEnd_Line → the line number where the match ends.

  • lngEnd_Column → the exact column where the match ends.

  This way, the method not only confirms the presence of the search text but also tells you exactly where it sits in the module — which is critical for inserting error-handling code at the right spots.

• When the search operation is successful, we can extract information related to that program line to use for working within that particular Function or subroutine. We will read the following information about a particular program to insert the Error Handler lines of Code at appropriate locations in the Program:

  • Get the Program Name from the ProcOfLine Property (or in the expanded form Procedure name of the Line we found through search) of the program line.

  • Get the Procedure Body Line Number from the ProcBodyLine Property. The line number on which the program Private Sub cmdRunReport_Click() starts. This line number + 1 is the location where we can insert the first line (On Error Goto label statement) of the Error Handler.

  • Get the Number of Lines in this particular procedure, we are in, from the ProcCountLines Property.  Even though this is a piece of useful information, this has some drawbacks.  If there are blank lines above the procedure Name or below the End Sub or End Function line (if it is the last procedure in a Module, then it can have blank lines at the end), they are also included in the count.  So we must take corrective action or take alternative measures to correct the values.

• Once the above information is available, we can write the Error Handler lines into String Variables and use the InsertLines method of the Module Object to place them at the beginning and the end of the procedure.

The ErrorHandler() Function.

1. Open the VBA Editing Window (ALT+F11).

2. Insert a new Standard Module.

3. Copy and paste the following VBA Code into the Module and save it:

Public Function ErrorHandler(ByVal strModuleName As String, _
                                ByVal strSearchText As String, _
                                Optional ByVal lng_StartLine As Long = 1)
On Error GoTo ErrorHandler_Error
'--------------------------------------------------------------------------------
'Program : Inserting Error Handler Lines automatically
'        : in VBA Functions or Sub-Routines
'Author  : a.p.r. pillai
'Date    : December, 2011
'Remarks : All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------------------------
'Parameter List:
'1. strModuleName - Standard Module or Form/Report Module Name
'2. strSearchText - Text to search for within a
'   Function or Sub-Routine
'3. lng_StartLine - Text Search Start line Number, default=1
‘Remarks: Standard/Form/Report Module must be kept open before running this Code
'--------------------------------------------------------------------------------
Dim mdl As Module, lng_startCol As Long
Dim lng_endLine As Long, lng_endCol As Long, x As Boolean, w As Boolean
Dim ProcName As String, lngProcLastLine As Long
Dim ErrTrapStartLine As String, ErrHandler As String
Dim sline As Long, scol As Long, eline As Long, ecol As Long
Dim lngProcBodyLine As Long, lngProcLineCount As Long
Dim lngProcStartLine As Long, start_line As Long, end_line As Long

Set mdl = Modules(strModuleName)
lng_startCol = 1
lng_endLine = mdl.CountOfLines
lng_endCol = 255

With mdl
    .Find strSearchText, lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False
End With

'lng_StartLine - line number where the text is found
'lng_StartCol  -  starting column where the text starts
'lng_EndCol    - is where the search text ends
'lng_EndLine   - end line where the text search to stop
'if search-text is found then lng_StartLine and lng_EndLine will
'point to the same line where the search-text is found
'otherwise both will be zero

If lng_StartLine > 1 Then
  'Get Procedure Name.
  'The vbext_pk_Proc system constant
  'dictates to look within a Function or Sub Routine
  'Not to consider Property-Let/Get etc.
   ProcName = mdl.ProcOfLine(lng_endLine, vbext_pk_Proc)
   
   'Get Procedure Body Line Number
   lngProcBodyLine = mdl.ProcBodyLine(ProcName, vbext_pk_Proc)
   
   'Look for existing Error trap routine, if any
   'if found abort the program
   sline = lngProcBodyLine: scol = 1: ecol = 100: eline = lng_endLine
   x = mdl.Find("On Error", sline, scol, eline, ecol)
   If x Then
      MsgBox "Error Handler already assigned, program aborted"
      Exit Function
   End If
   
 'Get Line Count of the Procedure, including
 ' blank lines immediately above the procedure name
 'and below, if the procedure is the last one in the Module
   lngProcLineCount = mdl.ProcCountLines(ProcName, vbext_pk_Proc)
   
 'Create Error Trap start line
   ErrTrapStartLine = "On Error goto " & ProcName & "_Error" & vbCr
 'Compose Error Handler lines
   ErrHandler = vbCr & ProcName & "_Exit:" & vbCr

'determine whether it is a Function procedure or a Sub-Routine
'lng_StartLine = lng_endLine:
lng_startCol = 1: lng_endCol = 100: lng_endLine = lngProcBodyLine + lngProcLineCount
'save the startline and lng_EndLine values
start_line = lng_StartLine: end_line = lng_endLine

'Check whether it is a Function Procedure or a Sub-Routine
w = mdl.Find("End Function", lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False)

If w Then 'Function Procedure
   'Take correct procedure line count excluding
   'blank lines below End Sub or End Function line
   lngProcLineCount = lng_StartLine
   ErrHandler = ErrHandler & "Exit Function" & vbCr & vbCr
Else
   lng_StartLine = start_line: lng_endLine = end_line: lng_startCol = 1: lng_endCol = 100
   w = mdl.Find("End Sub", lng_StartLine, lng_startCol, lng_endLine, lng_endCol, False)
   If w Then 'Sub-Routine
     lngProcLineCount = lng_StartLine
     ErrHandler = ErrHandler & "Exit Sub" & vbCr & vbCr
   End If
End If
   'create Error Handler lines
   ErrHandler = ErrHandler & ProcName & "_Error:" & vbCr
   ErrHandler = ErrHandler & "MsgBox Err & " & Chr$(34) & " : " & Chr$(34) & " & "
   ErrHandler = ErrHandler & "Err.Description,," & Chr$(34) & ProcName & "()" & Chr$(34) & vbCr
   ErrHandler = ErrHandler & "Resume " & ProcName & "_exit"
 
  'Insert the Error catch start line immediately below the header line
   mdl.InsertLines lngProcBodyLine + 1, ErrTrapStartLine
   
 'Insert the Error Handler lines at the bottom of the Procedure
 'immediately above the 'End Function' or 'End Sub' line
   mdl.InsertLines lngProcLineCount + 2, ErrHandler
   
End If

ErrorHandler_Exit:
Exit Function

ErrorHandler_Error:
MsgBox Err & " : " & Err.Description, , "ErrorHandler()"
Resume ErrorHandler_Exit

End Function

Running the Code.

Since this program itself is a Coding aide, you must keep the target Module (Standard/Form/Report) open before running this program to insert the error-handling code segment into the target Function/subroutine.

You may call the ErrorHandler() Function from the Debug Window or from a Command Button Click Event Procedure as shown below:

'The third parameter is optional, you may omit it
ErrorHandler "Form_Employees","myReport",1

This will start searching for the text myReport from the beginning of the Employees Form Module, stop within the program where the search finds a text match, and insert the Error Handling Code lines at the beginning and end of the program.

If the text 'myReport' appears in more than one Function/Sub-Routine in the Module, then you must give the third parameter (Search starts line number) to start searching for the text beyond the area where exclusion is required. Example:

'Look for text 'myReport' from line 20 onwards only 
ErrorHandler "Form_Employees","myReport",20

When the ErrorHandler() Function is run, first, it will look for the presence of existing error handling lines starting with 'On Error', and if found, assumes that the error handling lines are already present in the Function/Sub-Routine and stops the program after displaying the following message:

‘Error Handler already assigned, program aborted.'

Comment lines are added for clarity above the program lines, explaining what happens next. 

If you find any logical errors in the program, please give me feedback through the comment section of this page. To protect from spam, we insist on joining the site before you are able to post comments.

Technorati Tags: Utility, Microsoft
Key Words: Error, Function, Sub-Routine, Form, Module

Earlier Post Link References:


• Change the Query Top Values property with VBA
• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page 2 of Report
• Product Group Sequence with Autonumbers