Try catch statement in VBA using the standard VBA error handling statements

Question

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:

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!
   

(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

• Rory: Only two comments: 1 Why on earth would you use this? 2 On Error Goto -1
• Ioannis: Try/catch can also be emulated by wrapping the relevant code with On Error Resume Next and On Error GoTo 0 and checking the Err.Number. The above is somewhat hard to follow, has a bit of a spaghetti structure..
• HarveyFrench: Thanks Rory, I've changed it. You would use it or the same reason anyone would use a TRY CATCH statement in VB or SQL Server. ie it lets you structure your code differently. ie you can use the same error handler for many lines of code without having to put the lines into their own procedure.
• HarveyFrench: Loannis. yes I've done that in the past, for single lines of code that need an error handler. Ie one line of code has one error handler. TRY CATCH allows a block of code (with many lines) to be embedded in a procedure with it's own error handler. I use TRY CATCH a lot in SQL Server and as it's available in VB as well it must serve some generally useful purpose. Admittedly this version is a little messy.
• D_Bester: @Loannis What if you want to skip multiple lines when you get an error. See my answer for a simplified example. Of course you can also do that with regular error handling.

Answer 1

The problem is that runtime errors in VBA are not exceptions, and error-handling in VBA has very little in common with exception handling.

RememberErrLine = Erl()

The Erl function is a hidden member of the VBA.Information module for a reason - it returns 0 unless the error occurred on a numbered line. And if you're using line numbers in VBA, you have been living in a cave for 25 years and are probably using GoSub statements instead of writing procedures. Line numbers are supported for legacy/backward-compatibility reasons, because code written in the 1980's required them.

I like how you said it yourself:

' THEREFORE KEEP THE CODE HERE VERY SIMPLE!

..but why isn't that applied to the rest of the code? No offense, but this is spaghetti logic, written in procedures that clearly and shamelessly violate the Single Responsibility Principle. No SRP-compliant code would ever need two of such "try-catch" blocks.

This smells:

Case 0: ' No Error, do Nothing

It means one of two things: either you have error-handling code that runs in non-error contexts, or you have dead code that should be deleted.

This (削除) smells (削除ここまで) reeks:

GoTo CatchBlock2_End:
CatchBlock2_Start:

Firstly, a colon (:) that isn't specifying a line label, is an instructions separator. Turns out a new line is also an "instructions separator", so the colon at the end of GoTo CatchBlock2_End is utterly useless and confusing, especially given the indentation level of the GoTo statement.

Speaking of GoTo...

Neil Stephenson thinks it's cute to name his labels 'dengo'

I don't like how I need to jump between labels to follow the code. IMO it is messy and needlessly spaghettified.

---

Fine, smartypants. So, how does one cleanly handle errors in VBA then?

1. Write clean code in the first place.

Adhere to best practices, and write small procedures that do one thing, and do it well.

2. Write Object-Oriented code.

Abstraction and encapsulation are two of the 4 pillars of OOP, and they're fully supported in VBA. Polymorphism is also somewhat of an option; only proper inheritance is ruled out, but that doesn't prevent one from abstracting concepts in class modules and instantiating specialized objects.

Procedural code written in standard modules (.bas) should be tiny little public methods (macro "hooks") that create the objects required to run the functionality.

So, how does that even remotely relate to proper error-handling?

3. Embrace idiomatic error handling, don't fight it.

Given code that adheres to the above points, there's no reason to not implement error-handling the idiomatic VBA-way.

Public Sub DoSomething()
 On Error GoTo CleanFail
 'method body
CleanExit:
 'cleanup code goes here. runs regardless of error state.
 Exit Sub
CleanFail:
 'handle runtime error(s) here.
 'Raise Err.Number '"rethrow" / "bubble up"
 Resume CleanExit
 Resume 'for debugging - break above and jump to the error-raising statement
End Sub

This pattern is analoguous to a "try-catch-finally" in the following way:

• The body is the "try" part, that does what the method name says and nothing more
• CleanFail is the "catch" part, that only runs if an error is raised
• CleanExit is the "finally" part, that runs regardless of whether or not an error was raised... unless you're rethrowing. But then if you need to bubble up an error for the calling code to handle, you shouldn't have much cleanup code to execute, and you should have a very very very good reason to do so.

If your error-handling subroutine can raise an error, then you're not adhering to SRP. For example, writing to a log file is a concern of its own, that should be abstracted into some Logger object that lives to deal with logging concerns, and exposes methods that handle their own errors. Error-handling subroutine code should be trivial.

• HarveyFrench: Thanks @mat'smug for taking the time to add comments which really helped me I'm up for violent yet humorous criticism. I've been reviewing my code and I'm pleased to say that the vast majority adheres to the principals you outline. Your explanation was useful though and it made me reflect and realise that I didn't appreciate that VB and SQL Server TRY CATCH statements are only used once in each procedure (I thought they were a means on not having to abstract code out to make it more readable). If you fancy adding some more comments about the ManageErrSource procedure I'm all ears...
• Mathieu Guindon: @HarveyFrench I'll add some more when I get a chance - hadn't looked at this one ;-) referencing and using the VBIDE API requires special security settings, which is not cool. I've come to use TypeName(Me) as a source for custom errors in class modules, and the only way for an error to know what procedure it occurred in, is to hard-code the procedure name into a local const, ideally not too far from the method's signature. I like the call stack idea, but one drawback is that you need to consistently "push" and "pop" whenever you enter/exit a procedure, otherwise it becomes a lie.
• HarveyFrench: The code I received from fmsinc.com gets around a lot of the issues I've been having. I'd value you opinion. See here codereview.stackexchange.com/questions/94498/… I do appreciate your time as this is driving me nuts.

