PDA

View Full Version : On Error Resume Next ?



MWE
10-04-2005, 06:11 AM
I have a "cleanup" section at the end of a procedure that sets objects to nothing and closes files that may have been opened:CleanUp:
On Error Resume Next
Set xlCheck = Nothing
Set xlMaster = Nothing
Workbooks(MasterBook).Close SaveChanges:=False
If MasterBook is undefined, the last statement above triggers an error message:
RunTime Error 13
Type Mismatch

The problem is easy to fix:CleanUp:
On Error Resume Next
Set xlCheck = Nothing
Set xlMaster = Nothing
If MasterBook <> "" Then Workbooks(MasterBook).Close SaveChanges:=False
but I do wonder why the On Error Resume Next statement is inadequate for the first case

Tommy
10-04-2005, 06:59 AM
Do you have an On Error GoTo earlier? If you do I have had trouble with this senario. More than 1 On Error will cause a mix up. Sometimes I can get it to work with a On Error GoTo 0 to turn it off and then turn it back on. I am anal about turning it off in each sub/function, if I have turned it on, just because of this. I also clear all errors before exiting the sub/function. It will show up someplace else if I don't.

MWE
10-04-2005, 07:28 AM
Do you have an On Error GoTo earlier? If you do I have had trouble with this senario. More than 1 On Error will cause a mix up. Sometimes I can get it to work with a On Error GoTo 0 to turn it off and then turn it back on. I am anal about turning it off in each sub/function, if I have turned it on, just because of this. I also clear all errors before exiting the sub/function. It will show up someplace else if I don't.
thanks for the prompt reply.

No, I do not have an On Error GoTo earlier. Clearing all errors before exiting is a good idea. How do you do that? If there are no errors, and try to clear errors, do you spawn another error?

Tommy
10-04-2005, 07:50 AM
No I just do an err.clear as a sample blank sub/function:


Sub Sample()
'turn on error checking
On Error GoTo ERRORHANDLE
'turn off error checking
On Error GoTo 0
'I'm through go home
Exit Sub
'errors happen
ERRORHANDLE:
'what is it
MsgBox Err.Description
'clean it up don't leave it laying around
Err.Clear
'turn it off so it doesn't show up 8/9 subs/functions later/before
On Error GoTo 0
End Sub


This is how I handle it, more or less, but you get the idea. I developed this method due to unforseen errors showing up in the wrong place. I kept having to trace errors through several subs/functions because I didn't turn it off or clear errors that I did catch. I kept getting what I call false errors, they (errors) had actually occured elsewhere, so I started turning off error trapping when I left a sub/function and clearing errors etc. Now when an error happens I catch it where it happens and I spend more time coding and trying to figure out where my head was at that time :)

TonyJollans
10-04-2005, 08:17 AM
Might you have had an earlier (handled) error and not done a Resume? That would explain it.

Cyberdude
10-05-2005, 08:57 PM
Tommy, the Help facility is a little sketchy on the Err.Clear.
They say that it clears all the parameters that are normally given values when an error occurs, but it wasn't clear to me that it also deactivates a previous On Error statement. You are saying that it does indeed wipe out the effects of all previously executed On Error statements?

TonyJollans
10-06-2005, 01:06 AM
Tommy, the Help facility is a little sketchy on the Err.Clear.
They say that it clears all the parameters that are normally given values when an error occurs, but it wasn't clear to me that it also deactivates a previous On Error statement. You are saying that it does indeed wipe out the effects of all previously executed On Error statements?

No, Err.Clear does not wipe out the effects of previous On Error statements.

If an error has not occurred the only thing which changes the effect of an On Error statement is an error or another On Error ststement

If an error has occurred and an On Error statement has trapped it, a Resume statement MUST be executed before further error trapping in the same procedure will happen. The Resume can be part of the original On Error statement (On Error Resume ....) or it can be a separate statement at some point after the error has been trapped.

Cyberdude
10-06-2005, 04:57 PM
Hey, Tony, thanx for the reply. I didn't make myself clear when I said "wipe out the effects of all previously executed On Error statements". What I was trying to say is this:
If I execute an On Error statement, then the user's trapping mechanism is enabled, and any subsequent appropriate error will be handled in the fashion dictated by the On Error statement. If no error occurs, then I execute Err.Clear, will that disable the user trapping mechanism so that any subsequent appropriate errors will be handled by the system's error handling mechanism? It's not clear that Err.Clear will do that. It looks like it just cleans out the descriptive stuff that is stored as a consequence of an error occurring. I would guess that the last On Error statement that executed will still be in effect. I probably guessed wrong. http://vbaexpress.com/forum/images/smilies/119.gif

Later: Hmmm, upon rereading your statement, maybe you did understand. But you raised another question. You said something about a second On Error statement executing after the execution of a first one. What is the effect of THAT. Does the second one override the first?

TonyJollans
10-06-2005, 11:35 PM
Hi Sid,

You are correct about Err.Clear. It clears out details of the last error; it does not reset traps.

Yes, a second On Error statement in the same procedure overrides the first. But only if you are not currently in an active trap.

