This possibly confirms once again what I have said a few times: .. Something along the lines of…
… Error handling in VBA is not difficult, in fact the subject is extremely easy and anyone understanding very basic VBA coding can check out and prove/ learn things about anything related to Error handling in VBA with a few simple code lines. But the big problem is that the actual permutations of what can / might happen in coding involving Error handling in VBA is so many that no one has the time or interest to either do, or read, the necessary long Blog it would require.
I lost count how many times I thought I had it all sussed, and then noticed some permutation I missed. It looks like that has happened again.
On Error GoTo 0 is a bit different to what I thought?
The average documentation, Blogs, forum answers regarded as correct etc., will start along these lines:
….Disables enabled error handler in the current procedure ……… the literature may or may not then go to say something else, but rarely goes on to tell the full story accurately.
I think I have it now sussed, (…well until the next time I get caught out with something else I missed, Lol. )
This would be a good summary statement in my opinion
….Disables user enabled error handler in the current procedure, and may effectively cause revertion back to standard default VBA Error handling.
Here’s the quirk: ( or thing I had missed )
First here is what I think those moderately confident in error handling in VBA will know.
_ For a simple routine, any user enabled error handlers* will effectively be disabled by On Error GoTo 0, and revertion is to the standard default VBA Error handling. (*A user enabled error handler will be replaced by any subsequent user enabled error handler, so On Error GoTo 0 is tanking the last one, but the previous one(s) effectively ‘aint there anymore neither).
This coding demos that basic idea
Code: Select all
Sub OnErrorGoToNullSimpleExplanation1()
On Error GoTo ErrHandlrCoding1 '
On Error GoTo 0 ' In simple terms, this makes it as if I never had the last code line
On Error GoTo ErrHandlrCoding1 '
On Error GoTo ErrHandlrCoding2 ' As far as I know, this effectively replaces ErrHandlrCoding1 so also effectively makes it as though the last code line was no done
On Error GoTo 0
Dim Cnt As Long
Let Cnt = 1 / 0
MsgBox prompt:="You won't ever see this, because VBA stops and chucks up an error at the last code line"
Exit Sub
ErrHandlrCoding1:
MsgBox prompt:="You won't ever see this"
Exit Sub
ErrHandlrCoding2:
MsgBox prompt:="You won't ever see this"
End Sub
Sub OnErrorGoTo0SimpleExplanation2()
On Error GoTo ErrHandlrCoding1 '
On Error GoTo 0 ' In simple terms, this makes it as if I never had the last code line
On Error GoTo ErrHandlrCoding1 '
On Error Resume Next ' As far as I knew this effectively replaced ErrHandlrCoding1 so also effectively makes it as though the last code line was no done
On Error GoTo 0
Dim Cnt As Long
Let Cnt = 1 / 0
MsgBox prompt:="You won't ever see this, because VBA stops and chucks up an error at the last code line"
Exit Sub
ErrHandlrCoding1:
MsgBox prompt:="You won't ever see this"
End Sub
_ Now, The next caught me out and I expect could others
Consider an initial routine with user enabled error handling, and that routine then calls a second routine. Let’s say that the second routine has its own user enabled error handler, but that is then disabled via On Error GoTo 0 in that second routine. If a further error occurs in that second routine , it will not revert to the standard default VBA Error handling. Rather it will be handled by the user enabled error handling in the first calling routine, as demoed in the next coding
Code: Select all
Sub OnErrorGoBackToLastErrHamdlr1()
On Error GoTo ErrHandlrCoding1
Call ErroringRoutine1
Call ErroringRoutine2
Call ErroringRoutine3
Exit Sub
ErrHandlrCoding1:
MsgBox prompt:="You will see this, 3 times"
Resume Next
End Sub
Sub ErroringRoutine1()
On Error GoTo ErrHandlrCodingInThisRoutine '
On Error GoTo 0
Dim Cnt As Long
Let Cnt = 1 / 0
MsgBox prompt:="You won't ever see this. Note the Resume Next in ErrHandlrCoding1: will not bring you here either. (It takes you to the line after the second Call"
Exit Sub
ErrHandlrCodingInThisRoutine:
MsgBox prompt:="You won't ever see this"
End Sub
Sub ErroringRoutine2()
On Error Resume Next '
On Error GoTo 0
Dim Cnt As Long
Let Cnt = 1 / 0
MsgBox prompt:="You won't ever see this"
End Sub
Sub ErroringRoutine3()
On Error Resume Next '
Dim Cnt As Long
Let Cnt = 1 / 0
On Error GoTo 0
Let Cnt = 1 / 0
MsgBox prompt:="You won't ever see this"
End Sub
But I was not expecting it. One reason for me not expecting it is that I have read and said myself that On Error GoTo 0 gives standard VBA error handling. Whilst that may technically not be incorrect, it is maybe not so useful to say, at least to someone wanting to learn: In those last 3 routines On Error GoTo 0 didn’t do that, not even in the current running procedure
I welcome any comments, better explanations, links to good documentation I may have missed, or if anyone can think of anything similar related to On Error GoTo 0 I may have overlooked.
Alan https://www.mrexcel.com/board/threads/o ... st-4357024