Answer 2

Listen to Mat'sMug, but he didn't cover the situation where you actually know how to recover from an error. For completeness, I'd like to cover that.

Let's look at how we would do something like this in VB.Net first.

Try
 foo = SomeMethodLikelyToThrowAnException
Catch e As SomeException
 foo = someDefaultValue
End Try
' some more code

The idiomatic way to do this in VB6 is to ResumeNext. Write this down, because it's the only time I'll ever say it's right to ResumeNext.

On Error Goto ErrHandler
 foo = SomeMethodLikelyToRaiseAnError
 ' some more code
CleanExit: 
 ' clean up resources
 Exit Sub
ErrHandler:
 If Err.Number = ConstantValueForErrorWeExpected Then
 foo = someDefaultValue
 Resume Next
 End If
 Resume CleanExit 'or re-raise error
Exit Sub

The alternative way is to inline this logic, which I think is a bit cleaner and closer to the Try...Catch idiom, but it can get ugly quick if abuse it.

On Error Resume Next 
 foo = SomeMethodLikelyToRaiseAnError
 If Err.Number = ConstantValueForErrorWeExpected Then 
 foo = someDefaultValue
 End If
On Error Goto 0

Either is an idiomatic way to deal with expected errors, but whatever you do. Don't bother with Resume Next until you completely understand what it does and when it's appropriate. (More a warning to future readers than to you. You seem to thoroughly understand error handling in VB6. Perhaps a little too well for your own good.)

• HarveyFrench: Thanks @RubberDuck for your useful comments. Being honest I do find myself using "On Error resume next" before quite a few procedure calls after which there is typically a SELECT CASE that responds to any error raised. The big mistake I realise I am making is that I raise an user defined exception in the sub procedure to flag situations arising (like the user requesting to cancel processing). I thin I shold be using functions more. This is an indication that my general code structure is "not ideal"/poor and I think and I need to address this. Thanks.
• RubberDuck: You've hit on a great point @HarveyFrench. Exceptions are for exceptional behavior, not control flow. Welcome to CR.
• HarveyFrench: I'd be very interested in your opinions on this SO question: stackoverflow.com/questions/31007009/…
• HarveyFrench: The code I received from fmsinc.com gets around a lot of the issues I've been having. I'd value you opinion. See here codereview.stackexchange.com/questions/94498/…
• johny why: Your "only time you would use Resume Next" is a time i would not use On Error Goto. i think that's a misapplication of On Error Goto, which is an interrupt. Interrupts should be used for errors which interrupt the flow of your program, including unhandled errors and user-interrupts. Your use-case isn't either. You're just trying something. If that option is no good, you'll use the default value. Your program continues with Resume Next. That's the flow of your program. Your inline version isn't just cleaner, it's a more appropriate use of error tools. Not a minor difference!

Answer 3

This answer is intended to simplify the Try/Catch pattern to be easily understandable.

This is not very different from regular inline error handling except that it can skip multiple lines at once, handle an error and then resume regular execution. This is a very cleanly structured pattern for handling an error. The flow moves very cleanly from top to bottom; no spaghetti code here.

