Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between 'on error goto 0' and 'on error goto -1' -- VBA

Tags:

excel

vba

msdn

Can anyone find the difference between 'On error goto -1' and 'on error goto 0' in VBA? I've tried google and msdn, but I've had no luck.

like image 786
sterlingalston Avatar asked Jan 04 '13 14:01

sterlingalston


People also ask

What is On error GoTo 0 in VBA?

On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.

What is On error GoTo in VBA?

The On Error GoTo statement instructs VBA to jump to a line label and enter "error handling mode" whenever an unexpected error occurs at runtime. After handling an error, code can resume back into "normal" execution using the Resume keyword.

How do I stop a GoTo error?

To shut off (disable) the active handler, use On Error GoTo 0 . Doing so will close off the code block that uses that handler. Alternatively, exit the subroutine using Exit Sub , which automatically turns off the handler.

What is the use of an error GoTo statement in VB net?

The On Error GoTo 0 statement turns off error trapping. Then the On Error Resume Next statement is used to defer error trapping so that the context for the error generated by the next statement can be known for certain. Note that Err. Clear is used to clear the Err object's properties after the error is handled.


2 Answers

On Error GoTo 0 disables any error trapping currently present in the procedure.

On Error GoTo -1 clears the error handling and sets it to nothing which allows you to create another error trap.

Example: On Error GoTo -1

After the first error is raised, it will GoTo ErrorFound which will then clear the routine's error handling and set a new one, which will GoTo AnotherErrorFound when an error is found.

Sub OnErrorGotoMinusOneTest()      On Error GoTo ErrorFound      Err.Raise Number:=9999, Description:="Forced Error"      Exit Sub  ErrorFound:      On Error GoTo -1 'Clear the current error handling     On Error GoTo AnotherErrorFound 'Set a new one     Err.Raise Number:=10000, Description:="Another Forced Error"  AnotherErrorFound:      'Code here  End Sub 

Example: On Error GoTo 0

After the first error is raised, you will receive the error as error handling has been disabled.

Sub OnErrorGotoZeroTest()      On Error GoTo 0      Err.Raise Number:=9999, Description:="Forced Error"  End Sub 
like image 186
Francis Dean Avatar answered Sep 20 '22 19:09

Francis Dean


This answer addresses the confusion between the error object and the error handler.

The error object can be cleared using Err.Clear. This does not affect the error handler.

The error handler becomes enabled by using On Error Goto <label>. It becomes active when an error occurs.

While the error handler is active, you can not assign a new error handler. On Error Goto <label> will have no effect. VBA simply ignores the attempt to assign a new error handler.

Using Err.Clear does not cancel the error handler.

Jumping to a different place in the code using Goto <label> does not cancel the error handler. Using Goto <label> in an error handling block can cause confusion and should be avoided. You might think the error handler is no longer active when in fact it is still active.

The effect of an active error handler is that you can not assign a new error handler. On Error Goto <label> will have no effect. VBA simply ignores the attempt to assign a new error handler. Any additional errors will be unhandled while the error handler is active.

The only way to exit an active error handler is:

  1. Resume
  2. Resume Next
  3. Resume <label>
  4. On error goto -1
  5. exit the procedure

Using any one of these ways to exit the error handler will also clear the error object.

Excellent source: Pearson Error Handling In VBA Chip Pearson doesn't mention On error goto -1 in his article. To quote him:

I deliberately did not include On Error GoTo -1 because it serves no real purpose and can lock up the entire Excel application unless used in exactly the right way. Yes, On Error GoTo -1 is syntactically valid, but it is like giving a gun to drunk teenager. Nothing good will come from it.

You can also handle errors inline without using an error handler using the error object: MSDN Inline Error Handling

like image 29
D_Bester Avatar answered Sep 19 '22 19:09

D_Bester