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

migrated from stackoverflow.com Jun 23 '15 at 6:29

This question came from our site for professional and enthusiast programmers.

  • 1
    Only two comments: 1 Why on earth would you use this? 2 On Error Goto -1 – Rory Jun 22 '15 at 23:51
  • 4
    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.. – Ioannis Jun 22 '15 at 23:52
  • 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 Jun 23 '15 at 0:02
  • 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. – HarveyFrench Jun 23 '15 at 0:05
  • @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. – D_Bester Jun 23 '15 at 13:50
up vote 22 down vote accepted

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.

  • 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... – HarveyFrench Jun 23 '15 at 19:57
  • @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. – Mathieu Guindon Jun 23 '15 at 21:13
  • 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. – HarveyFrench Jun 23 '15 at 21:21

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.)

  • 1
    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. – HarveyFrench Jun 23 '15 at 20:08
  • You've hit on a great point @HarveyFrench. Exceptions are for exceptional behavior, not control flow. Welcome to CR. – RubberDuck Jun 23 '15 at 20:36
  • I'd be very interested in your opinions on this SO question: stackoverflow.com/questions/31007009/… – HarveyFrench Jun 23 '15 at 20:48
  • 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/… – HarveyFrench Jun 23 '15 at 21:18

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:

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

  • 1
    @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 Jun 23 '15 at 9:22
  • @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. – HarveyFrench Jun 23 '15 at 10:05
  • @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 Jun 23 '15 at 13:44
  • @HarveyFrench Err.Clear and On Error Goto -1 are NOT equivalent. See stackoverflow.com/a/30994055/2559297 – D_Bester Jun 23 '15 at 13:45
  • 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. – HarveyFrench Jun 23 '15 at 14:15

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
  • 3
    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. – TheCoffeeCup Nov 5 '15 at 1:06

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.