Traditionally the error handler is placed at the bottom. But the Try/Catch construct is so elegent. It's a very structured way of handling errors and is very easy to follow. This pattern attempts to reproduce that in a very clean concise way. The flow is very consistent and doesn't jump from place to place.

Sub InLineErrorHandling()
 'code without error handling
BeginTry1:
 'activate inline error handler
 On Error GoTo ErrHandler1
 'code block that may result in an error
 Dim a As String: a = "Abc"
 Dim c As Integer: c = a 'type mismatch
ErrHandler1:
 'handle the error
 If Err.Number <> 0 Then
 'the error handler is now active
 Debug.Print (Err.Description)
 End If
 'disable previous error handler (VERY IMPORTANT)
 On Error GoTo 0
 'exit the error handler
 Resume EndTry1
EndTry1:
 'more code with or without error handling
End Sub

Sources:

• Pearson Error Handling In VBA
• How to: Handle Run-Time Errors in VBA
• Properly Handling Errors in VBA (Excel)
• My own: How to do an error handling block inline like Try/Catch

Properly managed this works quite nicely. It is a very clean flowing pattern that is reproducible anywhere it is needed.

• HarveyFrench: @D_Bester, Thanks for the links and the simple example. I'm still learning and found your feedback useful, however you will need to add an "On Error Goto 0" after the "on Error goto -1". Also on reflection I think it is better to use Err.Clear instead of "On Error Goto -1" as it more clearly shows what is happening. I'm finding this whole error handling in VBA a bit of a black art.
• HarveyFrench: @D_Bester. On reflection, you code is fine if all you want to give the user a message when an error occurs, but what if you want to re-raise the error? Which will be a very common scenario. Consider. If you code was trying to lookup a customer's details and it couldn't get them for an UNEXPECTED reason. You would need to re-raise he error and let the code that is using your code to do the lookup decide what to do.
• D_Bester: @HarveyFrench If you want to re-raise the error just use 'Err.Raise'. No problem there assuming the code is structured well and error handling is enabled in the calling code.
• D_Bester: @HarveyFrench Err.Clear and On Error Goto -1 are NOT equivalent. See stackoverflow.com/a/30994055/2559297
• HarveyFrench: You're right they are not the same sorry. But I think the code above still needs On Error GoTo -1 replaced with Err.Clear otherwise the "'more code without error handling" will jump to ErrHandler1 if an error occurrs.

Answer 4

To clarify my previous post, the following line from HarveyFrench's code:

RememberErrLine = Erl()

will not work unless line numbers have been added to every line of code. Rather than manually typing line numbers, which is way too tedious, you can use a tool to automatically add the line numbers. There are a few tools out there that can do this, I use one called CodeLiner.

Here is the code with line numbers, which will allow Erl() to work successfully:

Option Compare Database
Option Explicit
Dim RememberErrNumber As Long
Dim RememberErrDescription As String
Dim RememberErrSource As String
Dim RememberErrLine As Integer
Private Sub RememberThenClearTheErrorObject()
10
11 On Error Resume Next
12
 ' For demo purposes
14 Debug.Print "ERROR RAISED"
15 Debug.Print Err.Number
16 Debug.Print Err.Description
17 Debug.Print Err.Source
18 Debug.Print " "
19
20
 ' This function has to be declared in the same scope as the variables it refers to
22 RememberErrNumber = Err.Number
23 RememberErrDescription = Err.Description
24 RememberErrSource = Err.Source
25 RememberErrLine = Erl()
26
 ' 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
29 Err.Clear
30
 ' 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.
35
36
 ' For demo purposes
38 Debug.Print "ERROR RAISED is now 0 "
39 Debug.Print Err.Number
40 Debug.Print Err.Description
41 Debug.Print Err.Source
42 Debug.Print " "
43
 ' For demo purposes
45 Debug.Print "REMEMBERED AS"
46 Debug.Print RememberErrNumber
47 Debug.Print RememberErrDescription
48 Debug.Print RememberErrSource
49 Debug.Print " "
50
End Sub
Private Sub ClearRememberedErrorObjectValues()
54
 ' This function has to be declared in the same scope as the variables it refers to
56 RememberErrNumber = 0
57 RememberErrDescription = ""
58 RememberErrSource = ""
59 RememberErrLine = 0
60
End Sub
Sub ExampleOfTryCatchBlockInVBA()
67
68 On Error GoTo HandleError
69
70
 ' -----------------------------------------------------
 ' SubProcedure1 has the example of a multiple line TRY block with a block of code executed in the event of an error
