Utility for inserting VBA Error Handler Code

Introduction

We have tried a few examples of working with VBA Module Object Properties and Methods. We have learned how to insert a Sub-Routine into a Form Module with expression.InsertLines() method of Module Object with Program. We have also seen how to upload VBA Programs from a Text File into a Form Module with expression.AddFromFile() method of Module Object. We have prepared a list of Functions and Sub-routines from a particular Module with the program.

Now, we are going to write a very useful Utility Program for inserting general Error Handler Code Lines into VBA Functions or Sub-Routines automatically. Let us take a look at the Error Trap Routine that we normally write in a Sub-Routine to take care of unexpected errors and to exit the program gracefully, without stopping the code and giving trouble to the User in the middle of 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 are the Error Handler lines and the dotted area will hold the actual program.  Normally, we concentrate on writing code for the actual action we intended to execute within the procedure (the dotted line area) and the Error Handler part can wait for the later finishing touches stage.  File handling programs or areas where validation checks are performed gets more attention in setting up error trap routines.

Our idea is to insert the Error Handler lines at the beginning and end of the program automatically.  Any serious program that you will write needs these lines of code and writing them manually everywhere will take some of your valuable time in a busy schedule.  If you left out some of your programs without adding the Error Handler lines earlier, you can add them now with the Utility Program that we are going to write.

As you can see in the above code the lines suffixed with _Exit:, _Error: etc., have the program name attached to them like cmdRunReport_Click_Exit: and these values are taken from the Sub-Routine or Function Names.  The first line of the error handler will be inserted immediately after the Program Name and other lines at the end of the Program.  So we must know a few details about the program before we are able to insert the Error Trap Lines into appropriate locations in the program.  For that, we must address a few Property Values of any line of code located within the Function or Sub-Routine and get the property values from the Module Object.

A plan to find the specific location in the Module.

To make it more clear, let us draw out a plan for our program as below:

1. First, search for some unique text within the VBA Module, located within our target Function or Sub-Routine.  For this, we can use the Find() method of the Module Object.

2. Once the search stops on the target line within the Function or Sub-Routine we can read several details of the program we are in now.  The Find() method not only finds the target line of our program with the search text, but also the program line number within the Module (all the lines within a Module are sequentially numbered including blank lines), the Column Number at which the search text starts, which column the search text ends, etc.  The Find() method Syntax is as given below:

   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 is the search text to find.

• The next four parameters tell where to start and where to stop looking for the search text.  For example, if you want to search for the second occurrence of the text “myReport” located somewhere beyond line number 25 then you will set lngStart_Line=25.  If “myReport” is in Docmd.OpenReport “myReport” then the lngStart_Column value can be about 10 or leave it at 0 to start searching from the beginning of the line.  Once the search text is located by the Find method all four variables will be loaded with the search text-related values as below:

  • The lngStart_Line = line number on which the search text is located.

  • lngStart_Column = Column Number (or the first character of the search text starts on which character position from left)

  • lngEnd_Line = Line on which the Search Text found and search stopped.

  • lngEnd_Column = Column on which the search text ends.

• Once the search text is located on a line the lngStart_Line and lngEnd_line will refer to the same program line on which the search text is located.  The start and end column values will be loaded into the lngStart_Column and lngEnd_Column variables.

• When the search operation is successful, we can extract information related to that program line to use for working within that particular Function or Sub-Routine. We will read the following information of 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 Error Handler.

  • Get the Number of Lines in this particular procedure, we are in, from 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 take correct 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/Sub-Routine.

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, stops within the program where the search finds a text match and inserts 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 wherever 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 error 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 Query Top Values property with VBA
• Creating Animated Command Buttons
• Data Change Monitoring
• Continued on Page-2 of Report
• Product Group Sequence with Autonumbers