Error Handling

[austenr]

Is there a way you can code error handling for the whole sub without having to put it everywhere in the code that you get an exception? Thanks

[Zack Barresse]

Hi Austen,

Sure. But you should be very careful when doing something like this, especially during testing/debugging/troubleshooting. This should be considered on a final project when you know exactly what errors you will get, why, and when. You can do something like ...

Sub MyTest()
    On Error GoTo myError
    'code
    'blah
    'simulated error here
    'rest of code
    'finishing here
    GoTo myEnd
myError:
    'error handling code here
    Err.Clear
myEnd:
    'ending code here
End Sub

[Aaron Blood]

Usually the format doesn't inlcude a GOTO... but I guess that's OK.

I don't agree with the idea that you can't have an error handler until the app is complete. I like to structure mine like this so I can develop the code and kill errors as they crop up.

Sub Test() 
On Error GoTo ErrHand
Err.Raise 1004
Err.Raise 32
Err.Raise 50
Exit Sub
 
ErrHand:
Select Case Err.Number 
Case 1004
Err.Clear
Resume Next
Case 32
Err.Clear
Resume Next
Case Else
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Err.Clear
Stop
Resume
End Select
End Sub

I like to include the Case Else catchall with an alert so I can build the err handler incrementally as I work. In other words... as errors pop up I add new cases to deal with them. Also, notice the Stop and Resume lines in the catchall. It stops the code after alerting me, and I can use the step feature to step to resume and then step right to the offending line!

The code wrapper here doesn't display my indentation correctly... Usually I leave the Exit Sub line at the same level as the Sub/End Sub lines. If you format it right it looks like the sub is split into two parts code on top ErrHandler at bottom. Execution never has to skip the err handler due to the Exit Sub. The indentation on my Select Case structure doesn't appear correct here either... I usually apply an indent to each Case within the Select Case so it's easy to see the enclosed structure. Not sure why it doesn't work here...

Select Case X 
<tab> Case 1
<tab> <tab> ...
<tab> Case 2
<tab> <tab> ...
<tab> Case Else
<tab> <tab> ...
End Select

That's irritating!

[Zack Barresse]

... Not sure why it doesn't work here...

Because Mark Rowlinson programmed it that way. LOL! He wrote the PHP for the VBA tags. If you want to show it your way, you could always use the CODE tags..

As far as my above error handling goes, the big reason you may not want to exit sub is if you have a common clean up to do at the end - irregardless of any errors. If there is not a need for such a 'clean-up', I would also recommend the Exit Sub type of exit.

It ultimately depends on the flow of your routine. Each one is individual and different. so depending on your specific needs, the structure may (or may not) change. It is always best to test the code thoroughly and know exactly what will generate errors and what errors those are going to be. (Just don't forget to clear your errors!)

Good luck!

[Aaron Blood]

As far as my above error handling goes, the big reason you may not want to exit sub is if you have a common clean up to do at the end - irregardless of any errors. If there is not a need for such a 'clean-up', I would also recommend the Exit Sub type of exit.

You can put stuff like that in before the Exit Sub.
But OK, to each his own...



The code tag... hmmm... I only see a VBA wrapper.

You mean I actually have to edit it to say "code"... Ohhh, the workload... don't know if I can take it.

Sub Test() 
	 On Error GoTo ErrHand
	 Err.Raise 1004
	 Err.Raise 32
	 Err.Raise 50
Exit Sub
ErrHand:
	Select Case Err.Number
		Case 1004
			Err.Clear
			Resume Next
		Case 32
			Err.Clear
			Resume Next
		Case Else
			MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
			Err.Clear
			Stop
			Resume
	End Select
	
End Sub

Hmmmm... that seems to be too much spacing for a single indent. But at least it did do it right.

[austenr]

I was reading that the GoTo is not a good idea that you should always use On Error Resume Next. I guess everyone has an opinion.. Just trying to be as efficient and clean as possible. Thanks everyone who responded. Gives you something to ponder.

[austenr]

BTW...I have a counter in my program that counts the number of different occurances. I want to write the results of the counter to the end of a sheet. The problem is that the number of lines on the sheet varies depending on the number of errors found, file size, etc. How can I find the last unused row and drop one more row and write the contents of my counter? I know I am straying off the path of this thread but I just thought I might get some input.

[Zack Barresse]

Range("A65536").End(xlUp).Offset(1).Value = counter

Re: On Error Resume Next

This can be a good idea. It really depends on what you are trying to do. It should also be based off of the individual task while knowing exactly what errors you will be getting and where - and why. The large caveat with this statement is it is a catchall for all errors. It will bypass any errors and continue with the coding. If you need something specific to happen if a certain error is triggerred (e.g. a user did something they weren't supposed to), especially if this can happen at multiple places in your coding, such a statement might not be too beneficial.

I do use this statement on some things, but it is always with caution and after I have thoroughly debugged and troubleshot the code. My 2? humble opinion anyway.

[Aaron Blood]

I was reading that the GoTo is not a good idea that you should always use On Error Resume Next.

Where'd you read that?

The only good reason I could see doing that would be if you wanted to handle the same err.number differently depending on where it occurs in the code.


FF, I think he means using it in this context as opposed to just blanket applying it to the whole sub.

Sub MyMacro() 
On Error Resume Next
...code1 here
On Error Goto 0
...code 2 here
On Error Resume Next
...code 3 here
On error Goto 0
...code 4 here
End Sub

Where Code1 & 3 have blanket trapping and code2 & 4 do not include err handling. "On Error Goto 0" turns off "On Error Resume Next".

[Killian]

Hiya,

just thought I'd drop in a quick line about my favourite subject fo error handling.
I favour AB's method of catch ing errors and filtering the results with Select Case. Also useful for "guiding " the user in the right direction with message boxes.
And this one:

On Error Resume Next
 'TO DO: Find out what the hell's going on here before we send this to the client!!!?

... but I don't use that very often ;-)

[austenr]

I need to display the total of two counters at the end of a Sub using a MsgBox. Could someone point me to the code? Thanks.

[Zack Barresse]

Assuming your two variables are labeled 'counter1' and 'counter2', you may be able to look at something like this ...

MsgBox "Counter 1: " & counter1 & vbCrLf & _
        "Counter 2: " & counter2 & vbCrLf & vbCrLf & _
        "With a difference of " & counter1 - counter2, _
        vbInformation, "COUNTER"