Error Handling not working

Question

I have (on a number of occasions) struggled to get error handling to work the way I want...

Imagine that I have some code that might cause an error and send the running-line into an error handling routine, as below

'==============================

Sub DoSomething()

   On Error GoTo ErrTrap

    [Some code here that might cause an error]

    exit sub

ErrTrap:

    Err.Clear

    On Error Resume Next ' ***

    [Some code here that tidies up]

    MsgBox "There has been an error", vbCritical

End Sub

'==============================

The 'Resume Next' line in the error trap (the one with the ***) is ignored and if an error occurs while tidying up, that error is handled by the 'Debug-box' - as if I had written:

On Error GoTo 0

But sometimes the tidying up part involves similar things to what caused the error in the first place and so might also cause an error... and I am trying to avoid the user seeing the Debug-box. What am I doing wrong? How can I handle an error that occurs after my error trap has been 'sprung'?

Many thanks if anyone can clear this up for me.

M

Answer 1

Thanks for your answer Andreas, I appreciate the time you've taken to help me with this...

...so that's just the wrong syntax, but I hope you don't mind me asking...

Could it be:

(I've just added a 'Resume' line - the one in bold)

'==============================

Sub DoSomething()

   On Error GoTo ErrTrap

    [Some code here that might cause an error]

    exit sub

ErrTrap:

    Err.Clear

    Resume FixIt

FixIt:

    On Error Resume Next ' ***

    [Some code here that tidies up]

    MsgBox "There has been an error", vbCritical

End Sub

'==============================

Many thanks

M

Answer 2

Could I have written my code like below? (using resume instead of 'GoTo'):

'==============================

Sub DoSomething()

   On Error Resume ErrTrap

No.

Andreas.

Answer 3

Hi Andeas, thanks for your answer. I didn't know that I had to use resume before the error was properly reset. I thought that Err.Clear did that.

One more question though...

Could I have written my code like below? (using resume instead of 'GoTo'):

'==============================

Sub DoSomething()

   On Error Resume ErrTrap

    [Some code here that might cause an error]

    exit sub

ErrTrap:

    Err.Clear

    On Error Resume Next ' ***

    [Some code here that tidies up]

    MsgBox "There has been an error", vbCritical

End Sub

'==============================

Many thanks

M

Answer 4

I would suggest that you use

ErrTrap:

    MsgBox Err.Number & vbCr & Err.Description

    Exit Sub

You should only use:

On Error Resume Next

When you know why the error will occur and you have incorporated into your code the necessary commands to overcome the error.

It does NOT belong in an Error trapping routine.

Answer 5

Before I go into the details, your main routine should have an error handler like this one:

Sub Main()

  On Error GoTo Errorhandler

  'Your code

  Exit Sub

Errorhandler:

  If Err.Source = "" Then Err.Source = Application.Name

  Debug.Print "Source     : " & Err.Source

  Debug.Print "Error      : " & Err.Number

  Debug.Print "Description: " & Err.Description

  If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _

      Err.Description & vbNewLine & vbNewLine & _

      "Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then

    Stop 'Press F8 twice

    Resume

  End If

End Sub

And all your subroutines can also install error handlers. And in terms of the code above, they should write the name of the subroutine into Err.Source, e.g.: Err.Source = "DoSomething"

At any point inside a sub routine, when an unhandled error occurs (e.g. after On Error Goto 0), the code jumps back into the error handler inside the main routine.

Okay, back to your question, basically you think you could do that:

Sub Wrong()

  On Error GoTo Errorhandler

  Err.Raise 1

  Exit Sub

Errorhandler:

  On Error Resume Next

  Err.Raise 2

End Sub

This does not work as expected because you do not call Resume! And that is the secret:

Sub Right()

  On Error GoTo Errorhandler

  Err.Raise 1, "Sub Right", "Error description 1"

  Exit Sub

Errorhandler:

  Select Case Err.Number

    Case 1

      Resume Errorhandler

    Case 2

      'Error is not trappable

      Exit Sub

  End Select

  Err.Raise 2, "Sub Right", "Error description 2"

End Sub

Copy sub Right into the VBA editor and debug it (execute it step by step by pressing F8 key). Then you should understand whats going on. Afterwards debug also the subs below my name, which are other possibilities.

Andreas.

Sub Right2()

  On Error GoTo Errorhandler

  Err.Raise 1, "Sub Right", "Error description 1"

  Exit Sub

Errorhandler:

  Select Case Err.Number

    Case 1

      Resume Errorhandler

    Case 2

      Resume Next

  End Select

  Err.Raise 2, "Sub Right", "Error description 2"

  'Just a line of code:

  DoEvents

End Sub

Sub Right3()

  On Error GoTo Errorhandler

  Err.Raise 1, "Sub Right", "Error description 1"

  Exit Sub

Errorhandler:

  Select Case Err.Number

    Case 1

      Resume Errorhandler

    Case 2

      On Error Resume Next

      Resume Errorhandler

  End Select

  Err.Raise 2, "Sub Right", "Error description 2"

  'Just a line of code:

  DoEvents

End Sub