On Error

[Mike H.]

If I place the statement "On Error Goto 0" at the top of a sub and that sub
calls other subs, does the error handling carry through? If one of the
called subs has a statement "On Error Goto ErrorHandle" and then when I
return from that sub, is the original error handling for the "home" sub still
in effect?

 

[Chip Pearson]

Error handling travels upwards though the chain of called procedures. If AAA
calls BBB and BBB has no On Error handler, then the error handler in AAA is
used if an error occurs in BBB. If BBB has an error handler and an error
occurs in BBB, that own error handler is used. If AAA calls BBB and BBB
calls CCC, and BBB has an error handler, an error in CCC will cause the
handler in BBB to run. If BBB has no error handler, the handler in AAA is
used.

See http://www.cpearson.com/Excel/ErrorHandling.htm for more info.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Mike H.]

If I set up the ONLY Error handler in AAA and one choice the user has will
issue a resume next command, and I get an error in BBB, the resume next
command resumes on the next command after BBB was called, not the next
command WITHIN BBB. Without testing further, I would assume, that an error
in CCC would resume next back to where BBB was called (CCC was called within
BBB). Is this correct?

 

[Chip Pearson]

If the only error handler is in AAA, that error handler will be invoked by
an error in any subordinate procedure. Thus, if AAA calls BBB which calls
CCC, and an error occurs in CCC, BBB is terminated altogether is execution
passes directly from the error in CCC to the error handler in AAA. If the
error handler in AAA is Resume Next, execution returns to the line after the
call to BBB.

For example, look at the following code. AAA has the only error handler. AAA
call BBB which calls CCC which deliberately raise a DIV/0 error. Execution
goes directly from the error in CCC to the line after the call in AAA to
BBB. Therefore, the output in the Immediate window is

1 In AAA
1 In BBB
1 In CCC
2 In AAA

which clearly shows that BBB is skipped over by the error handling logic.
(If execution were to go back to BBB after the error in CCC, you'd see the
"2 In BBB" output.

Sub AAA()
On Error Resume Next
Debug.Print "1 In AAA"
BBB
Debug.Print "2 In AAA"
End Sub

Sub BBB()
Debug.Print "1 In BBB"
CCC
Debug.Print "2 In BBB"
End Sub

Sub CCC()
Debug.Print "1 In CCC"
Debug.Print 1 / 0 ' deliberately raise an error
Debug.Print "2 In CCC"
End Sub

If you did in fact put an error handler in BBB, the error in CCC would use
that error handler (because it is the most recently error handler made
active) and the code would go back to BBB, not AAA.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Mike H.]

Since this discussion, I spent much of yesterday modifying the code of a big
project to have error handling routines in each sub. But when the user runs
the program, they SOMETIMES and sporatically get "project execution has been
interrupted." The choices are continue or end. Debug is not available as
the project is protected and locked. The next time they go in to excel and
run the project, it does NOT give any of these errors. Sometimes they may
get 40 during the running. Any ideas why this would happen? Could it have
anything to do with the protection?