Solved: Regarding the Use of Resume

[Cyberdude]

Regardless of what the course of action is, before you proceed any further with code that may raise another error you really need to clear the first error. This is where the Resume statement comes into play. If you want to continue code execution from (say) a line you?ve named Line5, you now need to put Resume Line5 as the next line of your code.

In his excellent article ?Error Trapping and Handling? http://xlvba.3.forumer.com/index.php?showtopic=17, johnske suggests that upon entry to your error handler code, the first thing you should do is ?clear? the currently active error and disable the error handler. He says that this is best done by using Resume (line) or Resume Next. Curiously, his example didn?t do exactly that:
[vba]Sub DummySub1()
?(some code here)
On Error GoTo MyErrorHandler
Workbooks.Open(?Myworkbook.xls?)
?(some code here)
Line5:
?(alternative code here)
Exit Sub
MyErrorHandler:
MsgBox ?Can?t find the workbook ? & ?MyWorkbook.xls?
Resume Line5
End Sub[/vba] Notice that he placed the ?Resume Line5? AFTER the ?MsgBox? statement. This is not a criticism, since I can see that his example is showing how to handle an error, then to go back into the main code and continue executing. Still, it does violate the recommendation he made, since it is certainly possible (not likely) for an error to occur within the MsgBox statement. Should that occur, it would seem that an infinite loop would begin, since the still active On Error GoTo statement (early in the code) would transfer execution to MyErrorHandler again, and so on. (Please correct me if I?m wrong about this.)

My question is, should you write the sequence something like the following:
[vba] MyErrorHandler:
Resume Contin
Contin:
MsgBox ?Can?t find the workbook ? & ?MyWorkbook.xls?
Resume Line5
End Sub[/vba] It seems klutzy to put the target label immediately following the Resume statement, yet that seems to be what is needed. However, that means that there is no longer an active error handler, so the second Resume would probably cause an error because it is being used outside of an active error handler. That suggests that (in my code above) the Resume Line5 should be replaced by GoTo Line5. Do I have a correct understanding of all this??

[mdmackillop]

This will error within the error handler, but it goes to debug, not a loop
[VBA]Sub DummySub1()
'(some code here)
On Error GoTo MyErrorHandler
Err.Raise 6
Workbooks.Open ("Myworkbook.xls")
'(some code here)
Line5:
MsgBox "resumed"
Exit Sub
MyErrorHandler:

MsgBox "Can’t find the workbook " & "MyWorkbook.xls"
Workbooks.Open ("Myworkbook.xls")
Resume Line5
End Sub
[/VBA]

[johnske]

... the first thing you should do is ?clear? the currently active error and disable the error handler. He says that this is best done by using Resume (line) or Resume Next. Curiously, his example didn?t do exactly that...

Perhaps that could've been worded better - the first thing to do of course is to 'handle' the error before the main body of code is executed. In the very simple example given, 'handling the error' merely consists of informing the user that there was an error and the nature of it, then clearing the error before branching off to the alternative action (in case the alternative action also raises an error that would be immediately fatal without the Resume statement).

[Cyberdude]

Thanks for the replies, guys!

John, the changed example that I proposed:
[vba]MyErrorHandler:
Resume Contin
Contin:
MsgBox "..."
GoTo Line5[/vba] Is this a reasonable sequence ... anything wrong or undesirable about it?

[Ken Puls]

[vba]MyErrorHandler:
Resume Contin
Contin:
MsgBox "..."
GoTo Line5[/vba] Is this a reasonable sequence ... anything wrong or undesirable about it?

I don't see what the point is there. Why add another line that never has any point to slow you down? Granted it's a fraction of a second, but it just seems like bloat to me.

Actually, while I won't argue that Goto statements have their uses, I try to avoid them wherever I can. I don't think I've ever used more than one in a routine, and that only to make sure that objects are set to nothing before I bail out. I find that having multiple statements scattered throughout the file jumping up and down... it makes the code very hard to follow. I'll put in a significant amount of time to break up my routines, then organize them with with if/then and select case structures to avoid using Goto's everywhere.

Again, I'm not saying that I'm right, here, that's just my style. In fact, I JUST spent a half hour re-writing a routine to avoid just this issue.

Just my 2 cents.

[johnske]

... Is this a reasonable sequence ...

Hi Sid,

IMO: No, not reasonable, this is going a bit too far. It's unlikely that asking Excel to display an error message will cause an error, but if it does, it's also unlikely to be of our making. In such cases we would probably be best served by simply closing and re-opening Excel or (maybe) do a "Detect and Repair"...

We put error trapping in place to cater for any reasonable action the user may take that will cause our code to crash and generate errors. i.e. we may take certain actions in a particular order to do something and our procedure works fine when we do that. But we need to cater for users that may not do things in the same order that we do them.

As a simple example - we may have a procedure B that, during the course of the procedure opens a workbook and leaves it open. Procedure A on the other hand opens the same workbook and closes it before the procedure ends. We may expect that users will use procedure A 1st and B 2nd, but unless we either hard-code it so that it must work that way, or have an error-handling procedure, they'll get an error if they use B before A. This is to be expected and we should anticipate these sorts of things.

