On Error GoTo 0 - Was we ( or me ) wrong (or not quite right) again?

[DocAElstein]

Hallo, :)
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

So far so good

_ 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

I am not complaining about this feature. Actually when I tripped over it , it was helpful at the time.
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

[HansV]

Microsoft's documentation On Error statement mentions that the effect of On Error GoTo 0 is limited to the current procedure:

On Error GoTo 0    Disables any enabled error handler in the current procedure.

(Italics are mine)

[DocAElstein]

Yes, I have seen that. Both in the official documentation and some other places. It is probably not incorrect. I think it’s one of those things that when you know all the answers, then if something is correct, then it sounds like a good thing to say, but might not always be, (I mean Microsoft saying that may not be so useful, in that documentation. - I think it is good that you mention that documentation here)

When I was little, someone told me turning the light switch off stopped electricity flowing in the ceiling light. It was correct. I got the (first) really bad shock of my life when I turned the switch off and tried to connect a new ceiling light. (It was not so much the electricity shock, - that felt like some strange combination of holding very tightly to a very powerful Jack Hammer whilst the dentist tries to drill out a painful nerve from your hand to your brain. But falling off the ladder in that very uncontrolled way was something people only usually experience shortly before the death the injury causes)

I suppose what I am saying is that the documentation is a bit incomplete, and a lot of pain might be avoided by simply changing it just a bit to like, turning the light switch off stops electricity flowing in the ceiling light, and it might stop electricity being there.
, or , On Error GoTo 0 disables any user enabled error handler in the current procedure, and might revert to default error handling.

To be fair, the confusion I had was only partly due to the (IMVHO) unnecessarily incomplete Microsoft documentation. The other reason is that I have seen literature saying that On Error GoTo 0 enables the default VBA error handling, and that is probably wrong, at least sometimes.
On the other hand, saying On Error GoTo 0 disables any enabled error handler in the current procedure, is probably correct…..

I think I know the answer now, but I am not quite sure yet the best way to explain it. Thinking about it and discussing it here can sometime help get the best solution, or the best way to explain it.

[SpeakEasy]

The Microsoft documentation also explicitly states that if the current procedure cannot handle an error for some reason (no error handler, or error handler already activated, or On Error Goto 0 has disabled the error handling in the procedure) then "If the calling procedure has an enabled error handler, it is activated to handle the error. If the calling procedure's error handler is also active, control passes back through previous calling procedures until an enabled, but inactive, error handler is found. If no inactive, enabled error handler is found, the error is fatal at the point at which it actually occurred."

Essentially we walk back up the calling tree until either the error is handled by an explicitly code error handler, or we reach a point in the hierarchy where no such handler has been coded, and the system reverts to the native, built-in error handler and normal VBA error handling is resumed at that point

[DocAElstein]

.. documentation also explicitly states that if the current procedure cannot handle an error for some reason (no error handler, or error handler already activated, or On Error Goto 0 has disabled the error handling in the procedure) then "If the calling procedure has an enabled error handler, it is activated to handle the error....