73
74 SubProcedure1
75
76
77
78 Exit Sub
79 HandleError:
80
81 Select Case Err.Number
82 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!
86
87 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
91
92 Case Else
 ' Just the Err.raise below is used for all other errors
94
95 End Select
96
 '
 ' 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.
 '
101 Err.Raise Err.Number _
 , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
 , Err.Number & "-" & Err.Description
104
 ' 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)
107 Resume
108
End Sub
Sub SubProcedure1()
112
 ' -----------------------------------------------------
 ' Example of a multiple line TRY block with a Case statement used to CATCH the error
115
 '
 ' 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!
 '
121
122 Dim i As Integer
123
' This line puts in place the defualt error handler found at the very foot of the procedure
125 On Error GoTo HandleError
126
127
 '
 ' Perhaps lots of statements and code here
 '
131
132
 ' First an example with comments
134
135
 ' -----------------------------------------------------
 ' TRY BLOCK START
138
 ' This next line causes execution to "jump" to the "catch" block in the event an error is detected.
140 On Error GoTo CatchBlock1_Start
141
 ' #OWNSUB
143
144 tsub_WillNotRaiseError_JustPrintsOk
145
146 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
147 i = 100 / 0
148 End If
149
 '
 ' Perhaps lots of statements and code here
 '
153
 ' #OWNSUB
155
 ' TRY BLOCK END
 ' -----------------------------------------------------
158
159
 ' -----------------------------------------------------
 ' CATCH BLOCK START
162 CatchBlock1_Start:
163
164 If Err.Number = 0 Then
165 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.
168
169 Else
170
 ' 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!
178
 ' 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)
181 RememberThenClearTheErrorObject
182
183 On Error GoTo HandleError '#THISLINE#
184
185 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
186 i = 100 / 0
187 End If
188
189 Select Case RememberErrNumber
190 Case 0: ' No Error, do Nothing
191
192 Case 2517
193 Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
194 ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
195
196 Case Else
 ' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
198
 ' NOTE this is giving an example of what woudl happen if the CatchBlock1_ErrorElse is not used below
200 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
201 i = 100 / 0
202 End If
203
204 On Error GoTo CatchBlock1_ErrorElse
205
206
 ' 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.
215
216 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
217 i = 100 / 0
218 End If
219
220 CatchBlock1_ErrorElse:
221 On Error GoTo HandleError
 ' This line must be preceeded by an new "On error goto" for obvious reasons
223 Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
224
225 End Select
226
227 On Error GoTo HandleError
228
229 End If
 ' CATCH BLOCK END
 ' -----------------------------------------------------
232 On Error GoTo HandleError ' Unnecessary but used to delimt the catch block
233
234
235
236
'
' lots of code here perhaps
'
240
241
242
243
 ' -----------------------------------------------------
 ' 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)
 '
250
 ' -----------------------------------------------------
 ' TRY BLOCK START
253
254 On Error GoTo CatchBlock2_Start
255
256 tsub_WillNotRaiseError_JustPrintsOk
257
258 If vbYes = MsgBox("Do you want to raise an error? - (PRESS CTRL+BREAK now then choose YES)", vbYesNo) Then
259 i = 100 / 0
260 End If
261
 '
 ' Perhaps lots of statements and code here
 '
265
 ' TRY BLOCK END
 ' -----------------------------------------------------
268
269
270 GoTo CatchBlock2_End:
271 CatchBlock2_Start:
272
273 RememberThenClearTheErrorObject
274
275 On Error GoTo HandleError
276
277 Select Case RememberErrNumber
278 Case 0: ' No Error, do Nothing
279
280 Case 2517
281 Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
282 ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
283
284 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
288 Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
289
290 End Select
291
292 On Error GoTo HandleError
293
294 End If
295
296 CatchBlock2_End:
 ' CATCH BLOCK END
 ' -----------------------------------------------------
299 On Error GoTo HandleError ' Unnecessary but used to delimt the catch block
300
301
302
303
'
' Here you could add lots of lines of vba statements that use the generic error handling that is after the HandleError: label
'
'
308
'
' You could of course, alway add more TRY CATCH blocks like the above
'
'
313
314
315
316 Exit Sub
317 HandleError:
318
319 Select Case Err.Number
320 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!
324
325 Case 111111
 ' You might watch to do special error handling for some predicted error numbers
 ' perhaps exit sub
 ' Perhaps using the Err.raise below