In other words we usually only need to anticipate all sorts of 'reasonable actions' that could cause our code to generate an error, not errors caused by the 'system' code - the system has its own error-handling procedures.

Regards,
John

[Bob Phillips]

Sid,

I have to go with Ken here, keep those goto's down to a mimimum. I use a standard error handling methodology in my code that goes along the lines of

- in the initiating procedure, set the error handler to a routine at the end of that procedure (I am considering even having an error handler procedure)

- have a goto at the end of that procedure to avoid an automatic drop into the error handler

- in all called procedures, do NOT reset the error handler except to temporarily bypass expected failures using On Error Resume Next ... On Error Goto 0

- set variables for the current module and procedure names

For example,

[vba]

Option Explicit
Option Private Module

Public Sub TopLevelSub()

sModuleName = "gmModule1"
sProcedure = "TopLevelSub"

On Error GoTo tpl
'some code such as
Call tlCalledSub1
Call tlCalledSub2

GoTo tl_Exit

tl_ErrHandler:
MsgBox "Module: " & sModule
MsgBox "Procedure: " & sProcedure
MsgBox "Error num: " & Err.Number
MsgBox "Error description: " & Err.Description
'etc.

tl_Exit:
'tidy up code such as clearing objects
'resetting events, screenupdating
End Sub

Public Sub tlCalledSub1()

'some code that might error and if it does, it flies
'off to tl_ErrHandler

End Sub

Public Sub tlCalledSub2()

'some code

On Error Resume Next
Set oWB = Workbooks("IsThisWorkbookOpen.xls")
On Error GoTo 0
If Not oWB Is Nothing Then
'more code
End If

End Sub
[/vba]

[Cyberdude]

I think you guys missed the point I was asking about. In the oversimplified example, I showed a single MsgBox statement. I the real world that could be a considerable amount of code to do some sort of backout or cleanup before exiting. In such a case, the possibility for another error can be quite real, and I would certainly want the original error cleared before a secondary error occurs.

As for your need to not use GoTo statements, how logically does a Resume Line5 differ from a GoTo statement?? Both transfer control.

[johnske]

... the real world that could be a considerable amount of code to do some sort of backout or cleanup before exiting. In such a case, the possibility for another error can be quite real, and I would certainly want the original error cleared before a secondary error occurs...

In the error handling routine it's up to the coder at this point to provide safe code that won't error out. The intent of an error-handling routine is to handle errors, not create them. If you have an error-handling routine that CREATES another error you've obviously failed to meet this objective.

BTW: Here's what Help has to say about Resume...

Resume Statement

Resumes execution after an error-handling routine is finished

Note the last bit - the intent is " after an error-handling routine is finished".

[Ken Puls]

I think you guys missed the point I was asking about. In the oversimplified example, I showed a single MsgBox statement. I the real world that could be a considerable amount of code to do some sort of backout or cleanup before exiting. In such a case, the possibility for another error can be quite real, and I would certainly want the original error cleared before a secondary error occurs.

Sure, but your code snippet that I was taking aim at didn't have anything to do with the msgbox:

[vba]MyErrorHandler:
Resume Contin
Contin: [/vba]

To me, this is the equivalent of having Line 1 say Goto Line 2, then doing something worthwhile. Resume Contin is an unnecessary waste of 13 characters.

As for your need to not use GoTo statements, how logically does a Resume Line5 differ from a GoTo statement?? Both transfer control.

Sure. They skip you down, then the resume sends you back up, then you might get another error that sends you down, then resume back up... pretty soon you're lost in the spaghetti code. I don't recall ever having needed to use a resume to send myself back into a procedure. As Bob says, I'll use a quick:

[vba]On Error Resume Next
'do something
If Err.Number <> 0 Then
'do something else
End if
On Error Goto 0[/vba]

[johnske]

Sure, but your code snippet that I was taking aim at didn't have anything to do with the msgbox:

[vba]MyErrorHandler:
Resume Contin
Contin: [/vba]

To me, this is the equivalent of having Line 1 say Goto Line 2, then doing something worthwhile. Resume Contin is an unnecessary waste of 13 characters.



Sure. They skip you down, then the resume sends you back up, then you might get another error that sends you down, then resume back up... pretty soon you're lost in the spaghetti code. I don't recall ever having needed to use a resume to send myself back into a procedure. As Bob says, I'll use a quick:

[vba]On Error Resume Next
'do something
If Err.Number <> 0 Then
'do something else
End if
On Error Goto 0[/vba]

Hi Ken,

Not quite the same thing. You're only allowed one error at a time in Visual Basic, so once your error-trap has been sprung you need to reset it, and that's exactly what Resume does - it resets the error trap. If you don't use Resume to reset the trap a second error is immediately fatal.

Also, if you use Resume in place of a Goto when there's no error, this creates an error in its own right.

Regards,
John

[Ken Puls]

