Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Utility for inserting VBA Error Handler Code


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
Exit Sub

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
       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
    Exit Function
    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:

    Earlier Post Link References:



  1. Hi a.p.r.
    you've done a great job there :-) but it's a bit cumbersome to use, couldn't you modify the function so that it simply searches through the mdb/accdb from where it's launched and inserts the error handler lines in all the procedures that don't have "On Error goto" in them?

  2. Hi grovelli,

    Thanks for the idea. Sure we will do that. I will come out with a modified function shortly.

    Thanks again.


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code