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.)
The most important facts to realise are:
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.
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!
(NOTE that On Error Goto 0 is different from the above)
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 mostly best to use:
On Error Goto -1
as using Err.clear You would often need to write
Err.Clear
On Error Goto MyErrorHandlerLabel
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.
Admittedly this technique creates a few more lines of code than a nice VB try catch statement, but it's not too messy and pretty easy to get your head around.
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
On Error Goto -1\$\endgroup\$On Error Resume NextandOn Error GoTo 0and checking theErr.Number. The above is somewhat hard to follow, has a bit of aspaghettistructure.. \$\endgroup\$