Share via

VBA: Error Message Example for Visual Basic for Applications

  • Article

In a procedure, use the following structure for error trapping:

Option Explicit

Private Const ModuleName   As String = "Assembly_Info"

Public Sub ProcedureNameHere()
On Error GoTo ErrTrap
Const ProcedureName As String = "ProcedureNameHere"

      'detail code here 

ExitProcedure:
    On Error Resume Next
    Exit Sub

ErrTrap:
    Select Case Err.number
        Case Else
            Error_Handler.DisplayMessage ModuleName, ProcedureName, Err.number, Err.description
    End Select
    Resume ExitProcedure
    Resume 'for debugging

End Sub

In a module, enter the following:

Option Explicit

Private Const ModuleName As String = "Error_Handler"

Public Sub DisplayMessage( _
  ByVal module As String _
, ByVal procedure As String _
, ByVal number As Double _
, ByVal description As String _
, Optional ByVal line As Variant = 0 _
, Optional ByVal title As String = "Unexpected Error")
On Error Resume Next
Const ProcedureName As String = "DisplayMessage"
Dim msg             As String

    msg = "Contact your system administrator." & vbCrLf
    msg = msg & vbCrLf & "Module: " & module
    msg = msg & vbCrLf & "Procedure: " & procedure
    msg = msg & IIf(line = 0, "", vbCrLf & "Error Line: " & line)
    msg = msg & vbCrLf & "Error #: " & number
    msg = msg & vbCrLf & "Error Description: " & description

   'Dim x As Integer: x = 1 / 0 'use to test error message

If Err.number Then msg = msg & vbCrLf & vbCrLf & "An error occurred during building the message. Some information may be missing. A restart of the application is strongly recommended." End If MsgBox msg, vbCritical, title End Sub