Error handling

[Aussiebear]

I see a lot of threads that use all sorts of error handling such as

"On Error goto Err" 'where Err is the small section of code later on in the code
"On Error resume Next"

What is a better way to handle and trap the error when designing your code?

[Paul_Hossler]

Chip has a nice writeup

http://www.cpearson.com/Excel/ErrorHandling.htm

Personally, I think that the best approach depends on the circumstances

I bracket a statement or block with On Error Resume Next / Goto 0 if it doesn'y matter, e.g

Simple examples of my (very) personal style



Option Explicit

Sub sub1()
On Error Resume Next
Worksheets("MightNotBeThere").Delete
On Error GoTo 0
End Sub

Sub sub2()
Dim n As Long
On Error GoTo ErrHandle
n = InputBox("Enter Positive Number", "Error Msg")
If n < 1 Then Err.Raise 1000, "Sub2", "Not Positive"
MsgBox "n = " & n
Exit Sub
ErrHandle:
MsgBox Err.Description & " -- I'll make it 1000"
n = 1000
Resume Next ' goes to the MsgBox
End Sub

Sub sub3()
Dim n As Long
On Error GoTo ErrHandle
GetNumber:
n = InputBox("Enter Positive Number", "Error Msg")
If n = 0 Then Err.Raise 1001, "Sub3", "Zero is no good"
If n < 1 Then Err.Raise 1000, "Sub3", "Not Positive"
MsgBox "n = " & n
Exit Sub
ErrHandle:
MsgBox Err.Description & " -- Try again"
Resume GetNumber
End Sub

Sub sub4()
Dim n As Long
n = 0
On Error GoTo ErrHandler
MsgBox 1234 / n
On Error GoTo 0
Exit Sub
ErrHandler:
n = 10
MsgBox "Correcting"
Resume
End Sub

[Aussiebear]

Thank you Paul. I was more wondering if in hindsight that the procedure should or should not be used, given in the design phase we should be trying to overcome the errors that are created.

[Aflatoon]

You'll find people on every part of that spectrum. I know some who don't really believe in error handling at all, and others who put it in every routine. Personally I fall somewhere near the middle but leaning more to the no error handling side, especially at the testing phase. Sometimes though, using an error handler is just a lot simpler than the alternative options. Typically for me, anything that is an On Error Resume Next followed by resetting error handling, should be its own separate procedure.

[Aussiebear]

Fair enough I guess. All to often I see code that has error handling put in but it serves no real purpose as it tends to hide the error rather than track it down.

[Aflatoon]

If I had a penny for every forum question I've seen that was along the lines of "my code doesn't error but it doesn't do what I want (or do anything)" and the first line of the code body is On Error Resume Next... :)

[Aussiebear]

...ROFL. We must be reading the same code book then.

[georgiboy]

I quite often use it to make the code flow/ run but I don't use it to inform the user what is going on.

Sort of the same subject, when I use on error resume next, I tend to use it like an IF block. This may not be the right way to do it but I do it to make the code readable and easy to spot that you may have forgotten the 'On Error GoTo 0' line.

A good example is creating a collection of unique items:

Sub test()
Dim rCell As Range, col As New Collection

For Each rCell In Range("A1:A100").Cells
On Error Resume Next
col.Add rCell.Value, CStr(rCell.Value) ' If error then you are trying to add a duplicate (or blank), it will be skipped.
On Error GoTo 0
Next rCell
End Sub

[Aflatoon]

What's the point of having it inside the loop?

[Paul_Hossler]

Well, FWIW I like to bracket just the statement or statements that might throw an error that I want to to ignore

I just find it easier to follow the code that way

[georgiboy]

What's the point of having it inside the loop?

If it was outside of the For Next loop and I made a typo in the for next line, I would miss it as below:

Sub test()
Dim rCell As Range, col As New Collection

On Error Resume Next
For Each rCell In Range("A1A100").Cells
col.Add rCell.Value, CStr(rCell.Value) ' If error then you are trying to add a duplicate (or blank), it will be skipped.
Next rCell
On Error GoTo 0
End Sub

As I know that 'col.Add' line will throw the error - that is the only line I want to handle.

[Aflatoon]

Since this is the kind of thing you'd probably reuse, I'd refactor it into its own routine, then you wouldn't need to turn error handling on and off inside the loop. ;)

[georgiboy]

I used it as an example for the post, these days I would probably use the below instead ;)

Sub test()
Dim v As Variant

v = Application.Unique(Range("A1:A100"))
End Sub

[Jan Karel Pieterse]

Here is an example of a cascading error handling scheme. Start the top routine and step through to see this in action. The error message shows the call stack.

Option Explicit

'Each Routine has an error handler, but only the entrypoint Routine displays an error message

Sub CascadingError()
On Error GoTo LocErr
nextsub1
Exit Sub
LocErr:
MsgBox "Routine Cascadingerror" & vbNewLine & Err.Description
End Sub

Sub nextsub1()
On Error GoTo LocErr
nextsub2
Exit Sub
LocErr:
If Err.Number < 0 Then
Err.Raise vbObjectError + 2, _
"nextsub1", _
"Routine nextsub1" & vbNewLine & Err.Description
Else
Err.Raise vbObjectError + 2, _
"Routine nextsub1", _
"Error in nextsub1, number: " & Err.Number & ", error description: " & vbNewLine & Err.Description
End If
End Sub

Sub nextsub2()
On Error GoTo LocErr
MsgBox 1 / 0
Exit Sub
LocErr:
If Err.Number < 0 Then
Err.Raise vbObjectError + 2, _
"nextsub2", _
"Routine nextsub2" & vbNewLine & Err.Description
Else
Err.Raise vbObjectError + 2, _
"nextsub2", _
"Error in nextsub2, number: " & Err.Number & ", error description: " & vbNewLine & Err.Description
End If
End Sub

[Aussiebear]

Thank you JKP.