Cyberdude
10-08-2005, 10:26 AM
Tony, thanx for being patient with me on this error stuff, but I've always found it to be intimidating. I DO think I've got it all straight now. But let me confirm one last point regarding a second On Error statement replacing a preceding one.
My tests seem to show that a second On Error statement that appears in an active error handler is just ignored. It doesn't cause a compile error and it doesn't trigger a run-time error. In fact upon entry to the error handler, the original On Error statement is disabled so that an identical error in the error handler doesn't cause an infinite loop. Said differently, upon entry to an error handler, all preceding On Error statements are disabled. Error handling is done by the system. Is this correct?

TonyJollans
10-08-2005, 11:12 AM
Hi Sid,

You are correct in all but one minor detail (see below)

I feel another article coming on :)

You have to be careful with terminology - I hope it comes clear..

First off - you won't get compile errors. It is valid to code as many On Error statements as you like.

Secondly - you won't get run time errors either! Whether an On Error statement has an effect depends on one thing only - is there an active error handler at the time.
If there is no active handler, an on error will enable one and disable a previously enabled one.

If there is an active handler, an on error will have no effectNow, what the On Error statement means is:
When there is an error execute the statement which follows "On Error".The only statements which are allowed following the On Error are Goto and Resume, so one of these wil be executed following an error.

If a Resume is executed, that's the end of the matter as far as VBA is concerned; what you actually do in your code is up to you but VBA has no more interest as far as error-handling goes and the error handler remains in effect; if there is another error the error handler will again be triggered.

If a GoTo is executed the error handler which was previously enabled becomes active; when it is active it is no longer enabled. If another error occurs while an error handler is active, VBA will look up the calling chain for an enabled error handler and execute the first one it finds (which may be - and in your tests obviously was - the system handler).

An active error handler remains active until a Resume is executed - or the procedure ends (when the error handler within it becomes inactive and disabled). A Resume does two things - it executes a GoTo to the label (or line number) given in the Resume statement and it deactivates and re-enables the error handler.

Cyberdude
10-09-2005, 11:36 AM
Tony, you certainly have a gift for explaining the complicated.

Now, still another question. This is nit picking, but I?ve wondered why an error handler has four possible states (enabled, disabled, active, and inactive) when it would seem that only two (active and inactive) would suffice. Why does an error handler have to be enabled? The only situation I can come up with is one like the following:
On Error GoTo LabelA ?(Enables the error handler)
?
GoTo LabelX
?
LabelA:
?
LabelX:
Range(?A1?).Select ?<-(any statement)

?
In other words, the coder has some lines following LabelA that he wants to execute even though an error hasn?t occurred. I ran a test on this case, and it worked normally. I assume in this case that the Range statement is within an enabled, but inactive, error handler. Wouldn?t it be sufficient to think of the error handler as just being inactive? How does the enabled state affect things??

TonyJollans
10-09-2005, 03:36 PM
As you noted in an earlier post, active/inactive is merely a device to stop infinite loops. Enabled/disabled is whether or not you have any error traps in effect in the current procedure. There are actually only three states in total (active/disabled, inactive/enabled, inactive/disabled). It is also worth noting, perhaps, that the Err object is always available to you whether or not you have any error trapping and whether or not you have had an error.

I think what you are missing is that, as far as VBA is concerned, there isn't a whole load of specific error-handler code; the only statement which is executed as a result of an error is the one which follows the On Error statement (either the GoTo or the Resume).

I'm more comfortable in Word these days but, if you have, for example:
On Error Goto LabelErr:
Worksheets("DoesExist").Activate
Worksheets("MightExist").Activate
Worksheets("AlsoExists").Activate
LabelErr:
MsgBox "There has been an error"Then ...
If sheet "MightExist" does exist then sheet "AlsoExists" will be activated.
If sheet "MightExist" does not exist then sheet "DoesExist" will remain activated.
You will always see the message box whether or not sheet "MightExist" exists.The MsgBox statement is not specific error handling code and if program flow dictates, then VBA is happy to execute it regardless of whether there has been an error. What happens when there is an error is that program flow is changed; instead of executing the statment after the erroring statement, the GoTo after the On Error is executed. What code you want to branch to, or round, is entirely up to you and nothing, directly, to do with errors. It is your responsibility to make sure that code you write for handling errors is only executed when there has been an error; VBA does not do that for you.

I hope that hasn't muddied the waters too much

johnske
10-10-2005, 03:29 AM
Hi Cyber,

To add to what Tony has said in a couple of posts above, you always need a 'Resume' statement of some kind to clear errors so code execution can proceed.

You're "allowed" to have one error so you can proceed on to some error handling procedure, if you don't "handle" the error in some way a second error is usually fatal. However, a single 'On Error Resume Next' can allow code execution to proceed despite multiple errors (at least until error-handling is cancelled by an On Error Goto 0).

This approach is shown in the first piece of code, and while it may be suitable for a finished procedure, when developing the procedure you should write code to trap all individual arrors (similar to the 3rd example below)Sub ErrorTest()

'in here we're assuming the sheets below "DoesExist",
'"MightExist", and "AlsoExists" are ALL NON-existent...