Thanks, enlightening, I was missing that. It seems to be explaining what I have experienced…. Another hour plus of looking and I still can’t find it. Can you point me to it, please? - I guess I need to search for something not specifically On Error Goto 0, but I can’t find the right search words to narrow down the internet jungle to a sane amount. I am usually swamped by blogs, that only tell a bit of the story and in some cases are partially incorrect. (I am yet again amazed that so many people don’t can’t or wont get this large but simple subject area right and complete once and for all. I think I may be getting close.

.. Essentially we walk back up the calling tree until either the error is handled by an explicitly code error handler, or …

Interesting, and a bit strange, at least to me for now. I wonder why we don’t have some similar back up the hierarchical thingy for a simple single procedure. – In my first code example in my opening post, Sub OnErrorGoToNullSimpleExplanation1() , I had an initial error handler that I did not explicitly disable with On Error GoTo 0 . But I then enabled a second one. Then I explicitly disabled the second one with On Error GoTo 0 .
At least I assume that is what I did. Maybe what that does is disable all enabled user error handlers?
In any case, we then find that error handling reverts to the fatal native default stuff, - it don’t wonk back up any hierarchical then. Maybe it tries but there ‘aint a hierarchy anymore, as the On Error GoTo 0 tanks them all with one shot – as the old saying goes , kill all the error handlers with a single On Error GoTo 0 shot
My explanation is/ was, so far, that a further error handler in the same procedure replaces/ removes any previous one. ( I have not yet seen official documentation to confirm that, or maybe I did once and forgot. But so far that has been the case and I have seen no Blog contradict that, even if half of them don’t mention that permutation ).

[HansV]

See the article I linked to, under Remarks.

[DocAElstein]

That says mostly the second bit of what SpeakEasy said, but not the first bit I quoted, The first bit I quoted would explain the situation I have,
or maybe I need to read it all again carefully.. maybe it all infers it, but it hasn’t inferred that to me… yet.


Edit late, later: It was worth yet another read though, ( about read number 84 I am at the moment, Lol): It told me another permutation that I had missed. …. If an error occurs while an error handler is active …… the current procedure's error handler can't handle the error. …… I knew that bit – (that is all the raised exception codswallop ) – but I had missed what it then goes on to say - … Control returns to the calling procedure.
I can confirm that seems correct:

Code: Select all

Sub YouGotARaisedException_NoPoroblemICanHandle()  '     https://eileenslounge.com/viewtopic.php?p=315227#p315227
On Error GoTo ThisCallingProceduresErrorHandler

Call SubThatAcivatesItsErrorHandler

Exit Sub
ThisCallingProceduresErrorHandler:
MsgBox prompt:="I can handle it"
End Sub

Sub SubThatAcivatesItsErrorHandler()
On Error GoTo BedLateFridayNight

Dim Cnt As Long
 Let Cnt = 1 / 0 ' Oh no, I gone and raised an exception, now I am in trouble, ( as the actress said to the bishop ) ,  .....  or so I thought

BedLateFridayNight:
 Let Cnt = 1 / 0 ' I was thinking this would be fatal, but it 'aint.....  "If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. Control returns to the calling procedure."   -   https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement
End Sub

Whilst this is interesting, it only comes a bit closer to explain my original situation. It does say that if my error handler in a called procedure cannot handle the error, then the calling procedure takes over, but it is saying it for the case of my error handler in a called procedure being activated. It is not saying that the same thing happens when the error handler in a called procedure cannot handle the error because I disabled it with On Error GoTo 0 …. I suppose it might be slightly inferred, almost,

[StuartR]

Pedant alert. You infer, the document implies.

[DocAElstein]

Yes, indeed, by using the word infer, I was infering that the documentation implies, ( I assume you infer documentation by document , your post implies that.. , although not for the first time, I am not so sure what I am talking about, :) )

[DocAElstein]

Having read the (early parts of the), Remarks at that documentation in both the English and German quite a few times now, and again after sleeping on it, I think perhaps that if some time in the future I continue to observe the behaviour/ feature I depicted in the second bit of demo coding starting with Sub OnErrorGoBackToLastErrHamdlr1() , then if I read that documentation quickly, perhaps not having seen it before or remembering too well that I had seen it before, then I expect I could easily get the impression that the early Remarks at that documentation which is talking about Calling routine behaviour could give the impression that it is implied, as I may have inferred or similar.
Perhaps for now, to move on, I can be happy with something like this, just to jog my memory about this feature …

….. Whilst the documentation does not specifically mention the feature, reading the early Remarks referring to how things work when Calling procedures are involved, it could be seen to be implied, ( or you could easily get impressionied … :) )

(Although of course it would not be unusual for Microsoft to change the documentation in the future for better or worse, and it’s anyone’s guess what they might say. Untypically for the otherwise very useful archive sites, they are often not so useful for getting old Microsoft stuff, for some strange reason? - Perhaps best to save it for future reference - here )

Thanks for the replies so far, they are all helping me to get somewhere on this, or happy enough to accept that this new ( to me ) feature I noticed is probably what happens, or should be expected to happen, and as such another permutation of VBA error handling useful to be aware of.

Alan

[SpeakEasy]

I guess the clarity you seek may have been helped if they changed

If an error occurs while an error handler is active

to

If an error occurs while an error handler is active or is not enabled

rather than the latter few words being implied.

[DocAElstein]

Not so much got implied to me. (Lack of previous knowledge and experience on my behalf, in that subject area, perhaps). Some of what was written at the link was useful and interesting, but going only a bit in the direction of suggesting what I was looking for.

