Return to Question

ie Err.Clear removes an "On Error Goto" that is currently in place. So therefore it is often best to use:

On Error Goto -1   
then follow it with either 
On error Goto 0       ' If you want no error handler to be active
or 
On Error Goto MyErrorHandlerLabel  ' probably best!

ie Err.Clear removes an "On Error Goto" that is currently in place. So therefore it is mostly best to use:

On Error Goto -1   

as using Err.clear You would often need to write

Err.Clear
On Error Goto MyErrorHandlerLabel

1. When you use on error goto Label1 the procedure enters into a state of "I'm handling an error" as an exception has been raised. When it is in this state, if another "On Error Goto" label2 statement is executed it will NOT goto label2, but raises and error which is passed to the code that called the procedure.

2. You can stop a procedure being in the "I'm handling an error" state by clearing the exception (setting err to nothing) by using

    Err.clear
   

   or

    On Error Goto -1    ' Which I think is less clear!
   

1. When you use on error goto Label1 the procedure enters into a state of "I'm handling an error" as an exception has been raised. When it is in this state, if another "On Error Goto" label2 statement is executed it will NOT goto label2, but raises and error which is passed to the code that called the procedure.

2. You can stop a procedure being in the "I'm handling an error" state by clearing the exception (setting err to nothing so the err.number property becomes 0) by using

    Err.clear
    or
   
    On Error Goto -1    ' Which I think is less clear!
   

Also important to note is that Err.Clear resets it to zero but it is actually equivalent to:

On Error Goto -1 
On Error Goto 0

ie Err.Clear removes an "On Error Goto" that is currently in place. So therefore it is often best to use:

On Error Goto -1   
then follow it with either 
On error Goto 0       ' If you want no error handler to be active
or 
On Error Goto MyErrorHandlerLabel  ' probably best!