On Error Resume Next '< this's the only error-handling statement

'1st statement
Worksheets("DoesExist").Activate '< this raises an error

'the 'Resume' above now clears the 1st error and
''next' directs execution first to the error-message
'(directly below) and then on to the 2nd statement
MsgBox Err.Description & 1 '< describes the 1st error

'2nd statement
Worksheets("MightExist").Activate '< this raises an error also
'the 'Resume' above now clears the 2nd error and
''next' directs execution first to the error-message
'(directly below) and then on to the 3rd statement
MsgBox Err.Description & 2 '< describes the 2nd error

'3rd statement
Worksheets("AlsoExists").Activate '< this raises an error also

'the 'Resume' above now clears the 3rd error and
''next' directs execution first to the error-message
'(directly below) and then on to the 4th statement
MsgBox Err.Description & 3 '< describes the 3rd error

'4th statement
'execution now proceeds to End Sub
End SubYou may think that 'Err.Clear' clears errors but in fact it doesn't, the following is an example of that
Sub ErrorTest1()

'in here we're assuming the sheets below "DoesExist",
'"MightExist", and "AlsoExists" are also all NON-existent...

line1:
On Error GoTo Err1
Worksheets("DoesExist").Activate '< this raises an error

line2:
'here we may expect that the 1st error has been cleared
On Error GoTo Err2
Worksheets("MightExist").Activate '< this raises a 2nd & fatal error
'showing that the 1st error has
'NOT been cleared...
'(code execution does not get to this line)
line3:
On Error GoTo Err3
Worksheets("AlsoExists").Activate
Err1:
MsgBox Err.Description & 1
Err.Clear '< we expect this to clear the 1st error
GoTo line2

Err2:
MsgBox Err.Description & 2
Err.Clear
GoTo line3

Err3:
MsgBox Err.Description & 3
Err.Clear

End SubThis type of individual error-handling needs a 'Resume' to actually clear the error, as demonstrated here...
Sub ErrorTest2()

'in here we're assuming the sheets below "DoesExist",
'"MightExist", and "AlsoExists" are also all NON-existent...

line1:
On Error GoTo Err1
Worksheets("DoesExist").Activate '< this raises an error

line2:
On Error GoTo Err2
Worksheets("MightExist").Activate '< raises a 2nd error

line3:
On Error GoTo Err3
Worksheets("AlsoExists").Activate '< raises a 3rd error

Exit Sub
Err1:
MsgBox Err.Description & 1
'clears error1
Resume line2

Err2:
MsgBox Err.Description & 2
'clears error2
Resume line3

Err3:
MsgBox Err.Description & 3
'clears error3
Resume Next

'N.B. 'End Sub' also clears error3
End Sub

HTH,
John :)

Cyberdude
10-10-2005, 11:59 AM
Hey, John, groovy reply. I've copied it to my On Error collection and will study it. I'm accumulating a lot of stuff on this subject. Who knows ... I might even eventually become comfortable with it. Now I'm trying to understand when to use "Resume" vs "On Error GoTo 0". It's not completely clear to me what the differences are. I'll keep fooling with it. Thanx again. :beerchug:

johnske
10-10-2005, 12:45 PM
The 'On Error Resume Next' or 'On Error Goto (somewhere)' sets up an error trap. When you want to exit from/cancel error-trapping use the 'On Error Goto 0'

:)

Cyberdude
10-10-2005, 01:04 PM
OK, but here's my problem: I thought that the statement "Resume line" also "exited/cancelled error trapping". So when do you use "On Error GoTo 0" vs "Resume line"? :bug:

TonyJollans
10-10-2005, 01:45 PM
Hi Sid,

The basics ...

There are three mutually exclusive states (within a procedure):
Error Trapping Totally Disabled
Error Trapping Enabled (but no trapped error)
Error TrappedResume can only be executed after an error has been trapped - and only once per trapped error.
When Resume is executed it exits Error Trapped mode and returns to Error Trapping mode.

On Error Goto 0 disables Error Trapping mode
On Error (anything else) enables Error Trapping mode - and provides the statement to be executed when an error occurs

On Error can not be executed in Error Trapped mode
Resume can only be executed in Error Trapped mode

Does that help at all?

johnske
10-10-2005, 01:56 PM
OK, but here's my problem: I thought that the statement "Resume line" also "exited/cancelled error trapping". So when do you use "On Error GoTo 0" vs "Resume line"? :bug:No, 'Resume' clears the error, it doesn't cancel/exit the error-trap, that's why I used the first example to show that error-trapping is still active for three consecutive errors when a single 'On Error Resume Next' statement is used.

The idea of cancelling/exiting an error-trap (which may be merely giving an error message) with 'On Error Goto 0' is usually so you can set another trap for a different part of the code requiring that you respond to this error with a different course of action such as Opening another workbook.

HTH,
John :)

Cyberdude
10-11-2005, 11:21 AM
Guys, you've done an outstanding job of leading me through the forest. I can't begin to thank you enough for taking your valuable time. I do believe I've got it now.
And I want to apologize to mwe for stealing his thread.
Sid