Common VBA Error Services

The section Exploring the matter includes a better-than-nothing approach with neither uses this Common VBA Error Services nor any other additional component - with a plea    f o r    using them all. The sections aim is to somehow bootstrap the matter, last but not least towards a professional solution. The section may be skipped when one is already convinced to go all the (professional) way.

The promise of this Common Component: Error messages like the one below:

with	details
Type of error	differentiating Application Error, VB Runtime error, and Database error)
Error Number	either the one raised by Err.Raise or the one raised by the system
Error Description	of the error (err.Description)
Error source	procedure which raised the error
Error path	see The path to the error for the required conditions
About	Optional additional info about an error** (when concatenated to the error description with `
Resume Error Line button	obligatory
Error line	when (pretty unlikely) available)

Services

Service	Kind 1	Description
Asserted	S	Only used with regression testing (mErH.Regression = True) to avoid the display of errors specifically tested. When Regression = False the Asserted service is ignored and any error is displayed.
BoP	S	Indicates the Begin of a Procedure and is used to maintain the call stack. Attention: When the service is about to be used by the Common VBA Execution Trace Service as well this service is exclusively to be called via an BoC Interface which is to be copied in each component when used.
EoP	S	Indicates the End of a Procedure and is used to maintain the call stack. Triggers the display of the Execution Trace when the end of the 'Entry-Procedure' is reached. Attention:
ErrMsg	F	Displays an error message either with the procedure the error had been raised (when no BoP statement had ever been executed) or displays it passed on the Entry Procedure (which is the first procedure with a BoP statement thereby assembling the-path- to-the-error displayed when the Entry Procedure is reached.
Regression	P w	When TRUE the ErrMsg only displays errors which are not regarded 'Asserted'.

Installation

1. Download mErH.bas
2. Import mErH
3. Activate the error services by the Conditional Compile Argument mErH = 1 in the Project properties.

Optional installation of the Common VBA Message Service

Not required but significantly improves the display of errors - thereby providing a message display service with less limits.

1. Download mMsg.bas, fMsg.frm, and fMsg.frx
2. Import mMsg, fMsg.frm to your VB-Project
3. Activate the message service by the Conditional Compile Argument mMsg = 1 in the Project properties.

Usage

Universal service interfaces

Begin/End of Procedure indication

The below procedures may be copied into any component and used in any procedure with an error message call. Alternatively, the Common VBA Basics Components may be installed and the procedure called mBasic.BoP / mBasic.EoP. The procedures function as a universal interface which keeps the use of the mErH and mTrc components optional. ²

Public Sub BoP(ByVal b_proc As String, _
      Optional ByVal b_args As String = vbNullString)
' ------------------------------------------------------------------------------
' Common 'Begin of Procedure' interface serving the 'Common VBA Error Services'
' and - if not installed/activated the 'Common VBA Execution Trace Service'.
' ------------------------------------------------------------------------------
#If mErH = 1 Then          ' serves the mTrc/clsTrc when installed and active
    mErH.BoP b_proc, b_args
#ElseIf clsTrc = 1 Then ' when only clsTrc is installed and active
    Trc.BoP b_proc, b_args
#ElseIf mTrc = 1 Then   ' when only mTrc is installed and activate
    mTrc.BoP b_proc, b_args
#End If
End Sub

Public Sub EoP(ByVal e_proc As String, _
      Optional ByVal e_args As String = vbNullString)
' ------------------------------------------------------------------------------
' Common 'End of Procedure' interface serving the 'Common VBA Error Services'
' and - if not installed/activated the 'Common VBA Execution Trace Service'.
' ------------------------------------------------------------------------------
#If mErH = 1 Then          ' serves the mTrc/clsTrc when installed and active
    mErH.EoP e_proc, e_args
#ElseIf clsTrc = 1 Then ' when only clsTrc is installed and active
    Trc.EoP e_proc, e_args
#ElseIf mTrc = 1 Then   ' when only mTrc is installed and activate
    mTrc.EoP e_proc, e_args
#End If
End Sub

Please not that is absolutely essential that BoP/EoP services are always called paired with identical arguments within a procedure (Sub, Function, Property)!

The universal ErrMsg interface

The below function may be copied into any component which uses at least one error message call. Alternatively, the Common VBA Basics Components may be installed and the procedure called mBasic.ErrMsg. It functions as a universal interface which keeps the use of the mErH and mMsg components optional while still providing a reasonable debugging option. ²

Private Function ErrMsg(ByVal err_source As String, _
              Optional ByVal err_no As Long = 0, _
              Optional ByVal err_dscrptn As String = vbNullString, _
              Optional ByVal err_line As Long = 0) As Variant
' ------------------------------------------------------------------------------
' Universal error message display service. Obligatory copy Private for any
' VB-Component using the common error service but not having the mBasic common
' component installed.
' Displays: - a debugging option
'           - an optional additional "About:" section when the err_dscrptn has
'             an additional string concatenated by two vertical bars (||)
'           - the error message by means of the Common VBA Message Service
'             (fMsg/mMsg) when installed and active (Cond. Comp. Arg.
'             `mMsg = 1`)
'
' Uses: AppErr  For programmed application errors (Err.Raise AppErr(n), ....)
'               to turn them into a negative and in the error message back into
'               its origin positive number.
'
' W. Rauschenberger Berlin, June 2023
' See: https://github.com/warbe-maker/VBA-Error
' ------------------------------------------------------------------------------
#If mErH = 1 Then
    '~~ When Common VBA Error Services (mErH) is availabel in the VB-Project
    '~~ (which includes the mMsg component) the mErh.ErrMsg service is invoked.
    ErrMsg = mErH.ErrMsg(err_source, err_no, err_dscrptn, err_line): GoTo xt
    GoTo xt
#ElseIf mMsg = 1 Then
    '~~ When (only) the Common Message Service (mMsg, fMsg) is available in the
    '~~ VB-Project, mMsg.ErrMsg is invoked for the display of the error message.
    ErrMsg = mMsg.ErrMsg(err_source, err_no, err_dscrptn, err_line): GoTo xt
    GoTo xt
#End If
    '~~ When neither of the Common Component is available in the VB-Project
    '~~ the error message is displayed by means of the VBA.MsgBox
    Dim ErrBttns    As Variant
    Dim ErrAtLine   As String
    Dim ErrDesc     As String
    Dim ErrLine     As Long
    Dim ErrNo       As Long
    Dim ErrSrc      As String
    Dim ErrText     As String
    Dim ErrTitle    As String
    Dim ErrType     As String
    Dim ErrAbout    As String
        
    '~~ Obtain error information from the Err object for any argument not provided
    If err_no = 0 Then err_no = Err.Number
    If err_line = 0 Then ErrLine = Erl
    If err_source = vbNullString Then err_source = Err.source
    If err_dscrptn = vbNullString Then err_dscrptn = Err.Description
    If err_dscrptn = vbNullString Then err_dscrptn = "--- No error description available ---"
    '~~ About
    ErrDesc = err_dscrptn
    If InStr(err_dscrptn, "||") <> 0 Then
        ErrDesc = Split(err_dscrptn, "||")(0)
        ErrAbout = Split(err_dscrptn, "||")(1)
    End If
    '~~ Type of error
    If err_no < 0 Then
        ErrType = "Application Error ": ErrNo = AppErr(err_no)
    Else
        ErrType = "VB Runtime Error ":  ErrNo = err_no
        If err_dscrptn Like "*DAO*" _
        Or err_dscrptn Like "*ODBC*" _
        Or err_dscrptn Like "*Oracle*" _
        Then ErrType = "Database Error "
    End If
    
    '~~ Title
    If err_source <> vbNullString Then ErrSrc = " in: """ & err_source & """"
    If err_line <> 0 Then ErrAtLine = " at line " & err_line
    ErrTitle = Replace(ErrType & ErrNo & ErrSrc & ErrAtLine, "  ", " ")
    '~~ Description
    ErrText = "Error: " & vbLf & ErrDesc
    '~~ About
    If ErrAbout <> vbNullString Then ErrText = ErrText & vbLf & vbLf & "About: " & vbLf & ErrAbout
    
    ErrBttns = vbYesNo
    ErrText = ErrText & vbLf & vbLf & "Debugging:" & vbLf & "Yes    = Resume Error Line" & vbLf & "No     = Terminate"
    ErrMsg = MsgBox(Title:=ErrTitle, Prompt:=ErrText, Buttons:=ErrBttns)
xt:
End Function

The ErrSrc function for distinct procedure names

Private Function ErrSrc(ByVal sProc As String) As String
    ErrSrc = "<the component's name>." & sProc
End Function

The AppErr function for distinct 'Application-Error-Numbers'

Private Function AppErr(ByVal app_err_no As Long) As Long
' ------------------------------------------------------------------------------
' Ensures that a programmed 'Application' error number not conflicts with the
' number of a 'VB Runtime Error' or any other system error. Returns a given 
' positive 'Application Error' number (app_err_no) as a negative by adding the
' system constant vbObjectError. Returns the original 'Application Error' 
' number when called with a negative error number.
' ------------------------------------------------------------------------------
    If app_err_no >= 0 Then AppErr = app_err_no + vbObjectError Else AppErr = Abs(app_err_no - vbObjectError)
End Function

Using the error service in any procedure

The following is the recommended coding scheme by which finding an error becomes as easy and quick as possible - provided the Debugging option is activated for the 'resume the code line which causes the error' support.

Private Sub TestProc()
    Const PROC = "TestProc"
    
    On Error GoTo eh
    BoP ErrSrc(PROC)
    '
    TestTestProc    ' this one will raise the error
    '
xt: EoP ErrSrc(PROC)
    Exit Sub

eh: Select Case ErrMsg(ErrSrc(PROC))
        Case vbResume:  Stop: Resume
        Case Else:      GoTo xt
    End Select
End Sub

The above may be tested with:

Private Sub TestTestProc()
    Const PROC = "TestTestProc"
    
    On Error GoTo eh
    Dim wb As Workbook
    
    BoP ErrSrc(PROC)
    Debug.Print wb.Name ' will raise a VB-Runtime error no 91

xt: EoP ErrSrc(PROC)
    Exit Sub

eh: Select Case ErrMsg(ErrSrc(PROC))
        Case vbResume:  Stop: Resume
        Case Else:      GoTo xt
    End Select
End Sub

When an error is displayed and the Resume Error Line button is pressed the following two F8 key strokes end up at the error line. When it is again executed without any change the same error message will pop-up again of course.

The Entry Procedure

The 'registration' of the Entry Procedure is crucial for the error display service. The Entry Procedure is the one which calls other procedures but itself is not called by a procedure - but by an event or via Application.Run instead for example. When these procedures have a BoP/EoP service call the the path-to-the-error may be complete provided the Debugging option is not activated.

The path to the error

Irrespective of the approach which assembles a "path-to-the-error": The knowledge of the involved procedure's name (see the better-than-nothing example is a key issue.

Approach	Description	Pro	Con
Bottom up	The path is assembled when the error is passed on up to the Entry Procedure.	This approach assembles the path provided the Entry Procedure is known, i.e. has BoP/EoP statements. The completeness of the path does not depend on other passed BoP/EoP code lines on the way down to the error raising procedure.	This approach is contradicted by the debugging ( Resume the Error Line ) option which is provided with each error message.
Top down	A call stack is maintained with each BoP/EoP service call.	The path-to-the-error is as complete as the passed BoP/EoP service calls on the the way down to the error raising procedure. This approach provides a complete path to the error regardless the fact that the error message is directly displayed within the error raising procedure (or by the last procedure passed which had an On Error Goto .. statement.	The completeness/extent of the path- to-the-error depends on the passed BoP/EoP statements.

Conclusion: Procedures with an error handling (those with On Error Goto eh) should also have BoP/EoP statements - quasi as a default). Providing potential_Entry Procedure_ with BoP/EoP statements should be obligatory.

Regression test

Activation

With mErH.Regression = True at the beginning of a series of test procedures the display of 'asserted' errors will be suspended thereby supporting an uninterrupted regression test.

Asserted error(s) service

Used with regression testing. Errors which a are regarded asserted because explicitly tested are not displayed in order not to interrupt the test procedure. Effective only when the Regression property is set to True. Example: When the tested error is a programmed Application Error (raised by: Err.Raise AppErr(1), ErrSrc(PROC), "error description") the error is asserted by mErH.Asserted AppErr(1).

Using the optional fMsg/mMsg components for a better designed error message

When the two components are installed and activated they are automatically used by the ErrMsg function to display the error message not only significantly better designed but also more clear for how to use the Debugging option. If desired follow the below steps.

1. Download fMsg.frm, fMsg.frx, mMsg.bas
2. Import into your VB-Project fMsg.frm and mMsg.bas
3. Activate them by the Cond. Comp. Arg. mMsg = 1

Download from public GitHub repo

It may appear pretty strange when downloading first from a public GitHub repo but is is quite straight forward as the below image shows.

Exploring the matter

Used Common Components

The best possible result (last example) requires the additional Common VBA Message Service. The difference between using and not using it shown below.

ErrMsg 3	mErH	mMsg	Example result	Error path	Resume caption	Lay- out
yes	no	no		no	no	no
yes	no	yes		no	yes	yes
yes	yes	no		yes	no	no
yes	yes	yes		yes	yes	yes

A minimum, popularly "better-than-nothing", approach

The below code provides the maximum regarding debugging with a minimum of effort and means - with the following properties, together with a plea for possible improvements ⁴

Property	Provided	Comment (the possible improvement)
Error number and description	Yes	Err.Number and Err.Description provides the info.
Error source	Yes	A Const PROC = "...." statement is used to identify the procedures name and the ErrSrc function is used to prefix it with the component's name.
Application error number	No	A "kind-of-error" distinction is provided by the more elaborated "universal" ErrMsg function which uses the AppErr for this.
(proceed to) Error line	Yes	By using the fMsg/mMsg components buttons may be named precisely for what they mean
Info "about" error	No	Requires a more elaborated error message like the ErrMsg function
Path to the error	No	The display of a "path-to-the-error" is provided by the Common VBA Error Services component mErH which maintains a call stack and a back-up collection of the passed procedures.
Pre-asserted errors	No	The Common VBA Error Services component mErH provides a 'Regression-Test' option which suppresses the display of 'asserted', i.e. explicitly tested, errors.
Execution Trace	No	Only when the Common VBA Execution Trace service (clsTrc or mTrc component) is installed and activated the Common VBA Error Services component mErH passes on BoP/EoP statements 'by the way' supporting an execution trace.
Debugging option	Yes	The much more elaborated Common VBA Message Service provides an optimum display of the involved buttons.

Private Sub Demo()
    Demo_a 10, 0
    MsgBox "Execution continued since the error has been ignored!"
End Sub

Private Sub Demo_a(ByVal d_a As Long, _
                   ByVal d_b As Long)
    Const PROC = "Demo_a"
    
    On Error GoTo eh
    Debug.Assert d_a / d_b

xt: Exit Sub

eh: Select Case ErrMsg(ErrSrc(PROC))
        Case vbRetry:   Stop: Resume
        Case vbAbort:   GoTo xt
    End Select
End Sub

Private Function ErrMsg(ByVal err_src As String) As Variant
' ----------------------------------------------------------------------------
' Universal error message, providing a debugging option.
' Note: Since the possible Buttons arguments are limitied the used argument
'       is a "best of" choice.
' ----------------------------------------------------------------------------
    ErrMsg = MsgBox(Title:="An error occoured in " & err_src & "!" _
                    , Prompt:="Error " & Err.Number & ": " & Err.Description & vbLf & vbLf & _
                              "Retry  = Proceed to the error line option" & vbLf & _
                              "Cancel = Proceed to the end of the error causing procedure." _
                    , Buttons:=vbRetryCancel)
End Function

Private Function ErrSrc(ByVal sProc As String) As String
    ErrSrc = "mDemo." & sProc
End Function

Other

Conflict management

This Common Component has become a standard in all my VB-Projects. However, it is optional with all my Common Components in order to keep them autonomous and not depending on other components. See Conflicts with personal and public Common Components for more details.

Contribution, development, test, maintenance

Any contribution of any kind will be welcome. The dedicated Common VBA Component Workbook ErH.xlsm is used for development, maintenance, and last but not least for the testing.

Footnotes

1. S=Sub, F=Function, P=Property (r=read/Get, w=write/Let) ↩

2. All (my) Common Components make use of these Private procedures in order to keep the autonomous. I.e. they are independent from any other component but will use them when installed and activated. ↩ ↩²

3. The ErrMsg function is the universal interface which allows to keep the mErH component and the Common VBA Message Service optional while still providing a debugging option when neither is installed/activated. These procedures may also be copied from the mBasic.bas component as Private function (as used within all Common Components) or used directly when downloaded and imported. ↩

4. All my (public) Common Components provide a built-in README service which displays the component's corresponding README in the public GitHub repo. ↩