Error Message Example for Visual Basic for Applications (VBA)

In a procedure error trap use the following:

Public Sub ProcedureNameHere()
On Error GoTo ErrTrap

  'detail code here

ExitProcedure:
    On Error Resume Next
    Exit Sub
    
ErrTrap:
    Select Case err.Number
        Case Is <> 0
            Call DisplayErrorMessage("ProcedureNameHere", "ModuleNameHere", err.Number, err.Description)
            Resume ExitProcedure
        Case Else
            Resume ExitProcedure
    End Select
    
End Sub

In a module use the following:

Public Sub DisplayErrorMessage( _
ByVal procedure As String, _
ByVal module As String, _
ByVal errNbr As Double, _
ByVal errDes As String, _
Optional ByVal errLine As Variant = 0, _
Optional ByVal title As String = "Unexpected Error")
'------------------------------------------------------------------------------------------------
' Purpose:  Global error message for all procedures
' Example:  Call ErrorMsg("Module", "Procedure", 101, "descr", 1, "Error Description")
'------------------------------------------------------------------------------------------------
On Error Resume Next
Dim msg As String

    msg = "Contact your system administrator." & vbCrLf
    msg = msg & "Module: " & module & vbCrLf
    msg = msg & "Procedure: " & procedure & vbCrLf
    msg = msg & IIf(errLine = 0, "", "Error Line: " & errLine & vbCrLf)
    msg = msg & "Error #: " & errNbr & vbCrLf
    msg = msg & "Error Description: " & errDes & vbCrLf
    MsgBox msg, vbCritical, title

End Sub