The code below is pretty self explanatory: just copy and paste it all into a module. (It works but I'm interested to know what other people make of it and for any suggestions you might like to make.)

1. When you use on error goto Label1 the procedure enters into a state of "I'm handling an error" as an exception has been raised. When it is in this state, if another "On Error Goto" label2 statement is executed it will NOT goto label2, but raises and error which is passed to the code that called the procedure.

2. You can stop a procedure being in the "I'm handling an error" state by clearing the exception (setting err to nothing) by using

    On Error Goto -1
   

I use the above techniques with various labels to simulate the sometimes useful functionality that VB try catch blocks give, which I think have their place in writing readable code.

Here's some code that demonstrates this:

Option Compare Database
Option Explicit

Dim RememberErrNumber As Long
Dim RememberErrDescription As String
Dim RememberErrSource As String
Dim RememberErrLine  As Integer

Public Sub CopyErrorObjectValues()

    ' This function has to be declared in the same scope as the variables it refers to
    RememberErrNumber = Err.Number
    RememberErrDescription = Err.Description
    RememberErrSource = Err.Source
    RememberErrLine = Erl()
    
End Sub


Sub ExampleOfTryCatchBlockInVBA()

    ' -----------------------------------------------------
    ' SubProcedure1 has the Example of a multiple line TRY block with a Case statement used to CATCH the error
    
    On Error GoTo HandleError
    
    SubProcedure1

Exit Sub
HandleError:

    Select Case Err.Number
        Case 0
            ' This shold never happen as this code isan error handler!
            ' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
            ' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
            
        Case 111111
            ' You might watch to do special error handling for some predicted error numbers
            ' perhaps exit sub
            ' Perhaps using the Err.raise below
    End Select
    
    ' ie Otherwise
    '
    ' Note that I use the Err.Source to maintain a call stack of procedure names
    '
    Err.Raise Err.Number _
            , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
            , Err.Number & "-" & Err.Description
            
    ' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
    ' (By moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
    Resume
    
End Sub

Sub SubProcedure1()

    ' -----------------------------------------------------
    ' Example of a multiple line TRY block with a Case statement used to CATCH the error
    
    '
    ' It is sometimes better to NOT use this technique but to put the code in it's own procedure
    ' (ie I refer to the code below that has the comment #OWNSUB) .
    ' However,sometimes using this technique makes code more readable or simpler!
    '
    
    Dim i As Integer
    
' This next line causes execution to "jump" to the "catch" block in the event an error is detected.
On Error GoTo StartCatchBlock1
    
    tsub_WillNotRaiseError_JustPrintsOk   ' #OWNSUB
    
    If vbYes = MsgBox("Do you want to raise an error?", vbYesNo) Then
        i = 100 / 0                           ' #OWNSUB
    End If
    
    tsub_WillNotRaiseError_JustPrintsOk   ' #OWNSUB
    
    tsub_WillNotRaiseError_JustPrintsOk   ' #OWNSUB
    
    
GoTo EndCatchBlock1
StartCatchBlock1:

    ' For demo purposes
    Debug.Print "ERROR RAISED"
    Debug.Print Err.Number
    Debug.Print Err.Description
    Debug.Print Err.Source
    Debug.Print " "
    
    ' This saves the details of the error in variables
    ' to prevent the err object having it's values changed by any code that might execute in the "error handler"
    ' prior to the error being re-raised
    
    CopyErrorObjectValues

' "On Error GoTo -1 " is used to "end the error handler"
' If this is not used then the next "On Error GoTo HandleError"   below      (tagged with ' #THISLINE#)
' will actually NOT pass execution to the HandleError: label BUT the precedure that called this is passed the error.
' Using "On Error GoTo -1 "  gets around this and facilitates the whole TRY CATCH block scenario I am using there.

On Error GoTo -1 ' Disables enabled exception in the current procedure and resets it to Nothing.
 
    ' For demo purposes
    Debug.Print "ERROR RAISED is now 0 "
    Debug.Print Err.Number
    Debug.Print Err.Description
    Debug.Print Err.Source
    Debug.Print " "
    
    Debug.Print "REMEMBERED AS"
    Debug.Print RememberErrNumber
    Debug.Print RememberErrDescription
    Debug.Print RememberErrSource
    Debug.Print " "
 
        
    Select Case RememberErrNumber
        Case 0:  ' No Error, do Nothing
        Case 2517
            Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
            
         Case Else
            ' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised

            ' Note that the next line will reset the error object to 0, which is why the variables above are used to remember the values
            On Error GoTo HandleError   '#THISLINE#
            
            ' This line must be preceeded by an new "On error goto" for obvious reasons
            Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
    
     End Select
            
EndCatchBlock1:
On Error GoTo HandleError
    
'
' Here you could add lots of lines of vba statements that use the generic error handling that is after the HandleError: label
'
'

'
' You could of course, alway add more TRY CATCH blocks like the above
'
'
    
Exit Sub
HandleError:

    Select Case Err.Number
        Case 0
            ' This shold never happen as this code isan error handler!
            ' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
            ' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
            
        Case 111111
            ' You might watch to do special error handling for some predicted error numbers
            ' perhaps exit sub
            ' Perhaps using the Err.raise below
    End Select
    
    ' ie Otherwise
    '
    ' Note that I use the Err.Source to maintain a call stack of procedure names
    '
    Err.Raise Err.Number _
            , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
            , Err.Number & "-" & Err.Description
            
    ' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
    ' (By moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
    Resume
    
End Sub



Sub tsub_WillNotRaiseError_JustPrintsOk()

    Static i As Integer

    i = i + 1

    Debug.Print "OK " & i

End Sub



Public Function ManageErrSource(MyClassName As String, ErrSource As String, ErrLine As Integer, ProcedureName As String) As String

    ' This function would normally be in a global error handling module
    
    ' On Error GoTo err_ManageErrSource
 
    Const cnstblnRecordCallStack  As Boolean = True
 
    Select Case ErrSource
    
        Case Application.VBE.ActiveVBProject.Name
        
            ' Err.Source is set to this value when a VB statement raises and error. eg In Access by defualt it is set to "Database"
    
            ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
        
        Case ""
        
            ' When writing code ouside of the error handling code, the coder can raise an error explicitly, often using a user error number.
            ' ie by using err.raise MyUserErrorNumber, "", "My Error descirption".
            ' The error raised by the coder will be handled by an error handler (typically at the foot of a procedure where it was raised), and
            ' it is this handler that calls the ManageErrSource function changing the Err.Source from "" to a meaningful value.
            
            ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
        
        Case Else
    
            ' This code is executed when ManageErrSource has already been called.  The Err.Source will already have been set to hold the
            ' Details of where the error occurred.
            ' This option can be used to show the call stack, ie the names of the procdures that resulted in the prcedure with the error being called.
    
            If cnstblnRecordCallStack Then
            
                If InStr(1, ErrSource, ";") = 0 Then
                    ManageErrSource = ErrSource & ":: Called By: "
                End If
                ManageErrSource = ErrSource & ";" & ProcedureName & ":" & ErrLine
                
            Else
                ManageErrSource = ErrSource
                
            End If
        
    End Select
 
Exit Function
err_ManageErrSource:
    Err.Raise Err.Number, "MyModuleName.err_ManageErrSource", Err.Description
    Resume
    
End Function

The code below is pretty self explanatory: just copy and paste it all into a module and run it, it provides a few use cases and many explanatory comments in the text. (It works but I'm interested to know what other people make of it and for any suggestions you might like to make.)

1. When you use on error goto Label1 the procedure enters into a state of "I'm handling an error" as an exception has been raised. When it is in this state, if another "On Error Goto" label2 statement is executed it will NOT goto label2, but raises and error which is passed to the code that called the procedure.

2. You can stop a procedure being in the "I'm handling an error" state by clearing the exception (setting err to nothing) by using

    Err.clear
   

   or

    On Error Goto -1    ' Which I think is less clear!
   

I use the above techniques with various labels to simulate the sometimes useful functionality that Visual basic TRY CATCH blocks give, which I think have their place in writing readable code.

PS. Also of interest might be the procedure ManageErrSource which makes the Err.Source property store the procedure where the error occurred.

Option Compare Database
Option Explicit

Dim RememberErrNumber As Long
Dim RememberErrDescription As String
Dim RememberErrSource As String
Dim RememberErrLine  As Integer

Private Sub RememberThenClearTheErrorObject()

    On Error Resume Next
    
    ' For demo purposes
    Debug.Print "ERROR RAISED"
    Debug.Print Err.Number
    Debug.Print Err.Description
    Debug.Print Err.Source
    Debug.Print " "
    
    
    ' This function has to be declared in the same scope as the variables it refers to
    RememberErrNumber = Err.Number
    RememberErrDescription = Err.Description
    RememberErrSource = Err.Source
    RememberErrLine = Erl()
    
    ' Note that the next line will reset the error object to 0, the variables above are used to remember the values
    ' so that the same error can be re-raised
    Err.Clear
    
    ' Err.Clear  is used to clear the raised exception and set the err object to nothing (ie err.number to 0)
    ' If Err.Clear has not be used, then the next "On Error GoTo ALabel" that is used in this or the procedure that called it
    ' will actually NOT pass execution to the ALabel: label BUT the error is paseed to the procedure that called this procedure.
    ' Using Err.Clear (or "On Error GoTo -1 ")  gets around this and facilitates the whole TRY CATCH block scenario I am using there.
                
        
    ' For demo purposes
    Debug.Print "ERROR RAISED is now 0 "
    Debug.Print Err.Number
    Debug.Print Err.Description
    Debug.Print Err.Source
    Debug.Print " "
    
    ' For demo purposes
    Debug.Print "REMEMBERED AS"
    Debug.Print RememberErrNumber
    Debug.Print RememberErrDescription
    Debug.Print RememberErrSource
    Debug.Print " "
    
End Sub

Private Sub ClearRememberedErrorObjectValues()

    ' This function has to be declared in the same scope as the variables it refers to
    RememberErrNumber = 0
    RememberErrDescription = ""
    RememberErrSource = ""
    RememberErrLine = 0
    
End Sub




Sub ExampleOfTryCatchBlockInVBA()

    On Error GoTo HandleError
    
    
    ' -----------------------------------------------------
    ' SubProcedure1 has the example of a multiple line TRY block with a block of code executed in the event of an error
    
    SubProcedure1
    
    

Exit Sub
HandleError:

    Select Case Err.Number
        Case 0
            ' This shold never happen as this code is an error handler!
            ' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
            ' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
            
        Case 111111
            ' You might want to do special error handling for some predicted error numbers
            ' perhaps resulting in a exit sub with no error or
            ' perhaps using the Err.raise below
            
         Case Else
            ' Just the Err.raise below is used for all other errors
            
    End Select
    
    '
    ' I include the procedure ManageErrSource  as an exmple of how Err.Source can be used to maintain a call stack of procedure names
    ' and store the name of the procedure that FIRST raised the error.
    '
    Err.Raise Err.Number _
            , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
            , Err.Number & "-" & Err.Description
            
    ' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
    ' (When a break is active, by moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
    Resume
    
End Sub

Sub SubProcedure1()

    ' -----------------------------------------------------
    ' Example of a multiple line TRY block with a Case statement used to CATCH the error
    
    '
    ' It is sometimes better to NOT use this technique but to put the code in it's own procedure
    ' (ie I refer to the code below that is surrounded by the tag #OWNSUB) .
    ' However,sometimes using this technique makes code more readable or simpler!
    '
    
    Dim i As Integer
    
' This line puts in place the defualt error handler found at the very foot of the procedure
On Error GoTo HandleError

    
    '
    ' Perhaps lots of statements and code here
    '
 
 
    ' First an example with comments
 
 
    ' -----------------------------------------------------
    ' TRY BLOCK START
    
        ' This next line causes execution to "jump" to the "catch" block in the event an error is detected.
On Error GoTo CatchBlock1_Start
    
        ' #OWNSUB
    
        tsub_WillNotRaiseError_JustPrintsOk
        
        If vbYes = MsgBox("1. Do you want to raise an error in the try block? - (PRESS CTRL+BREAK now then choose YES, try no later.)", vbYesNo) Then
            i = 100 / 0
        End If
        
        '
        ' Perhaps lots of statements and code here
        '
        
        ' #OWNSUB

    ' TRY BLOCK END
    ' -----------------------------------------------------
    
    
    ' -----------------------------------------------------
    ' CATCH BLOCK START
CatchBlock1_Start:

    If Err.Number = 0 Then
        On Error GoTo HandleError
        ' Re-instates the procedure's generic error handler
        ' This is also done later, but I think putting it here reduces the likelyhood of a coder accidentally removing it.
        
    Else
    
        ' WARNING: BE VERY CAREFUL with any code that is written here as
        ' the "On Error GoTo CatchBlock1_Start" is still in effect and therefore any errors that get raised could goto this label
        ' and cause and infinite loop.
        ' NOTE that a replacement "On Error Goto" cannot be executed until Err.clear is used, otherwise the "On Error Goto"
        ' will itself raise and error.
        ' THEREFORE KEEP THE CODE HERE VERY SIMPLE!
        ' RememberThenClearTheErrorObject should be the only code executed and this called procedure must be tight!
        
        ' This saves the details of the error in variables so that the "On Error GoTo HandleError" can be used
        ' to determine how the next Err.Raise used below is handled (and also how any unexpected implicitly raised errors are handled)
        RememberThenClearTheErrorObject
        
        On Error GoTo HandleError   '#THISLINE#
            
        If vbYes = MsgBox("2. Do you want to raise an error in the erro handler? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
            i = 100 / 0
        End If
            
        Select Case RememberErrNumber
            Case 0:  ' No Error, do Nothing
            
            Case 2517
                Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
                ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
                
            Case Else
                ' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
    
                ' NOTE this is giving an example of what woudl happen if the CatchBlock1_ErrorElse is not used below
                If vbYes = MsgBox("3. Do you want to raise an error in the ELSE error handler? CatchBlock1_ErrorElse *HAS NOT*  been used? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
                    i = 100 / 0
                End If
    
     On Error GoTo CatchBlock1_ErrorElse
     
                
                ' SOME COMPLEX ERROR HANDLING CODE - typically error logging, email, text file, messages etc..
                ' Because the error objects values have been stored in variables, you can use
                ' code here that might itself raise an error and CHANGE the values of the error object.
                ' You might want to surround the code with the commented out CatchBlock1_ErrorElse lines
                ' to ignore these errors and raise the remembered error.  (or if calling a error handling module
                ' just use on error resume next).
                ' Without the CatchBlock1_ErrorElse lines any error raised in this "complex code" will be handled by the
                ' active error handler which was set by the "On Error GoTo HandleError" tagged as '#THISLINE#" above.
                
                If vbYes = MsgBox("4. Do you want to raise an error in the ELSE error handler when CatchBlock1_ErrorElse   HAS  been used? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
                    i = 100 / 0
                End If

CatchBlock1_ErrorElse:
     On Error GoTo HandleError
                ' This line must be preceeded by an new "On error goto" for obvious reasons
                Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
        
        End Select
                
        On Error GoTo HandleError
        
    End If
    ' CATCH BLOCK END
    ' -----------------------------------------------------
On Error GoTo HandleError  ' Unnecessary but used to delimt the catch block
    
     
    
    
'
' lots of code here perhaps
'
     
    
     
    
    ' -----------------------------------------------------
    ' Example 2
    '
    ' In this example goto statements are used instead of the IF statement used in example 1
    ' and no explanitory comments are given (so you can see how simple it can look)
    '
    
    ' -----------------------------------------------------
    ' TRY BLOCK START
    
On Error GoTo CatchBlock2_Start
    
        tsub_WillNotRaiseError_JustPrintsOk
        
        If vbYes = MsgBox("Do you want to raise an error? - (PRESS CTRL+BREAK now then choose YES)", vbYesNo) Then
            i = 100 / 0
        End If
         
        '
        ' Perhaps lots of statements and code here
        '
         
    ' TRY BLOCK END
    ' -----------------------------------------------------
     
    
GoTo CatchBlock2_End:
CatchBlock2_Start:

        RememberThenClearTheErrorObject
        
        On Error GoTo HandleError
            
        Select Case RememberErrNumber
            Case 0:  ' No Error, do Nothing
            
            Case 2517
                Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
                ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
                
            Case Else
                ' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
                ' In this case the unexpecetd erro will be handled by teh code that called this procedure
                ' This line must be preceeded by an new "On error goto" for obvious reasons
                Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
        
        End Select
                
        On Error GoTo HandleError
        
    End If
    
CatchBlock2_End:
    ' CATCH BLOCK END
    ' -----------------------------------------------------
On Error GoTo HandleError  ' Unnecessary but used to delimt the catch block
    
    
 

'
' Here you could add lots of lines of vba statements that use the generic error handling that is after the HandleError: label
'
'

'
' You could of course, alway add more TRY CATCH blocks like the above
'
'
    
    
    
Exit Sub
HandleError:

    Select Case Err.Number
        Case 0
            ' This shold never happen as this code isan error handler!
            ' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
            ' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
            
        Case 111111
            ' You might watch to do special error handling for some predicted error numbers
            ' perhaps exit sub
            ' Perhaps using the Err.raise below
    End Select
    
    ' ie Otherwise
    '
    ' Note that I use the Err.Source to maintain a call stack of procedure names
    '
    Err.Raise Err.Number _
            , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
            , Err.Number & "-" & Err.Description
            
    ' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
    ' (By moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
    Resume
    
End Sub



Sub tsub_WillNotRaiseError_JustPrintsOk()

    Static i As Integer

    i = i + 1

    Debug.Print "OK " & i

End Sub



Public Function ManageErrSource(MyClassName As String, ErrSource As String, ErrLine As Integer, ProcedureName As String) As String

    ' This function would normally be in a global error handling module
    
    ' On Error GoTo err_ManageErrSource
 
    Const cnstblnRecordCallStack  As Boolean = True
 
    Select Case ErrSource
    
        Case Application.VBE.ActiveVBProject.Name
        
            ' Err.Source is set to this value when a VB statement raises and error. eg In Access by defualt it is set to "Database"
    
            ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
        
        Case ""
        
            ' When writing code ouside of the error handling code, the coder can raise an error explicitly, often using a user error number.
            ' ie by using err.raise MyUserErrorNumber, "", "My Error descirption".
            ' The error raised by the coder will be handled by an error handler (typically at the foot of a procedure where it was raised), and
            ' it is this handler that calls the ManageErrSource function changing the Err.Source from "" to a meaningful value.
            
            ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
        
        Case Else
    
            ' This code is executed when ManageErrSource has already been called.  The Err.Source will already have been set to hold the
            ' Details of where the error occurred.
            ' This option can be used to show the call stack, ie the names of the procdures that resulted in the prcedure with the error being called.
    
            If cnstblnRecordCallStack Then
            
                If InStr(1, ErrSource, ";") = 0 Then
                    ManageErrSource = ErrSource & ":: Called By: "
                End If
                ManageErrSource = ErrSource & ";" & ProcedureName & ":" & ErrLine
                
            Else
                ManageErrSource = ErrSource
                
            End If
        
    End Select
 
Exit Function
err_ManageErrSource:
    Err.Raise Err.Number, "MyModuleName.err_ManageErrSource", Err.Description
    Resume
    
End Function