Not quite the same thing. You're only allowed one error at a time in Visual Basic, so once your error-trap has been sprung you need to reset it, and that's exactly what Resume does - it resets the error trap. If you don't use Resume to reset the trap a second error is immediately fatal.

Right, okay. I was aware of that. Basically, I use the construct above most of the time, as what I am going to do after testing the error is straight forward and won't error out.

If I'm testing an error, or could run into an error in my test, I'll assign the error number to a variable so that I can play with it:

[vba]On Error Resume Next
'do something
lError = Err.Number
On Error Goto 0
If lError <> 0 Then
'do something else that could trigger another error
End If[/vba]
This, of course, because using "On Error Resume Next" also has the side effect of clearing the error. I can also test the error number in a Select Case statement and act differently depending on the error, should I choose.

Also, if you use Resume in place of a Goto when there's no error, this creates an error in its own right.

Honestly, didn't know that, as I don't use Resume statments with the exception of the above noted. To clarify, though, "On Error Resume Next" doesn't raise an error, even if you run it standalone.

I totally understand that clearning an error (resetting) is required AFTER an error has been encountered. What I disagree with is the use of Resume in this example...
[vba]

MyErrorHandler:
Resume Contin
Contin:

[/vba]
...as well as jumping all over the code using Goto and resume statements. Again, there are times for everything, but I feel that with proper organization, logic and design, you can avoid most of them. Break up your subs, for example, and call them if an error is encountered, rather than sending your user to the bottom, then back to the top. The effect on the code is the same, but it makes it MUCH easier to read, follow and debug.

[Simon Lloyd]

I have just read everything you guys were talking about..................now my brain cell hurts..............and i am still none the wiser about error handling!, i am quite a novice at VBA and have been told in the past not to use On Error Resume Next as it ignores all errors but you all made it sound like a soft option that doesnt ignore all errors. In a nutshell if wanted to add error handling to my code (simple as it may be) is it as simple as

On Error Goto Xit
Xit: MsgBox "Error!"
If blah! then
Do something
End if

I'm still confused!

Regards,
Simon

[johnske]

Hi Simon,

On Error Resume Next certainly has its place in error handling, as does On Error Goto MyErrHandler.

The best course is to make your code bullet proof by always testing the environment before executing code that may give rise to an error, and than writing code to handle all eventualities. Naturally, this path means a fair degree of experience and more than a lot of testing and head-scratching as, for example, a simple procedure to open a workbook and copy some data to it then involves writing code to see if the workbook even exists (we get an error if it doesn't) and to create a new workbook if it doesn't. As well as that we should also test that an existing workbook is not already open (otherwise we get an error from trying to open an open workbook).

A simpler alternative is usually to use some sort of error-handling procedure(s) which may be as simple as exiting the procedure when an error occurs...

It's more a matter of identifying how far you need to take bullet-proofing your code, how much time you want to spend writing and testing it, then determining what approach best suits the task at hand.

Have you followed the link that Cyberdude gave in his 1st post and read the article? - Don't expect to understand it all in one go though

Regards,
John

[Bob Phillips]

I have just read everything you guys were talking about..................now my brain cell hurts..............and i am still none the wiser about error handling!, i am quite a novice at VBA and have been told in the past not to use On Error Resume Next as it ignores all errors but you all made it sound like a soft option that doesnt ignore all errors. In a nutshell if wanted to add error handling to my code (simple as it may be) is it as simple as

On Error Goto Xit
Xit: MsgBox "Error!"
If blah! then
Do something
End if

Simon,

Nothing wrong with On Error Resume Next as long as you reset it after the code could error in a controlled manner is completed, i.e. don't leave it open any longer than you need to.

If you set an error handler at the first point of entry for your code (it may have many first points in an application for different functions), and a errorhandler statement and code in that same procedure, any failure later on, including called modules, will jump back to the error handler in the top module. An On Error Resume Next will (temporarily) stop that jump back, which is why you need to reset as soon as is possible.

IMO that is all you will ever need in code, I cannot envisage a situation where I would write code that says Resume line_number.

[Simon Lloyd]

All hail the "Great Xid"!, nice to hear from you but as a novice it didnt really clear a lot of things up, Johnske made sense with his comment around error handling when creating a new workbook ( i have a question posted earlier today which includes creating a new workbook) where he said you need error handling to check for the existance of a workbook, check is it open/closed, i understand the need for all that (not sure how to accomplish it). I have read many times that error handling should be built in.......so being nieve i always put On Error Resume Next in each module, then struggled trying to find out why my code didnt seem to work.........from there on in i haven't used error handling.............a kind of suck it and see mentallity. Do you or anyone have a standard (for the novice) framework for error handling that even I could use easily?

Regards,
Simon

[Bob Phillips]

Simon,

You are doing the worst thing you could possibly do

See my post earlier in the thread.

[Dave]

Simon... perhaps Richie's post here will help. Dave
http://www.mrexcel.com/board2/viewto...7982&highlight=

[mdmackillop]

Hi Simon,
Why not post a sample of code to which error handling can be applied. That should give you an insight for the type of code you're creating
Regards
MD