329 End Select
330
 ' ie Otherwise
 '
 ' Note that I use the Err.Source to maintain a call stack of procedure names
 '
335 Err.Raise Err.Number _
 , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
 , Err.Number & "-" & Err.Description
338
 ' 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)
341 Resume
342
End Sub
Sub tsub_WillNotRaiseError_JustPrintsOk()
348
349 Static i As Integer
350
351 i = i + 1
352
353 Debug.Print "OK " & i
354
End Sub
Public Function ManageErrSource(MyClassName As String, ErrSource As String, ErrLine As Integer, ProcedureName As String) As String
360
 ' This function would normally be in a global error handling module
362
 ' On Error GoTo err_ManageErrSource
364
365 Const cnstblnRecordCallStack As Boolean = True
366
367 Select Case ErrSource
368
369 Case Application.VBE.ActiveVBProject.Name
370
 ' Err.Source is set to this value when a VB statement raises and error. eg In Access by defualt it is set to "Database"
372
373 ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
374
375 Case ""
376
 ' 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.
381
382 ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
383
384 Case Else
385
 ' 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.
389
390 If cnstblnRecordCallStack Then
391
392 If InStr(1, ErrSource, ";") = 0 Then
393 ManageErrSource = ErrSource & ":: Called By: "
394 End If
395 ManageErrSource = ErrSource & ";" & ProcedureName & ":" & ErrLine
396
397 Else
398 ManageErrSource = ErrSource
399
400 End If
401
402 End Select
403
404 Exit Function
405 err_ManageErrSource:
406 Err.Raise Err.Number, "MyModuleName.err_ManageErrSource", Err.Description
407 Resume
408
End Function

• TheCoffeeCup: Hi! Welcome to Code Review. Please add more context to your answer: explain why your suggestion will improve the OP's code, or perhaps go into more detail about what you are trying to say.
• johny why: Great, but unnecessary, i think, to number every single line. You can also just number the lines that are actually likely to cause an error -- Erl will find them.

Answer 5

Regarding 'CleanExit' and the 'Finally' topic.

Mat's Mug wrote:

CleanExit is the "finally" part, that runs regardless of whether or not an error was raised... unless you're rethrowing.

---

Such a situation could occur for example in this procedural code:

Procedural approach

Public Sub DoSomething()
 On Error GoTo CleanFail
 ' Open any resource
 ' Use the resource
CleanExit:
 ' Close/cleanup the resource
 Exit Sub
CleanFail:
 Raise Err.Number
 Resume CleanExit
End Sub

Problem here: If any error occurs in the methods body which has to be reraised in CleanFail, CleanExit will not be executed at all and hence the resource can't be closed properly.

Sure, you could close the resource also in the error handler itself, but that could lead to have multiple code fragments where resource handling will be/has to be done.

---

My suggestion is to use a custom object for each resource binding necessarity:

AnyResourceBindingClass

Private Sub Class_Initialize() 'Or even use Mats 'Create method' approach here instead.
 'Open/acquire the resource here
End Sub
Private Sub Class_Terminate()
 On Error GoTo CleanFail
 'Close/clean up the resource here properly
CleanExit:
 Exit Sub
CleanFail:
 MsgBox Err.Source & " : " & Err.Number & " : " & Err.Description
 Resume CleanExit
End Sub
Public Sub UseResource()
 'Do something with the resource
End Sub

Object oriented approach

Public Sub DoSomething()
 On Error GoTo CleanFail
 ' Use custom object which acquires the resource
 With New AnyResourceBindingClass
 .UseResource
 End With
CleanExit:
 Exit Sub
CleanFail:
 Raise Err.Number
 Resume CleanExit
End Sub

Opportunity: Because the custom object will be out of scope after the error is raised, its Terminate method will be executed automatically, which causes that the aquired resource will be closed/cleaned up properly.

One necessity less for a 'finally' block.

---

Error handling in Terminate method

In my opinion it is context dependent how an error will be handled in the Terminate method of the custom class. Maybe it should be logged somewhere additionally or even swallowed down at all?

Surely this is discussable.

But it is essential to enable an error handler in this method, because, as far as I know, any unhandled error in this method will cause VBA to break execution and display its standard runtime error messagebox.

• johny why: If "Close/cleanup" code has risk of throwing an error, it should be in it's own procedure. That has nothing to do with classes or objects, which aren't necessary to isolate the Close/cleanup code. Your resource object seems to have no bearing on the error-handling.