Resume On Error GoTo 0 -1 GoTo Error Handling Statements Runtime VBA Err Handling ORNeRe GoRoT N0Nula 1

[DocAElstein]

On Error GoTo LabelOrLineNumber only works once

This post demonstrates the classic pit fall which often leads to many of us learning about Runtime Error and Runtime Error Handling VBA. That is certainly how I first came across it

On Error GoTo LabelOrLineNumber only works once
Correct. - We know that now, don't we?
Just to be sure …. Consider the following codes.

In words this is what the following two codes were initially intended to do:
The idea is to loop through 5 numbers, 1 2 0 5 0 , and each one becomes the denominator in a simple equation dividing 10 by that denominator. ( In the actual data this such a looping would be expected to do these calculations 10/1 10/2 10/0 10/5 10/0 )
We are expecting that there may be some zeros used in the 5 numbers which would result in an error of "divide by null" ( We actually included 2 zeros in the actual test data to test this and the codes reaction to those two 0s )
So we thought we would do this:
We have an error handler that goes to an error handling code section. At that code section we will include a message box which will tell us that we have a problem with the current number in the denominator. ( It will tell us that our current number is 0 )… Having informed of the problem number, we go back to consider the next number. ( To facilitate this we put a label, Nxt in the code , and we send the code back to there after the informing message box.
Sounds easy, and we wrote these codes to do it

Sub OnErrorGoTo_OnlyWorksOnce()  '                                                                                '  https://excelfox.com/forum/showthread.php/2239-Resume-On-Error-GoTo-0-1-GoTo-Error-Handling-Statements-Runtime-VBA-Err-Handling-ORNeRe-GoRoT-N0Nula-1?p=10553&viewfull=1#post10553
 On Error GoTo ErHndler
Dim MyNumberStearingForNextLoop As Variant '                                 Stearing element for a For Each loop must be Variant or Object Type
     For Each MyNumberStearingForNextLoop In Array(1, 2, 0, 5, 0) '          This is read and Array(1, 2, 0, 5, 0) held the first tim in Loopp register to be accesed at each loop
      MsgBox 10 / MyNumberStearingForNextLoop: Debug.Print 10 / MyNumberStearingForNextLoop
Nxt: Next MyNumberStearingForNextLoop
'
Exit Sub ' Skip the "error handling code section" for a code run without an error - this never happens for this code
ErHndler: ' The "error handling code section" is from here until the End ==
 MsgBox "Problem with number " & MyNumberStearingForNextLoop: Debug.Print "Problem with number " & MyNumberStearingForNextLoop
 GoTo Nxt
End Sub
Sub OnErrorGoTo_StillOnlyWorksOnce()
 On Error GoTo ErHndler
Dim MyNumberStearingForNextLoop As Variant '
     For Each MyNumberStearingForNextLoop In Array(1, 2, 0, 5, 0) '
      MsgBox 10 / MyNumberStearingForNextLoop: Debug.Print 10 / MyNumberStearingForNextLoop
Nxt: Next MyNumberStearingForNextLoop
'
Exit Sub  ' Skip the "error handling code section" for a code run without an error - this never happens for this code
ErHndler: ' The "error handling code section" is from here until the End ==
 MsgBox "Problem with number " & MyNumberStearingForNextLoop: Debug.Print "Problem with number " & MyNumberStearingForNextLoop
 On Error GoTo ErHndler: ' This has no effect on the overal finctioning of the coding, as I am sexually aroused already,  in the State of Exception in Transalvania
 GoTo Nxt
End Sub

I think anyone who knows basic VBA but is not yet familiar with how VBA organises its error handling might intuitively expect that at least one of the codes will give these results, for example in the Immediate window (From the VB Editor Ctrl+g to get that window displayed)

10 
 5 
Problem with number  0 
 2
Problem with number  0

The code almost does this, but we find that the codes stop via the standard default VBA error handling. It does this at the second time that an attempt is made to divide by 0
This is because the exception was raised at the first attempt at dividing by 0 . ….
Just to refresh our memories:
The user predefined error handler , On Error GoTo ErHndler , is responsible for "embedding" the code in the Exception Software at the time of the error, starting at ErHndler. It does not simply "tell" VBA always to Go To ErHndler at every thing that causes an error.
So the first code has no instruction to do any "re routing" again in the exception state***. The exception software, I assume, is wired to use the standard default error handling for further errors.
*** Note importantly for later discussions: VBA does have the user defined error handling, On Error GoTo ErHndler , stored / registered, and will continue to use that, if we could get out of the exception state, ( which we can and that will be discussed later )
Because we are in the exceptional state of aroused erection, any further attempts to set a user defined error handler are superfluous and ignored. Hence the second code also reacts with the standard default VBA Error handling at the second attempt at divide by zero: The second On Error GoTo ErHndler code line ( the one in the error handling code section is effectively simply ignored