What still sounds best to me is something along the lines of what you originally wrote….
….. if the current procedure cannot handle an error for some reason (no error handler, or error handler already activated, or On Error Goto 0 has disabled the error handling in the procedure) then…. ……….
I am going with that. I will take that as correct , (whatever Microsoft say whenever)
As you already knew the answer, then as the documentation was saying something in that direction, you may have thought it was saying that at a glance. (Maybe you got impressionied, as Microsoft wanted, Lol!)
I think Microsoft documentation can be useful if you already know the answer and know where to find it. It can help jog the memory, or check the odd specific detail or syntax, - I often found it useful for doing just that myself: For learning it is less useful IMVHO. Possibly you and Hans figured at a glance it was saying something along the lines of what you wrote, maybe assuming it was saying that, as it probably should have been. It wasn’t and it isn’t. Or another possibility is that it may have been there and got lost in some Layout change or update, or migration that never gets/ got finished.
( I have seen some very useful older Microsoft stuff, but it could only be got by some complicated link hacks, some only legal here in Europe. It might be that Microsoft have some arrangement with, or other way to manipulate, archive places, search engines etc. and most things in the USA )

I am not sure why they call the current links … learn Microsoft com , - maybe they are trying to deliberately annoy or mislead , or its part of some secret plan to mess up VBA
Perhaps it’s just me still finding it a bit difficult to catch up and adapt: I went off to be something of a Technology hermit for about 20 years and it seems while I was away it went out of fashion to do decent detailed documentation that is right. We seem to be left to guess, infer, get the impressionied or whatever. Or maybe I am just going through a phase like Einstein did of wanting to get everything clearly written down so he could forget it all and go and do and think about something more interesting and come up with something absurd and great what every one thought was impossible .
Perhaps there is just too much in something like VBA to do good concise documentation, but I would have thought the internet and ease of searching should help that. Perhaps there is too much rubbish to sift through on the internet, so it is getting impossible to search to to find the good stuff, (and of course, Google and Co. will organise the search results by demand on payment, to whatever the paying customer wants).
I quite like the forum structure as a nice data base for organising useful and interesting stuff, and learning. Just a personal preference. It’s helped me get this one close to in the bag now.

My problem was less so, or only partly, that of what On Error Goto 0 was about, ( although slightly better documentation could have helped a bit there). I was missing that info about what goes on when error handling and Calling procedures with additional error handles are around. That Calling procedures stuff is partly there at that link, but as we have found, its currently missing bits.
What you wrote which included the On Error Goto 0 in it cleared that up nicely, and I think the documentation would have been better to say that, as you may have assumed it did, or as it may have implied to you, but didn’t to me. Or maybe the documentation did say it once, maybe you read it then. Microsoft documentation gets better or worse, perhaps on average stay the same, I don’t know for sure. They spend a lot of time changing things. I know that. (Someone told me they get grants to do stuff, and changing is a convenient way to use that money, parsing it off as development costs. New Graduates learn and develop at our cost, and mess things up in the process, which possibly fits in with their greater plan of things, the Graduates and Microsoft).


Perhaps with useful Microsoft stuff, keeping a useful link is not enough anymore, you need perhaps to do your own capture somehow. I expect there is some way to do a HTML type copy but one that somehow also catches and stores in original form anything further linked at that page. The web archive places sometimes work great at doing just that. Sometimes they don’t, such as with Microsoft stuff. It’s very varied. (One of the least bad Error handling WTF blogs was at a quite useful site where the owner did his best to hide all the evidence, but the archives have got him well sewn up, thankfully. )

[SpeakEasy]

>some very useful older Microsoft stuff

Yes, I think it is fair to say that back in the day when MSDN was a subscription service delivered on vast numbers of CDs, the documentation was often more comprehensive. We seemed to lose some details as it was migrated across to the Internet.

(One particular example Was the vbscript specific (and thus VBA) version of the regular expressions documentation. At one point this contained info that outlined how you could subclass the regexp library to provide your own custom pattern matching and substitution handling. That info is missing from the current documentation - indeed these days the vbscript specific documentation for this has been completely removed; you can still find it if you know it existed and look in the right places, such as the Wayback Machine)

[SpeakEasy]

Oh, and wait until you discover the almost undocumented (for VBA)

On Error Goto -1

[DocAElstein]

I thought I had been through the loop enough times with On Error Goto -1 to understand it fully, (the loop of thinking I know all about it and then noticing I missed something). But then I thought that about On Error Goto 0 didn’t I , Lol.
So now I am not so sure.
I had best do another loop or two on On Error Goto -1 tomorrow , in light of what I learned here, just to be on the safe side.

( We tangled with On Error Goto -1 a few times here, - this one here was exactly a year ago today! - https://eileenslounge.com/viewtopic.php ... f9#p305650 - It must be that time of the year, Lol.
In the light of our discussions/ revelations here, I see my explanations were not quite right there. But there were no Calling things anywhere there, so not much harm done. )

[SpeakEasy]

>exactly a year ago today

Well, I'd forgotten that!!