Q:

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

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

A:

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.

A:

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.

A:

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

