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

[DocAElstein]

Link to get to Page 2 ( using 2 above right, or square with page number generally, sometimes does not work due to number at end of title ) :
http://www.excelfox.com/forum/showthread.php/2239-Resume-On-Error-GoTo-0-1-GoTo-Error-Handling-Statements-Runtime-VBA-Error-Handling-ORNeRe-GoRoT-N0Nula-1?p=10559#post10559

_.__________________________

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=19877#post19877 Page 3
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*/page3 Page 3


https://www.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=19891&viewfull=1 page 4
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*/page4 Page 4

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=19909&viewfull=1#post19909 Page 5
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*/page5 Page 5

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=19906&viewfull=1#post19906 Page 6

__________________________________________________ ______________________________________________

This is post 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=10549&viewfull=1#post10549






Resume On Error GoTo 0 -1 GoTo Error Handling Statements, Error and VBA Error Handling in Runtime
Erections/ arousing of Exceptional states by Error, 2018

ORNeRe GoRoT N0Nula 1
The Glorious State of Exception in Transalvania
Hi
I wrote for myself and shared some notes on this a couple of years ago.
https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo
( and here these current new notes in word docm format with codes also: Errors and Error Handling in VBA 2018 (https://app.box.com/s/0qaizoj4lsto8wmhwnml90anlcxv1ki1) )
I think they are fairly complete, but never the less , I usually end up trying test code examples to remind me of what is going on.
It seems like an awkward subject, or is organised in a seemingly odd way, and unfortunately always seems to need a bit of thought or revision
Some people asked me to share some simple examples_..

_.. Here we go then, these will be a bit more practical and less theoretical than the notes, but a read of those notes as well probably does no harm. I will try a slightly different approach, just by way of a change. In the end though, I think I could end up just as long: I think this subject is not so difficult to master, its actually quite easy, bit almost nobody gets it completely correct. Possibly this is just because there are some strange syntaxes and the amount to learn is just that little too long for anyone to want read for a subject which on the face of it should be short, since for most real Object Oriented Programming, the try-catch structure would be the standard for just about everything, but instead VBA is an unholy alliance of MS Basic and sort-of-objects – ( Jay Freedman : https://eileenslounge.com/viewtopic.php?p=305700#p305700 )



https://i.postimg.cc/L5tmj8Jc/VBA-Default-Error-Handler.jpg https://i.postimg.cc/L5KHq18k/VBA-Default-Error-Handler-Div-by-0.jpg



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

“Pseudo” Default VBA Error handling
The Glorious State of Transylvania
The first, possibly most fundamental Error handler,
On Error GoTo LabelOrLineNumber

Codes are in the next two posts, and in this post is some background theory and concepts needed.

“Pseudo” Default VBA Error handling (Taping / hooking on to lower level stuff)
Using code to mimic the default handler can possibly help in learning how to use VBA Error Handling.
A couple of tools we need to get started on this are:
_ some basic understanding of one of the Error Handling statements, On Error GoTo LabelOrLineNumber ;
_ and an available VBA object associated with the exception situation, Err
_ Sub Class a Windows Visual Basic Wonk aka mess with the Exception Software
I expect at the heart of error handling is the first of the main two error handling statements:
On Error GoTo LabelOrLineNumber
Windows and Visual Basic , like Excel and VBA are related things.
The default VBA Error Handling, like most pre determined things in Excel, can be mimicked by us as we have access to a lot of the stuff/software that it uses:
We might be loosely called “application ( Excel ) programmers”.
We, as application programmers, can access directly or indirectly a lot of the software used to create the application in the first place . ( A running or “open” Excel is often referred to as an application, or the Excel application. I suppose it is talking about the software being applied ).
We might say we are working at a higher level in the computing way of things, but in actual fact we can often get at more deeper stuff and tap off, hook into it, or hang bits of our coding into the chain of events that comprises more lower level programming.
Using code to mimic the default handler
What does On Error GoTo LabelOrLineNumber Statement organise and do ? :
As far as us end users are concerned, or as they experience it, this statement does as it suggests: it appears that once an exception is raised, the code continues. But it appears to do so in first being “sent” to the place of the LabelOrLineNumber, just as it would in any GoTo LabelOrLineNumber.
That is how it appears. But it is more subtle.
It does not simply “tell” VBA always to Go To the place indicated at every thing that causes an error:
The code is allowed to continue further from that place indicated, which is where it is “sent to” the first time that an error occurs. ****But a very important point to bear in mind when trying to understand VBA Error Handling in Runtime is that the exception in this situation is still raised. Effectively we have hooked onto, or embedded our code now into the Exception Software which has started due to the error occurring.
I think the way that it actually works is as follows: The code running, or execution is not really sent at any point to the place indicated.
Initially, Passing of the On Error GoTo LabelOrLineNumber code line has the effect of doing the following: It makes a copy of the code and hangs that on the predetermined chain of events that occur when an error occurs. The start of the copy of the code is not the original routine start, but the LabelOrLineNumber point.
A fun way of thinking about it could be to say we are transported to another World, say in The Glorious State of Exception in Transylvania. A copy of our coding is taken with us, and we continue running the coding from the place we were sent to.
In the Glorious State of Exception.
It is reasonable to expect that a code running in general in the exception state , (or being run now pseudo as part of the Exception software), is likely not to function completely as the same coding in the “normal” state would. In fact it appears that most things do actually work normally.
But one thing for sure doesn’t, and that is, possibly as might be reasonably expected, the following:
In the raised exception state, any ( further ) uses of the statements On Error GoTo LabelOrLineNumber or On Error Resume Next , ( be they other code lines or the original On Error GoTo LabelOrLineNumber such as in a looping code situation ) will be ignored. This is because the code is now part of what is sometimes referred to as a call back procedure. In simple terms the code can be thought of now as part of the Exception software code. The first use of the On Error GoTo LabelOrLineNumber effectively linked / transferred the code there, or started a copy of the code from that place, so further attempts to do that would be superfluous and so such code lines are ignored. In simple terms , once we are in the exception state then codes lines like On Error GoTo LabelOrLineNumber or On Error Resume Next are just past as if they were not there. (Not all the error “things”/ Statements wont work, that is to say do what they are supposed to in the Exception State, some will, but more to that later, …. ).
The two main error handling statements give a complicated set of instructions, and cause all sorts of internal things to go on, as do the other two statements contain a GoTo . The significance of the GoTo is god knows fucking what. I expect it was just done for fun, to confuse, but maybe I can come up later with some fun ways that make some abstract sense





_ Err Object.( Function..Ref.)
Err is a function that returns an ErrObject
This is a VBA object associated with the exception situation. It has a couple of methods ( .Clear and .Raise ) and a few Properties.
It does not usually directly control an error situation, or at least does not have that as its main purpose. ( It is sometimes used to see If an error occurred )
Err.Clear _ does not clear the exception. It simply empties some string variable properties containing information about the last error which occurred. In fun terms, think of it as not taking us back to the real world or place we were sent from.
It is mainly used to hold information about the last error. But it does have a Method which pseudo can be used to fool VBA into thinking that an error occurred, ( .Raise)

The Properties have mainly the purpose of storing information about the error that occurred. The Exception software usually passes information related to the error when the error occurs. ( When using .Raise you can additionally via the .Raise( , , , , , ) arguments effectively mess about with the coding which passes this information so as to fill the object with any relevant or irrelevant profanities of your choice)
I use the Err object in my next codes to get the same information that the Default VBA Error handling chucks up at me: We can use Properties of the Err object to get information about the error. I don’t use the .Raise here. ( I will look at that later – It appears to be a waste of time, and no one can remember quite much about it )
Very likely the Exception software uses this Err object in exactly the same way as I will to get its information, although it is a bit of a “Chicken and Egg” question / situation: The Exception software fills initially this object with text and number information about the error as it occurs and then uses this information itself in the message box pop up that comes up by default. At least that is my theory. No one seems to know for sure.
We can reference these Properties ourselves and retrieve them, like, for example, in a code line to get a simple description of the error that occurred , like this:
Dim TheErrorDescription As String
_ Let TheErrorDescription = Err.Description

Although the Exception software can fill the object with information, it appears that the information is , to at least a first approximation, held in a similar way that the properties of any typical VBA object such as a range might be. As such we can ourselves, with simple VBA code, assign them.
So as example, for the last example we could do this:
_ Let Err.Description = “Any Text I like”
The value that the exception software had given would then be replaced / overwritten.
I can do that at any time, but it would be overwritten if I did it before an error occurred.

( The same assigning could be achieved when using the .Raise to ““make” an imaginary error”, for example like:
Err.Raise(Number:=42 , Source:= , Description:= “Any Text I like” , HelpFile:= , HelpContext:= ) _ )




Codes to mimic VBA Default Error handling.
See next two posts



Ref
https://www.eileenslounge.com/viewtopic.php?f=30&t=31896&p=247160#p247149

[DocAElstein]

"Pseudo" Default VBA Error handling

Codes to mimic VBA Default Error handling.
The code below, using user define VBA error handling, is an approximation to part of the VBA Default Error handling. ( The line numbers are just added to help the further explanations: they are not needed for these codes. )
Run the code.
We can, to a first approximation say that the _ OK _ Button which appears in the pop up message box is doing similar to the _ Beenden/Stop _ Button from Default VBA Error handling.

' -1 ' No Exception state before the code is run ( Ending a code clears any exception )
Sub PseudoVBADefaultErrorhandlingPartial()
0
1 On Error GoTo ErrHndler ' '_- Hang hook on chain of events that signify to go to call back procedure when chain is waggled by error erection
Dim Db As Double
' Coding
Let Db = 1 / 0 ' '_- When the error occurs, the Exception software has the code from ErrHndler included in it
' Other Coding. In the case of an error this will never be done
GoTo AfterErrHndler
ErrHndler: ' =========================== ' Call back procedure. Code to be hooked on to Exception software. User defined pseudo VBA Error handling
MsgBox prompt:="Laufzeitfehler '" & Err.Number & "':" & vbCr & vbLf & Err.Description & " ", Title:="Microsoft Visual Basic": Debug.Print "Laufzeitfehler '" & Err.Number & "':" & vbCrLf & Err.Description & " " ' From VB Editor , hit Ctrl+g to display the Immediate Window --- Laufzeitfehler '11': Division durch Null Runtime Error '11': division with zero
'
Exit Sub ' This will clear the exception state. No exception state once code is Ended
AfterErrHndler: '=======================
'You never get here
' Other Coding ' In the case of an error this will never be done
End Sub ' This will clear the exception state. No exception state once code is Ended' -1
Sub PseudoVBADefaultErrorhandlingPartialSimplified()
On Error GoTo ErrHndler '
Dim Db As Double
' Coding
Let Db = 1 / 0 ' When the error occurs, the Exception software has the code from ErrHndler included in it
' Other Coding. In the case of an error this will never be done
Exit Sub
ErrHndler: ' Error handling code section
MsgBox prompt:="Laufzeitfehler '" & Err.Number & "':" & vbCr & vbLf & Err.Description & ""
End Sub
Error handling code section
Just to clarify what I am doing here: I am using error handling techniques to make an error handler similar to the default VBA error handler. This is just by way of a learning exercise to get familiar with VBA error situations in general.
The code section as indicated within ==== is typically referred to as the Error handling code section. In this case the code carried out as part of the Exception Coding is within that section. But it need not be. That referral can be meant to include code lines such as GoTo LabelOrLineNumber or also the execution of any of the three resumes, which effectively bring the normal code back into action. So the term error handling code section is not clearly defined.
To a first approximation the error handling code section is the code section as indicated within ====

The error handling code section need not be , but often is, at the end of the main code. In either case it is necessary to organise that in normal code progressing when no error occurs, that error handling code section will be bypassed.
If, in the last code the error handling code section were at the end, then the label AfterErrHndler: could be omitted, and the Goto AfterErrHndler replaced with Exit Sub

Here the same code again in a more typically seen simplified form

' -1
Sub PseudoVBADefaultErrorhandlingPartialSimplified() ' 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=10551&viewfull=1#post10551
On Error GoTo ErrHndler '
Dim Db As Double
' Coding
Let Db = 1 / 0 ' When the error occurs, the Exception software has the code from ErrHndler included in it
' Other Coding. In the case of an error this will never be done
Exit Sub
ErrHndler: ' Error handling code section
MsgBox prompt:="Laufzeitfehler '" & Err.Number & "':" & vbCr & vbLf & Err.Description & ""
End Sub

[DocAElstein]

"Pseudo" Default VBA Error handling

Codes to mimic VBA Default Error handling.

The next code comes a bit closer to replicating the default VBA Error handling, but note that it is still an approximation
If you run the code and select _ No _ then this will be similar to selecting _ Beenden/Stop _ as before
If you select _ Yes _ , this will be something similar to selecting _ Debug _ in the standard default VBA error handler: The code will return to and pause just before the line that errored.
It might be easier to demo this in normal run mode, ( Click in code, and then select F5 or select play button )
In a typical situation, the selecting to _ Debug _ will allow you manually to do something before continuing, such as dragging the yellow arrow in the left margin away from the line causing an error.
DragDownPastErroringLine.JPG : https://imgur.com/pK01TYA
1999

The code will then appear then to procede normally , but note that it the code is still running in an aroused state of erected exception###


' Run this code in F5 initially to see the possibility of selecting Debug/Yes on pop up ' 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=10552&viewfull=1#post10552
' -1 ' No Exception state before the code is run
Sub PseudoVBADefaultErrorhandling()
0
1 On Error GoTo ErrHndler ' hang hook on chain of events that signify to go to call back procedure when chain is waggled by error erection
Dim Db As Double
' Coding
GoTo Eror ' up to here is "normal" code unless you drag cursor yellow in left margin back up after selecting Yes
JustBeforeError: ' == Pseudo Part of error handling ==
Stop ' ============== Pseodo Part of error handling ==
Eror: ' After stop, drag yellow arrow in margin down to pass error, or drag up to 0 and run
' If you drag down past error then code will go to normal end (but is running in exception state) DragDownPastErroringLine.JPG : https://imgur.com/pK01TYA
' Draging up to 0 will cause error to be handled by VBA default error handler as after error we are always in exception state DragUpTo 0.JPG : https://imgur.com/bdQy0xb
Let Db = 1 / 0 ' When the error occurs, the Exception software has the code from ErrHndler included in it
' Other Coding. In the case of an error this will not be done
GoTo AfterErrHndler
ErrHndler: ' ============================= ' Call back procedure. Code to be hooked on to Exception software == User defined pseudo VBA Error handling
Dim Answer As Long
Let Answer = MsgBox(prompt:="Laufzeitfehler '" & Err.Number & "':" & vbCr & vbLf & Err.Description & " ", Title:="Microsoft Visual Basic", Buttons:=vbYesNo)
If Answer = vbNo Then Exit Sub ' This will clear the exception state. No exception state once code is Ended
If Answer = vbYes Then ' This is equivalent to Hitting Debug in the default VBA error handler
GoTo JustBeforeError ' == Approx. what the default VBA error handler would do on selecting debug
Else
End If
AfterErrHndler: '======================================
' You may get here if you change the code, or skip the erroring line by draging the left margin yellow cursor down past the error line
' Other Coding '
' Nornal code end
End Sub '

One last experiment may be worth doing on the last code above after selecting _ Yes _ : Drag the yellow arrow back up to line 1 or 0 , so that the error handling statement On Error GoTo ErrHndler is done again.
DragUpTo 0.JPG : https://imgur.com/bdQy0xb
2000

Now continue the code in F5 or F8. You will notice that the actual default VBA Error handling is done at the error occurrence and not our user defined pseudo VBA Error handling, which in this code is organised in advance/ indicated by the code line On Error GoTo ErrHndler.
So despite that you effectively once again passed On Error GoTo ErrHndler, our user defined pseudo VBA Error handling is no longer done after it has once been used in a running code.
###This is because the exception is still raised, so such statements are ignored****.


In a round about sort of a way I think the above has got us through understanding On Error GoTo LabelOrLineNumber and a very important point is to remember that this error handling statement does not clear the exception.
One last code is useful to re emphasise this last point.
The codes in the next post demonstrates the classic pit fall which often what leads to many of us learning about Runtime Error and Runtime Error Handling VBA for the first time …. On Error GoTo LabelOrLineNumber only works once


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_- (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_-)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[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

[DocAElstein]

Resume Stuff in VBA error handling
( On Error Resume Next )

Resume and Resume Next and Resume LabelOrLineNumber
It turns out that the 3 resume statements are similar, but that the On Error Resume Next is, while having some similarities, not a simple intuitive derivative of the more fundamental resumes.

Often the word resume will generally mean a restart or somehow continuing.. In VBA, Generally, anything using the word Resume has the effect of putting VBA into a state as it had been previously. An important part of anything being done with anything using Resume will be therefore to stop the exceptional state. Often this is referred to as clearing the exception. That is the important difference between the two main error handling statements: For On Error GoTo LabelOrLineNumber we still have an exceptionally aroused erection state ****
Once again as it is important and is not intuitive: In VBA anything using the word Resume will usually clear the exception.
On Error GoTo LabelOrLineNumber does not clear the exception.

I don't know if the following derived way of looking at the resumes and then finally getting to On Error Resume Next , is anything like how VBA might do it, but it might be, and I felt like a change in the way of explaining.

The goal is to produce something pretty dangerous and very unwise to do. This is often a feature generally of Microsoft Software, and as application programmers we have access to the tools to help us break things.

We want a "blanket" error handler that just tells VBA to carry on at the next line after any error as if nothing had happened.
From the previous discussions we think that an error does automatically kick in the exception software, but we have a chance to pseudo hang a hook on the chain of events to call back / Hold back procedures as this is possible generally in any Windows / Visual basic coding environment…
I will assume that in the case of a On Error Resume Next I might want to know what error had occurred. ( In the first instance, if the exception is cleared, then so is the information in the Err object, so I will need to make some extra coding to get that info available again in Err. This will help us demo more aspects of error handling )
( **(On Error Resume Next )
I might be inclined to call this the second user definable types of error handling statement and put this alongside the one of the Error Handling statements we already comsidered, On Error GoTo LabelOrLineNumber. One reason why I do this is that it does not seem to logically follow what might be expected based on the Resume Next, specifically,
On Error Resume Next does not seem to clear the registers in object Err
, but
Resume Next does not seem to clear the registers in object Err
Strange. This seemingly jumbled up logic may be one reason why the subject is not always understood fully )


I believe the Resume is either the most , or pen ultimately most , fundamental step in what we are trying to achieve. It is interesting that one of the less common error handling statements , On Error GoTo -1 , has little earlier documentation, so I am not sure if it may be a derivative from Resume , which seems to have a more extensive documentation history.

Resume ( and Resume Next and Resume LabelOrLineNumber) will syntax fail ( error themselves in runtime ) if no error has occurred.

It is a good time perhaps to explain briefly the remaining error things, as they may be relevant to the final bad thing to have, On Error Resume Next , which will let us charge through like a blind dumb Bull in a china shop causing errors, breaking things un hindered throughout any code progression.

On Error GoTo -1
This clears the exception. This can be passed by a running code when the code is hunged embedded in the exception software running in the aroused erection, or in the normal un aroused code run. In the latter case , of "normal" code running, it has no effect. That is reasonable. In the exception case, it will effectively make the code continue in normal VBA mode in the current state using current back up exception Buffer data info retrieval.
I do not know if there is any reasoning behind the use of -1. Certainly it confuses most people. It does not confuse me, it makes a strange logic… I often put a fictitious '-1 on the wrong side of Sub at the top. As far as errors are concerned I think of it as meaning that the code is pseudo at its non started state, in which case no exception can have been raised. But that is not a perfect analogy, since we do not restart the coding, but rather just carry on, “back in the real world”, at a point just after where the _ On Error GoTo -1 is. _ This is like the end of the diversion, where the coding between
_ [ where the error occurred
, to
_ where the _ On Error GoTo -1 _ ]
is the diversion.

Because _ On Error GoTo -1 _ effectively only “works” in the exception state, we might consider it an error handling ”thing”, or a tool we can use in error handling, as part of an error handler. Therefore it would be considered a different sort of thing to the On Error Resume Next or Resume LabelOrLineNumber

[DocAElstein]

Error Handling statement Resumes
Resumes resume
Resumes Introduction
The word resume may be used generally to mean one of three similar code lines:
Resume, Resume Next, Resume LabelOrLineNumber

I am looking to mimic these things as a learning exercise and as a prerequisite for also mimicking the On Error Resume Next, (- but important to note here is the comment from the last post, ** , that it does not quite seem to follow the logic we might expect, such that the
Resume Next
,and the
Resume Next in _ On Error Resume Next
, would appear to be slightly different )


As noted, all of these, Resume, Resume Next, Resume LabelOrLineNumber , clear the exceptions, so part of what they effectively “do” is a On Error GoTo -1

The other thing they do is Go To somewhere near the erroring code line, or a label or line number.
Resume goes just before, and Resume Next goes just after an error.
Resume LabelOrLineNumber goes where specified by the label or line number



Resume ( and Resume Next and Resume LabelOrLineNumber ) will syntax fail ( error themselves in runtime ) if no error has occurred. So you must have an error initially, in which case you would be using the resumes in conjunction with an initial On Error GoTo LabelOrLineNumber to take you to an error handling code section where you could use the resume options. At that code section you could determine the current error if required, but you would need to do that before passing a resume statement.. because:

Resume, Resume Next , Resume LabelOrLineNumber and Err object
It appears that the resumes are not intended to keep track of what error occurred as the error object, Err, appears to be cleared of information following a resume.

( Somewhat surprisingly the On Error Resume Next does seem to keep information about the last error )

User error handler to mimic Resume in code example
See next post….. What we want to do is clear exceptions, so effectively “do” On Error GoTo -1
Then, the other thing they do is Go To somewhere near the erroring code line, or specifically where given in LabelOrLineNumber .
Resume should go just before, and Resume Next needs to go just after.
Resume LabelOrLineNumber will go specifically were specified to specifically go to.
We will have to consider the case of the information in the error object, Err, also. –
( ** As noted, after On Error Resume Next we appear to have information about the last error, whereas the more fundamental resume statements do not. .. strange )




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Error Handling statements Resumes
Error Handling statement Resume

Pseodo Resume Code
Below is an attempt a code that does what the actual Resume does
In the code are two lines which error.
At each error line the exception is raised and the code becomes part of the exception software running from GetMilkLuv:

As Resume takes us back to where the error occurred to, as it were, "try again" , then usually some attempt in the error handling would be done to prevent the error occurring again. ( That does not have to be the case: If one was expecting something external to occur which might prevent the code line erroring, then a resume without doing anything would be an option. However this is a very unadvisable use of Resume as it has the potential for causing an infinite looping if nothing prevents the error continuingly occurring. So the fist thing done at the error handler is giving a value to the be used in the denominator other than 0, so that 10 / TNominator no longer gives us an error.
The line of the error then needs to be noted. We use for the first time here a method, Erl(). This is not clearly defined in any documentation. I expect this is some method used internally as needed, from within the Exception State, to return the last executed line in the “real world”/ normal coding before the error. It has therefore become known as a method or function to return the line that errored.
This cannot be done after the next line, On Error GoTo -1 , as On Error GoTo -1 appears, in addition to its main purpose of clearing the exception, to additionally prevent the Erl function from giving us the line number of the last error.
Note that On Error GoTo -1 has also removed the information in the Err object about the last error. Hence code line 55 gives us no information.
Note that On Error GoTo -1 does not do the action of On Error GoTo 0. That is to say, the defined error handler is still "switched on" , or "pluged in and ready to be tripped by an error" as it were. One could say that it is deactivated. But it has not been "unplugged". Possibly you could think of it as the "trap being reset".
The last part of the error handler is to determine where to go back to. It is quite messy and requires the use of line numbers so demonstrates one good reason for having a predefined Resume.

Sub PseudoResumeGoToGet5ButComeBackDarling() ' 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=10556&viewfull=1#post10556
10 On Error GoTo GetMilkLuv
20 Dim TNominator As Long, RslTwat As Long
30 ' Other Code
40 Let TNominator = 0
50 Let RslTwat = 10 / TNominator
55 MsgBox Err.Description ' This gives blank. On Erro GoTo -1 has cleared the Err object of infomation
60 ' other code
70 Let TNominator = 0
80 Let RslTwat = 10 / TNominator
90 ' 0ther code
100 Exit Sub
110 GetMilkLuv: ' "Error handling Code section" is from here until the End
120 Let TNominator = 5 ' get 5 to take back with you
130 Dim errLine As Long: Let errLine = Erl ' this must be done before On Error GoTo -1 , as that clears the recorded error line
140 On Error GoTo -1
141 ' Err.Clear ' I do not need to do this, as it is effectively done as part of On Error GoTo -1 Note: Err.Clear removes the infomation, if an is present, in the Err object. it has no efffect on the actual error state
145 MsgBox prompt:="We want to go back to the erroring line " & errLine & " and try again"
150 If errLine = 10 Then
GoTo 10:
ElseIf errLine = 20 Then
GoTo 20
ElseIf errLine = 30 Then
GoTo 30
ElseIf errLine = 40 Then
GoTo 40
ElseIf errLine = 50 Then
GoTo 50
ElseIf errLine = 60 Then
GoTo 60
ElseIf errLine = 70 Then
GoTo 70
ElseIf errLine = 80 Then
GoTo 80
ElseIf errLine = 90 Then
GoTo 90
ElseIf errLine = 100 Then
GoTo 100
ElseIf errLine = 110 Then
GoTo 110
ElseIf errLine = 120 Then
GoTo 120
ElseIf errLine = 130 Then
GoTo 130
ElseIf errLine = 140 Then
GoTo 140
ElseIf errLine = 150 Then
GoTo 150
End If
End Sub
'

The equivalent code using the VBA Resume statement is shown below:

Sub VBAResume()
On Error GoTo GetMilkLuv
Dim TNominator As Long, RslTwat As Long
' Other Code
Let TNominator = 0
Let RslTwat = 10 / TNominator
MsgBox Err.Description ' This gives blank. On Erro GoTo -1 has cleared the Err object of infomation
' Other code
Let TNominator = 0
Let RslTwat = 10 / TNominator
' 0ther code
Exit Sub
GetMilkLuv: ' "Error handling Code section" is from here until the End
Let TNominator = 5 ' get 5 to take back with you
MsgBox prompt:="We want to go back to the erroring line and try again"
Resume
End Sub
'

[DocAElstein]

Error Handling statement Resumes
Error Handling statement Resume Next
There is very little difference between these codes and the codes from the last post. The line that the error handler goes to is just offset by 1 row. ( I use Select Case instead of ElseIf for no particular reason ) In this case the ability to change something to avoid the error again is less useful as we are not going to “try again”, ( at least not at the point which errored ). But it can be useful, for example at the error handling code section to give some information.
In the example, the information is given about the error type ( the line number is not available in the true Resume next which we are attempting to mimic). And the user is given the opportunity to continue or abort the code.

Pseudo Resume Next Code


Sub PseudoResumeNextGoToGet5ButComeBackDarling() ' 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=10557&viewfull=1#post10557
10 On Error GoTo GetMilkLuv
20 Dim TNominator As Long, RslTwat As Long
30 ' Other Code
40 Let TNominator = 0
50 Let RslTwat = 10 / TNominator
55 MsgBox Err.Description ' This gives blank. On Erro GoTo -1 has cleared the Err object of infomation
60 ' other code
70 Let TNominator = 0
80 Let RslTwat = 10 / TNominator
90 ' 0ther code
100 Exit Sub
110 GetMilkLuv: ' "Error handling Code section" is from here until the End
120 Dim Answer As Long ' You could build this option in if you wanted to
122 Let Answer = MsgBox(prompt:="Your code errored: " & Err.Description & vbCrLf & "Do you want to continue?", Buttons:=vbYesNo)
124 If Answer = vbNo Then Exit Sub 'End code if user does not want to continue after error
130 Dim errLine As Long: Let errLine = Erl ' this must be done before On Error GoTo -1 , as that clears the recorded error line
140 On Error GoTo -1
141 ' Err.Clear ' I do not need to do this, as it is effectively done as part of On Error GoTo -1 Note: Err.Clear removes the infomation, if an is present, in the Err object. it has no efffect on the actual error state
145 MsgBox prompt:="We want to go back to just after the erroring line " & errLine
150 Select Case errLine:
Case 10: GoTo 20
Case 20: GoTo 30
Case 30: GoTo 40
Case 40: GoTo 50
Case 50: GoTo 55
Case 55: GoTo 60
Case 60: GoTo 70
Case 70: GoTo 80
Case 80: GoTo 90
Case 90: GoTo 100
Case 100: GoTo 110
Case 110: GoTo 120
Case 120: GoTo 130
Case 130: GoTo 140
Case 140: GoTo 150
End Select
End Sub



Here the code using the actual VBA Resume Next error handling statement is used to do the same as the previous code .

Sub VBAResumeNext() ' 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=10557&viewfull=1#post10557
On Error GoTo GetMilkLuv
Dim TNominator As Long, RslTwat As Long
' Other Code
Let TNominator = 0
Let RslTwat = 10 / TNominator
MsgBox Err.Description ' This gives blank.
' Other code
Let TNominator = 0
Let RslTwat = 10 / TNominator
' 0ther code
Exit Sub
GetMilkLuv: ' "Error handling Code section" is from here until the End
Dim Answer As Long
Let Answer = MsgBox(prompt:="Your code errored: " & Err.Description & vbCrLf & "Do you want to continue?", Buttons:=vbYesNo)
If Answer = vbNo Then Exit Sub 'End code if user does not want to continue after error
MsgBox prompt:="We want to go back to just after the erroring line, and so ignore the error"
Resume Next
End Sub

[DocAElstein]

Error Handling statement Resumes
Error Handling statement Resume LabelOrLineNumber

This would be used in a similar situation to the last resume type codes, but differing in that when after any error the code should always resume in the same place.
In such code examples, the pseudo coding is easier, since there is no ambiguity on where exactly we go to . It can be seen the error statements of
On Error GoTo -1 : GoTo xxxx
and
Resume xxxx
are exactly the same.

In the code examples below, there are a couple of places where the code can error based on the value of a number variable, TNominator. The purpose of the error handling code section is to adjust that variable value until the whole code is passed.
Therefore in the error handling code section the value “held in” Nominator is adjusted on an error , and then the code restarts from near the start, regardless of where the error occurred. The code will only be completed when a value held in TNominator does not cause an error anywhere in the code.


Sub PseudoResumeLabelOrLineNumberGoToGet5ButComeBackDa rling()
0
1 On Error GoTo GetMilkLuv ' I only need to do this once. VBA has this registered and once the exception is cleared with On Error GoTo -1 , then this user defined error handle will be used again should an error occur
Dim TNominator As Long, RslTwat As Long
2 Let TNominator = 1
3
Let RslTwat = 10 / (TNominator - 1)
MsgBox Err.Description ' This always gives blank, even when an error had occured because On Erro GoTo -1 has clears the Err object of any infomation it might have ever beeen given
Let RslTwat = 10 / (TNominator - 2)
MsgBox prompt:="The code did not error anywhere for TNominator = " & TNominator
Exit Sub
GetMilkLuv: ' "Error handling Code section" is from here until the End
MsgBox prompt:="The number " & TNominator & " causes problems Matey-Boy, (or GirlieOh)"
Let TNominator = TNominator + 1
' Err.Clear ' I do not need to do this, as it is effectively done as part of On Error GoTo -1 Note: Err.Clear removes the infomation, if any is present, in the Err object. it has no efffect on the actual error state
On Error GoTo -1: GoTo 3 ' ' Direct equivalent of Resume 3
End Sub
'

Sub VBAResumeLabelOrLineNumber() ' ' ' 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=10558&viewfull=1#post10558
0
1 On Error GoTo GetMilkLuv ' I only need to do this once. VBA has this registered and once the exception is cleared with On Error GoTo -1 , then this user defined error handle will be used again should an error occur
Dim TNominator As Long, RslTwat As Long
2 Let TNominator = 1
3
Let RslTwat = 10 / (TNominator - 1)
MsgBox Err.Description ' This always gives blank, even when an error had occured because On Error GoTo -1 has clears the Err object of any infomation it might have ever beeen given
Let RslTwat = 10 / (TNominator - 2)
MsgBox prompt:="The code did not error anywhere for TNominator = " & TNominator
Exit Sub
GetMilkLuv: ' "Error handling Code section" is from here until the End
MsgBox prompt:="The number " & TNominator & " causes problems Matey-Boy, (or GirlieOh)"
Let TNominator = TNominator + 1
Resume 3 ' Direct equivalent of On Error GoTo -1: GoTo 3
End Sub








Side Issue
On Error GoTo 0 “works” in both the aroused state and the “normal” code running state
It is convenient using codes similar to the last to address this point.

The next two codes are a slight variation of the last one. After the first error an On Error GoTo 0 is done. This disables the initial error handle, On Error GoTo GetMilkLuv , and so the second error is handled by the VBA default error handler and we do not get a chance to adjust the TNominator so as to prevent the second error. The codes terminate with the default VBA error handler

They demonstrate one point in particular: The On Error GoTo 0 “works” in both the aroused state and the “normal” code running state:
The first code has the On Error GoTo 0 in the error handling code section before the resume so the code is at that point effectively part of the exception software;
The second code has the On Error GoTo 0 in the “main” code which due to the “ “On Error GoTo -1 “ effect “ of the Resume done in the error handler , is in normal code modus ( no exception state of aroused erection).
The effect of the On Error GoTo 0 is the same in both codes: It disables ( removes from VBA’s memory ) the user defined error handler after the first error any VBA defaults back to the default VBA error handler. The codes terminate therefore with the default VBA error handler on the second error in both codes.


' OnErrorGoTo0 In Stiffy : With an erection I remove the user error handler
Sub VBAResumeLabelOrLineNumberOnErrorGoTo0InStiffyModu s()
0
1 On Error GoTo GetMilkLuv '
Dim TNominator As Long, RslTwat As Long
2 Let TNominator = 1
3
Let RslTwat = 10 / (TNominator - 1)
' The above line when erroring was "handled by GetMilkLuv:" The line below is handled by the VBA deafault error handler when it causes an error
Let RslTwat = 10 / (TNominator - 2)
' you never get here !
MsgBox prompt:="The code did not error anywhere for TNominator = " & TNominator
Exit Sub
GetMilkLuv: ' "Error handling Code section" is from here until the End
On Error GoTo 0 ' VBA effectively disables/ removes the On Error GoTo GetMilkLuv instruction from its memory. I do it here while I have an erection
MsgBox prompt:="The number " & TNominator & " causes problems Matey-Boy, (or GirlieOh)"
Let TNominator = TNominator + 1
Resume 3
End Sub
'
' OnErrorGoTo0 Schlappschwanz : "Normal" code run disabling of user defined error handler
Sub VBAResumeLabelOrLineNumberOnErrorGoTo0Schlappschwa nz()
0
1 On Error GoTo GetMilkLuv '
Dim TNominator As Long, RslTwat As Long
2 Let TNominator = 1
3
Let RslTwat = 10 / (TNominator - 1)
' The above line when erroring was "handled by GetMilkLuv:" The second line below is handled by the VBA deafault error handler when it causes an error
On Error GoTo 0 ' VBA effectively disables/ removes the On Error GoTo GetMilkLuv instruction from its memory
Let RslTwat = 10 / (TNominator - 2)
' you never get here !
MsgBox prompt:="The code did not error anywhere for TNominator = " & TNominator
Exit Sub
GetMilkLuv: ' "Error handling Code section" is from here until the End
MsgBox prompt:="The number " & TNominator & " causes problems Matey-Boy, (or GirlieOh)"
Let TNominator = TNominator + 1
Resume 3
End Sub









Link to get to Page 2 ( using 2 above right from post #1, or the 2 below right in the page list, does not work due to number at end of title ) :
http://www.excelfox.com/forum/showthread.php/2239-Resume-On-Error-GoTo-0-1-GoTo-Error-Handling-Statements-Runtime-VBA-Error-Handling-ORNeRe-GoRoT-N0Nula-1?p=10559#post10559

[DocAElstein]

Post #10 on page #1 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=10558&viewfull=1#post10558






Error Handling statement Resumes
On Error Resume Next

The next posts will look at the second main Error handling statement , On Error Resume Next .
Every time I look at this, I come up with a slightly different idea. To some extent I may be going back and forwards, fluctuating a bit, bit on average I am perfecting the understanding a bit better.
If I try, as I have done in the past, to discus the more fundamental general idea of the Resume .
And build on that, to derive user defined error handlers that works in a similar way, things don’t come outs quite as logically as one might expect. .

In my opinion the whole VBA error handling is an unintuitive mess. In the unlikely event that you have read and understood most of my previous ramblings, then a couple logical conclusion might be the following:

_ We might reasonably guess that there is no On Error Resume
This is because that would imply that at an error the code tries again. And again. And again And again. And again.. … Conceivable there might be a situation where that might work if some external influence meant that the code line erroring suddenly didn't. But that is unlikely. More likely is that having such an option would cause the attempt at an infinite loop or retrying.
In fact that assumption is correct. There is no On Error Resume

_ We might reasonably guess that there might be a On Error Resume LabelOrLineNumber
That would be a reasonable thing to do. But there isn’t. Perhaps this should slightly spark our suspicions.

Indeed we find the On Error Resume Next is not quite doing _ On Error ……… then …. Resume Next. Almost it does. It does both
_ carry on after the line causing the error
, and
_ it appears to not be in The State of Exception
But here is the oddity. The error object, Err , is not cleared and so can still be used to tell us what error did occur , (and the Erl() also tells us the last line number that errored )




On Error Resume Next is very commonly used ( badly in the opinion of most professionals) to just keep a coding going despite any errors
This simplest use is shown in the next macro. All errors are ignored.


Sub OnErrorResumeNext1() ' 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=10559&viewfull=1#post10559
0 Dim Ex As Double
10 On Error Resume Next

20 Let Ex = 1 / 0
30 MsgBox prompt:=Err.Description & ", at line number" & Erl(): Debug.Print Err.Description & ", at line number" & Erl()

40 Let Ex = 1 / 0
50 MsgBox prompt:=Err.Description & ", at line number" & Erl(): Debug.Print Err.Description & ", at line number" & Erl()
End Sub
'

The output is like
Division durch Null, at line number20
Division durch Null, at line number40

[DocAElstein]

Codes for On Error Resume Next

Pseudo On Error Resume Next - Considerations

A convenient way to show this would involve the Return Next. A less convenient way would be to use a macro with line numbers , since the Return Next itself was shown using a pseudo code involving line numbers. ( 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=10557&viewfull=1#post10557 Sub PseudoResumeNextGoToGet5ButComeBackDarling() )
The basic thing going on is the same – we want a way to go back and carry on at the error, but as we noted there are some subtle unexpected differences in how
On Error Resume Next , is working
, and how we would expect perhaps something of the form
On Error …. Do effectively at the point of error ….Resume Next
, to work. So it may help avoid confusing the two to just do a single macro, the more fundamental one, using the code lines.

Difference On Error Resume Next and On Error …. Do effectively at the point of error …. Resume Next
This macro for Pseudo On Error Resume Next will be very similar to that for Pseudo Return Next ( 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=10557&viewfull=1#post10557 Sub PseudoResumeNextGoToGet5ButComeBackDarling() ) The main difference, and the very important difference is that we must add some coding to capture the Err and Erl() information, (at least some of the Err information just as way of demonstration). It is the availability of this information after the error that makes the On Error Resume Next not as we might expect from considering it as a On Error …. Do effectively at the point of error ….Resume Next, since a Resume Next clears this information.

For demonstration purposes, we will use the variable, errLine, to store, and then use to represent the Erl() in a true On Error Resume Next. We did this in previous macros.
We could do the same for the property of the Err object that we are using in the demonstration, Err.Description . For example we could do something like

Dim Err_Description As String: Let Err_Description = Err.Description
On Error GoTo -1 ' We want this to take us out of the State of Exception , but for the case of demonstrating how On Error Resume Next works, we don't want the extra feature of clearing the Err object
Let Err.Description = Err_Description '
Thereafter, we would use in our pseudo coding for demonstration repurposes, Err_Description to represent the true property Err.Description
However we have another possibility in the case of the error object, Err . - in the case of the error object, Err , we only need to do that temporarily until just after we do the On Error GoTo -1 , since thereafter we can re assign the Err.Description, since , perhaps strangely, the error object, Err , is not just read only, as is the Erl(), and as logically we might have similarly expected that the Err should be. So alternatively we could do something like this
Dim TempErrDesc As String: Let TempErrDesc = Err.Description
On Error GoTo -1 ' We want this to take us out of the State of Exception , but for the case of demonstrating how On Error Resume Next works, we don't want the extra feature of clearing the Err object
Let Err.Description = TempErrDesc ' in the case of the error object, Err , we only need temporarily store the Err properties until just after we do the On Error GoTo -1 , since thereafter we can re assign, for example, the Err.Description , since , perhaps strangely, the error object, Err , is not just read only, as is the Erl() , and as logically we might have similarly expected that the Err would be
Thereafter we can access properties** just as previously and normally from the actual error object, Err , itself. ( ** For a simplified demonstration I am only considering the .Derscription but you could use the same way , (of temporarily storing then re assigning after the On Error Goto -1 ) , for all the properties. )



Full pseudo code . ( ** For a simplified demonstration I am only considering the .Derscription for the Err, but you could use the same way , (of temporarily storing then re assigning after the On Error Goto -1 ) , for all the properties. )
__ …..see next post ……_

[DocAElstein]

Codes for On Error Resume Next
¬_.... from last post



Full pseudo code . ( ** For a simplified demonstration I am only considering the .Derscription for the Err, but you could use the same way , (of temporarily storing then re assigning after the On Error Goto -1 ) , for all the properties. )

Sub PseudoOnErrorResumeNextGoToGet5ButComeBackDarling( ) ' 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=19875&viewfull=1#post19875
10 On Error GoTo ResNxt
20 Dim TNominator As Long, RslTwat As Long, errline As String
30 ' Other Code
40 Let TNominator = 0
50 Let RslTwat = 10 / TNominator
55 MsgBox Err.Description ' This does not give blank, as it would after a Resume Next
56 MsgBox errline ' This effectively gives the line where the last error occured
60 ' other code
70 Let TNominator = 0
80 Let RslTwat = 10 / TNominator
90 ' 0ther code
100 Exit Sub
110 ResNxt: ' "Error handling Code section" is from here until the End
120 Dim Answer As Long ' You could build this option in if you wanted to
122 Let Answer = MsgBox(prompt:="Your code errored: " & Err.Description & vbCrLf & "Do you want to continue?", Buttons:=vbYesNo)
124 If Answer = vbNo Then Exit Sub 'End code if user does not want to continue after error
130 Let errline = Erl ' this must be done before On Error GoTo -1 , as that clears the recorded error line
132 Dim TempErrDesc As String: Let TempErrDesc = Err.Description ' '_-In the case of the error object, Err , we only need to do that temporarily until just after we do the On Error GoTo -1 , since thereafter we can re assign the Err.Description, since , perhaps strangely, the error object, Err , is not just read only, as is the Erl(), and as logically we might have similarly expected that the Err should be
140 On Error GoTo -1
141 ' Err.Clear ' I do not need to do this, as it is effectively done as part of On Error GoTo -1 Note: Err.Clear removes the infomation, if an is present, in the Err object. it has no effect on the actual error state
143 Let Err.Description = TempErrDesc ' '_-In the case of the error object, Err , we only need to do that temporarily until just after we do the On Error GoTo -1 , since thereafter we can re assign the Err.Description, since , perhaps strangely, the error object, Err , is not just read only, as is the Erl(), and as logically we might have similarly expected that the Err should be
145 MsgBox prompt:="We want to go back to just after the erroring line " & errline
150 Select Case errline:
Case 10: GoTo 20
Case 20: GoTo 30
Case 30: GoTo 40
Case 40: GoTo 50
Case 50: GoTo 55
Case 55: GoTo 60
Case 60: GoTo 70
Case 70: GoTo 80
Case 80: GoTo 90
Case 90: GoTo 100
Case 100: GoTo 110
Case 110: GoTo 120
Case 120: GoTo 130
Case 130: GoTo 140
Case 140: GoTo 150
End Select
End Sub











Just for the sake of comparison, and to emphasise the , perhaps unexpected required differences, I include again the previous pseudo coding for the Resume Next. In the pseudo coding done for the On Error Resume Next, above, I have made orange the most important differences. Those two most important differences are, in words,
_(i) the Error object , Err , does not get cleared in On Error Resume Next as it does in Resume Next
_(ii) related to (i), we can re assign the properties of the Error object , Err ( In either the normal code state or the in the State of Exception, so for the demo coding can use the real Err after, getting properties from it, rather than assigning, and using, variables for the various Err properties )


Sub PseudoResumeNextGoToGet5ButComeBackDarling() ' 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=10557&viewfull=1#post10557
10 On Error GoTo GetMilkLuv
20 Dim TNominator As Long, RslTwat As Long
30 ' Other Code
40 Let TNominator = 0
50 Let RslTwat = 10 / TNominator
55 MsgBox Err.Description ' This gives blank. On Erro GoTo -1 has cleared the Err object of infomation
60 ' other code
70 Let TNominator = 0
80 Let RslTwat = 10 / TNominator
90 ' 0ther code
100 Exit Sub
110 GetMilkLuv: ' "Error handling Code section" is from here until the End
120 Dim Answer As Long ' You could build this option in if you wanted to
122 Let Answer = MsgBox(prompt:="Your code errored: " & Err.Description & vbCrLf & "Do you want to continue?", Buttons:=vbYesNo)
124 If Answer = vbNo Then Exit Sub 'End code if user does not want to continue after error
130 Dim errline As Long: Let errline = Erl ' this must be done before On Error GoTo -1 , as that clears the recorded error line
140 On Error GoTo -1
141 ' Err.Clear ' I do not need to do this, as it is effectively done as part of On Error GoTo -1 Note: Err.Clear removes the infomation, if an is present, in the Err object. it has no efffect on the actual error state
145 MsgBox prompt:="We want to go back to just after the erroring line " & errline
150 Select Case errline:
Case 10: GoTo 20
Case 20: GoTo 30
Case 30: GoTo 40
Case 40: GoTo 50
Case 50: GoTo 55
Case 55: GoTo 60
Case 60: GoTo 70
Case 70: GoTo 80
Case 80: GoTo 90
Case 90: GoTo 100
Case 100: GoTo 110
Case 110: GoTo 120
Case 120: GoTo 130
Case 130: GoTo 140
Case 140: GoTo 150
End Select
End Sub

[DocAElstein]

Some Notes on On Error Resume Next usage
On Error Resume Next is bad
It is like a dumb blind Bull in a china shop, with the exception that some coding at least notes what error was last hidden.
Usually the advice is to only use an On Error Resume Next when you are expecting an error but can’t think of any other way to check for it that does not raise an exception . Further you should then use the On Error GoTo 0 as soon as possible after to remove the error handler ( “turn it off”, “un plug it”, disable it )
Remember we can do this On Error GoTo 0 at any point even after the On Error Resume Next has been used, as the exception is cleared by On Error Resume Next (Actually, On Error GoTo 0 can also be used in the exception state and also disables the error handler but has no noticeable effect if the exception is still raised )
Both On Error GoTo -1 and On Error GoTo 0 clear the Err object
It was perhaps reasonable to expect that On Error GoTo -1 cleared the information from the Err object. It may not be so obvious that On Error GoTo 0 also does this. So if you wanted to use a check on the Err properties after an On Error Resume Next in order to ascertain if and what error had been “hidden” , then you must do that before any On Error GoTo 0 or On Error GoTo -1.

On Error GoTo -1 and Err.Clear clear the Err object of information
As we noted above, initially one might think that On Error GoTo -1 has no useful function when a On Error Resume Next is in place, as effectively any resume type statement effectively does On Error GoTo -1, but for the unique case of On Error Resume Next which maintains the Err properties of the last error, the use of On Error GoTo -1 gives the possibility to clear the properties of the Err object, without disabling the error handler. But note, that the method Clear, that is to say Err.Clear, can also be used to do that.
But you never know, some crazy combination of all the statements might best suit some messy system



On Error Resume Next is bad. In most cases there is a better alternative to using On Error Resume Next .
Often it is a quick workaround. That tends to be how I have used it.
I don’t think I should have used it in such cases.
_a) By definition a work a round is bad.
_ b) Often it is jus ignorance as I don’t know ( yet ) the alternatives

In the next posts are some example of how I am using it. Maybe I will add to them, or give the better alternative not using error handling, if I ever figure it out.
Maybe from time to time I will add other examples of error handling generally, and welcome any comments or additions

[DocAElstein]

hhh

[DocAElstein]

Late Early Binding.
Only very rarely there are advantages in using Early Binding in preference to late Binding in a final shared File. For development the Early Binding is often preferable as this somehow seems to make an initial link or reference such that intellisense knows what is available. This requires however the checking of a library in the _ VB Editor – - - Tools – - - references _ list
The Late Binding alternative uses the CreateObject Method whose (“string”) argument , ignored by compile , is used at run time to “find” the library of the given name.
Well… I had some existing files which had a lot of Early Binding, and for the time being I did not want to change them.
The current problem example had an Early Binding reference to Word, done on a Office 2007 machine.
I got broken reference errors then on 2010 office versions. I also wanted the File to work in Excel 2003

I found by a bit of experimenting and Forum involvement _..
https://www.excelforum.com/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4820111
https://www.excelforum.com/excel-programming-vba-macros/1214789-late-binding-2.html#post4821675
_.. that a Globally Unique Identifier (GUID) appeared a fairly reliable to reference the appropriate libraries. A short code I found could be reliably used to check the reference programmatically via its GUID.
I don’t know yet if there is a good reference list for all GUIDs, but a simple code I could use to get a list of my checked references. For my example I checked the reference to Word on different Office versions and ran this code:





Sub RefItsGUIDsAndStuff()
Dim It As Variant
For Each It In ThisWorkbook.VBProject.References
Dim strIts As String
Let strIts = strIts & "Description:" & vbTab & It.Description & vbCr & "Name:" & vbTab & vbTab & It.Name & vbCr & "Buitin:" & vbTab & vbTab & It.BuiltIn & vbCr & "Minor:" & vbTab & vbTab & It.minor & vbCr & "Major:" & vbTab & vbTab & It.major & vbCr & "FullPath:" & vbTab & vbTab & It.fullpath & vbCr & "GUID:" & vbTab & vbTab & It.GUID & vbCr & "Type:" & vbTab & vbTab & It.Type & vbCr & "Isbroken:" & vbTab & vbTab & It.isbroken & vbCr & vbCr
Next It
Debug.Print strIts ' From VB Editor Ctrl+g to Immediate Window
End Sub
Some results for different Excel Versions



Excel 2007
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Excel 12.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 6
Major: 1
FullPath: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Office 12.0 Object Library
Name: Office
Buitin: Falsch
Minor: 4
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch

Description: Microsoft Forms 2.0 Object Library
Name: MSForms
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\FM20.DLL
GUID: {0D452EE1-E08F-101A-852E-02608C4D0BB4}
Type: 0
Isbroken: Falsch

Description: Microsoft Scripting Runtime
Name: Scripting
Buitin: Falsch
Minor: 0
Major: 1
FullPath: C:\Windows\system32\scrrun.dll
GUID: {420B2830-E718-11CF-893D-00A0C9054228}
Type: 0
Isbroken: Falsch

Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Excel 2003

Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Excel 11.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 5
Major: 1
FullPath: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Office 11.0 Object Library
Name: Office
Buitin: Falsch
Minor: 3
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch

Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch


Excel 2010
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 1
Major: 4
FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Excel 14.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 7
Major: 1
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\SysWOW64\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Office 14.0 Object Library
Name: Office
Buitin: Falsch
Minor: 5
Major: 2
FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch

Description: Microsoft Word 14.0 Object Library
Name: Word
Buitin: Falsch
Minor: 5
Major: 8
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch


I use the following codes to add programmatically the reference. ( The codes are in the ThisWorkbook code module). The reason for the error handler is that I cannot know if the check has already be made where the File might be in use. I think I can only check references that are made. The code would error at the attempt to check a reference already checked.
I could do the following which would be very simple: _ ….

With ThisWorkbook.VBProject.References
On Error Resume Next '
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2003
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2007
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=5 ' Office 2010
On Error GoTo 0
End With
_.. Typically, and a bad habit, is to use On Error Resume Next for convenience as above


With this following code, I have at least narrowed the chances of the code errorong

With ThisWorkbook.VBProject.References
On Error Resume Next '
Select Case CLng(Val(Application.Version))
Case 9: ' Excel 2000
Case 10: ' Excel 2002
Case 11: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2003
Case 12: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2007
Case 14: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=5 ' Office 2010
Case 15: temp = "Excel 2013"
Case 16: temp = "Excel 2016 (Windows)"
Case Else: temp = "Unknown"
End Select
On Error GoTo 0
End With

[DocAElstein]

hchchc

[DocAElstein]

mv yvmvm

[DocAElstein]

cjjcc

[DocAElstein]

Here is a link to the second but last post in Page 2, #19 ,
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=19889&viewfull=1#post19889

Link to get to Page 3 ( using 3 above right from post #11, or the 3 below right in the page list, does not work due to number at end of title ) :
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=19877#post19877 Page 3
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*/page3 Page 3




Err object. Err.Raise. Custom Error handler

One possible last area of VBA error things that can be considered is the possibility to raise an exception without actually having a code line that causes an error to occur, and possibly to modify the responses, or rather the given details of the error from the VBA default error handler pop up message
I am guessing that this means that you can cause the Exception software to start, or start that software running in a similar way to which it would automatically be triggered by an actual error occurring.
It seems that a few Blogs are not quite clear on exactly what this is about. I don’t think anyone quite remembers anymore exactly what it does. Certainly no one knows the things about the arguments that I think I do and probably don’t.
It is probably therefore a good idea to tackle this in two parts. First Part 1), an investigation into what the Err object and in particulate the Method .Raise is, and then Part 2), using it in a “Custom Error handler”

[DocAElstein]

jchkjscsh




























































Link to get to Page 3 ( using 3 above right from post #11, or the 3 below right in the page list, does not work due to number at end of title ) :
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=19877#post19877 Page 3
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*/page3 Page 3

[DocAElstein]

Part 1) Err object. Err.Raise.
The basic command for a custom error handler is the use of the Err object method of .Raise in such a code line:
Err.Raise(Number:= , Source:= , Description:= , HelpFile:= , HelpContext:= , LastDllError:= )
The basic function and usual usage is to start the default VBA Error handler, but the text properties will be mostly left empty in the uses of it usually seen.
I say mostly as syntaxly you must give at least the first, Number:= in the above code line if you use it.

Possibly the Err.Raise Custom Error handler has been introduced by someone for fun as some sort of Trolling. Most of what you can do with it you can do in other more intuitive ways. In addition there are a few quirks that no one quite seems to understand.


So the basic idea is that you can force something similar to happen as to that when an error occurs. Similar…

I think if we take another look generally at the Err object, try to work through what it, and in particular its Method .Raise does, mention a few quirks along the way, … then I think we will see that .Raise is not much more than a complicated way to bring up a Message box.

Back to the start:
Three codes did a demo on what goes on if you try to divide by zero. Here the simplest again:

Sub Error_VBADefaultErrorHandling()
Dim Db As Double
Let Db = 1 / 0 ' Code terminates here and VBA chucks up a meassage box
'You never get here
End Sub
The result with those codes was an error, and the corresponding message was of the form:

Laufzeitfehler '11':
Division durch Null

Runtime Error '11':
division with zero




The following codes are not much use for anything over than the discussions here. They are not much use as the main result of a simple call of the Err.Raise will be to stop the code in the typical VBA default error handling way.
The slight difference is that VBA does not make an attempt to fully fill the Properties of the Err object. This is reasonable as it has no real error to refer to.
So the syntax of the method allows for Property entries, with a couple of quirks:
A number must be give;.
If the number happens to be a number VBA recognises then VBA will add the appropriate other information

Sub RaiseAnyNumber()
Err.Raise Number:=9999
End Sub
ErrRaise9999Help.JPG : https://imgur.com/KSlN6D7 https://i.postimg.cc/sX8dmqY0/Err-Raise9999-Help.jpg
https://i.postimg.cc/fbzNRxW5/Err-Raise9999-Help.jpg
48294830

If we use the number 11 then VBA recognises that as the error when trying to divide by zero, and adds appropriately the description

Sub Raise11()
Err.Raise Number:=11
End Sub
ErrRaise11Help.JPG : https://imgur.com/tL6uvxN https://i.postimg.cc/R0gz6NNm/Err-Raise11-Help.jpg
4831

We can overwrite the attempt from VBA to add the corresponding information to the Err object Properties, although it appears that number is still used by VBA

Sub Raise11B()
Err.Raise Number:=11, Description:="An Error with Number 11, Bollox"
End Sub
ErrRaise11BolloxHelp.JPG : https://imgur.com/tDK7JwF https://i.postimg.cc/t4Hb8KLK/Err-Raise11-Bollox-Help.jpg
4832






_.......continued in next post

[DocAElstein]

_..... continuing from last post

In any practical use of the Err.Raise Method we would likely use it within one of the main two Error Handlers, On Error Resume Next or On Error GoTo LabelOrLineNumber

Indeed to progress further with the experiments here that will be useful to use it in that form.
I prefer to use the On Error GoTo LabelOrLineNumber , as the previous experiments showed the it was somewhat unexpected that when errors occurred with the On Error Resume Next that the information in the Err object properties were maintained. As I don’t know what extra coding is in place to make that happen, I will stick with the On Error GoTo LabelOrLineNumber as I am thinking that this may be more fundamental , which is preferable when delving down in experiments.

The following code is the simplified typical usage of On Error GoTo LabelOrLineNumber to hook from the LabelOrLineNumber position our code Sub RaiseErection() into the Exception software, allowing normal code type progression in the aroused state.
The purpose of the error handling code section is to give full details of the Err object Properties for different .Raise argument.
This will be helpful as an insight into how we might want to use the Err.Raise in a “customised” error handler
With the error handler in place, then as usual, we do not prevent an erecting to the exception state, - but the code is not ended via the default VBA error handler via a pop up with the Error description and Error Number displayed. The code in the exception state continues, that is to say the sub routine is now part of the exception software which continues allowing us to use the error object for those two properties of Error description and Error Number but also the other Properties available.

Sub RaiseErection()
On Error GoTo EmBed
Err.Raise Number:=9999
Exit Sub ' You never come here
EmBed: ' Error handling code section
Dim strMsg As String
Let strMsg = "Number:= " & Err.Number & vbCr & vbLf & "Description:= " & Err.Description & vbCr & vbLf & "Source:= " & vbCrLf & "HelpFile:= """ & Err.HelpFile & """" & vbCrLf & "HelpContext:= " & Err.HelpContext & vbCrLf & "LastDllError " & Err.LastDllError
MsgBox strMsg: Debug.Print strMsg
End Sub

Here the typical results displayed in a message box and also available to copy from the immediate window:

Number:= 9999
Description:= Anwendungs- oder objektdefinierter Fehler
Source:=
HelpFile:= C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm
HelpContext:= 1000095
LastDllError 0

One useful side effect from all this latest foray is the possibility to get at a working example of the Help File Button option typically seen in Pop up boxes. As example I can get at the above help info, ( which is the default when no error is listed for the error number given ) , now using codes such as this:

Sub HilfeIWishIHadSeenThisBefore()
Application.Help HelpFile:="C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm", HelpContextID:=1000096
VBA.InputBox prompt:="Test a HelpFile Button", HelpFile:="C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm", Context:=1000095
'Application.InputBox Prompt:="Test a HelpFile Button", HelpFile:="C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm", HelpContextID:=1000096 ' Help butttons are broken on Application Input box
End Sub
( I would have saved myself a lot of bother if I had seen this before I did this:
https://www.excelforum.com/excel-new-users-basics/1099015-vba-application-inputbox-option-helpfile-helpcontextid.html#post4827566 )

_.....
Using one of the resume statements it is very easy to modify the last error raising code to effectively loop through a number of options: by simply adding Resume Next at the end of the code the last exception is cleared, as is the Err object, and the code continues in normal modus at the next line. So with Resume Next added we can replace the single Err.Raise with a list which will be progressed through. Here an example code,

Sub RaiseErections()
0 On Error GoTo EmBed
1 Err.Raise Number:=9999
2 Err.Raise Number:=11
3 Err.Raise Number:=12
4 Err.Raise Number:=vbObjectError ' vbObjectError.JPG : https://imgur.com/fdh4ymA
5 Err.Raise Number:=-2147221504 ' -2147221504.JPG : https://imgur.com/1kKYjzA
6 Err.Raise Number:=vbObjectError + 1 ' vbObjectError + 1.JPG : https://imgur.com/sc9qm8d
7 Err.Raise Number:=vbObjectError + 500 ' vbObjectError + 500.JPG : https://imgur.com/7DNiUnR
8 Err.Raise Number:=vbObjectError + 11 ' vbObjectError + 11.jpg : https://imgur.com/8rqVpYe
Exit Sub ' You never come here
EmBed: ' Error handling code section
Dim strMsg As String
Let strMsg = "Number:= " & Err.Number & vbCr & vbLf & "Description:= " & Err.Description & vbCr & vbLf & "Source:= " & Err.Source & vbCrLf & "HelpFile:= """ & Err.HelpFile & """" & vbCrLf & "HelpContext:= " & Err.HelpContext & vbCr & vbLf & "LastDllError " & Err.LastDllError
'MsgBox strMsg
Debug.Print strMsg
Debug.Print ' To make a space between each set of infomation
Resume Next ' We clear the exceütioon, clear the Err object, and continue in normal code running mode at the next Err Raise line
End Sub





And here the results as seen in the Immediate window:


Number:= 9999
Description:= Anwendungs- oder objektdefinierter Fehler
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000095
LastDllError 0

Number:= 11
Description:= Division durch Null
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000011
LastDllError 0

Number:= 12
Description:= Anwendungs- oder objektdefinierter Fehler
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000095
LastDllError 0

Number:= -2147221504
Description:= Automatisierungsfehler
Ungültige OLEVERB-Struktur
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000440
LastDllError 0

Number:= -2147221504
Description:= Automatisierungsfehler
Ungültige OLEVERB-Struktur
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000440
LastDllError 0

Number:= -2147221503
Description:= Automatisierungsfehler
Ungültige Advisemarken
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000440
LastDllError 0

Number:= -2147221004
Description:= Automatisierungsfehler
Ungültige Schnittstellenzeichenfolge
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000440
LastDllError 0

Number:= -2147221493
Description:= Automatisierungsfehler
Das Objekt ist statisch. Der Vorgang ist nicht erlaubt.
Source:= VBAProject
HelpFile:= "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm"
HelpContext:= 1000440
LastDllError 0

The initial 3, or rather 1 and 3, results show that there is a standard description and help file, ( or “page”/ Context thereof ) for any unrecognised number.

The final 4 are associated with some vbObjectError concept.
Wherever I have looked, vbObjectError appears to be a constant with value of -2147221504
I expect that no one remembers what that really is and / or it is probably broken. … a typical answer given is that .. “.. it generates a unique error number… prevents conflict with any existing number and/ or prevents rewrite in the future—when later versions of Visual Basic that use more error numbers.. “
No one really knows what they mean by conflict in this sense means. In fact it appears the answer was given to them by someone who was given it by someone… if you spend enough time following down the chain you find you come back to the same point, that is to say no one remembers where the rumour started and the original person that started the rumour forgot and later asked someone else that fell for his rumour originally.
Clearly the results are not suggesting that a non used number is being obtained.
The results suggest that by trial and error you can find a number that is not being looking to get the first and third error message. The Help for those options suggests that the number is not used

So you may as well make up numbers that suit any particular logic or idea or organised list that suits you and a description you like.

Probably the only conclusions from this post is that if I chose to .Raise an error, then I might want to first make a code something similar to the ones in this post but which also have an error of the sort or similar to the one I want to do a customised error handler for. The I can get the standard Properties which I may then chose to use either in their entirety, such as in the case of the Help File path , ( and page number ( context Property ) , or I may chose to modify then somehow, such as in the description in order to be more specific about my particular error.

I am thinking that the Custom Error handler using the Err object .Raise method is a waste of time, and probably doing the same with Message boxes or Input boxes is probably a lot more intuitive and more versatile. But for completeness I will have a look at a simple example in the next post.

[DocAElstein]

Err object. Err.Raise. Custom Error handler

Part 2) Custom Error handler using Err object. Err.Raise.

The conclusions from the last post are that this is just a complicated way to get a message box up to warn or inform of an error.
The custom error handler, or rather the working part of it, .Raise is as un intuitive as most of the VBA error handling tools and concepts. One thing that does not follow obviously from the last post I that one thing that the Err.Raise effectively does is to replace any existing user defined error handling with the default VBA error handler, but with modified Properties as defined in the arguments of the .Raise:
Err. Raise(Number:= , Source:= , Description:= , HelpFile:= , HelpContext:= , LastDllError:= )
It appears to do this replacement even in the aroused exceptional state. So effectively we have pseudo
_ [On Error GoTo -1 : Raise exception, use default VBA handler with these modified arguments of( 11 , , , , , ) ]

As noted the use as in the last post of Err.Raise had little practical use. The fact that it appears to work in the Exception state would make it possible to use in such an example as below. There I use the On Error GoTo LabelOrLineNumber initially and then at the error handling code section sent to by the LabelOrLineNumber I will do the .Raise

Consider the simple example looked at already a few times of an attempt to divide by zero.
Based on the experiments from the last post I will decide to
_ give an arbitrary, ( hopefully never used ), Number ,
_ I will choose my own message ( .Description ) ,
_ I will use the .Source always seen from the last post ( It appears to be possible to use anything at all here, - but just to be on the safe side I will use what appears the appropriate one )
_ Use the appropriate help available for this sort of error

The purpose of this code would be to punish the Twat that tried to divide by zero. A different error is handled more politely.

Sub CunstromErrorhandler()
On Error GoTo ErrRaiseHandle
' An Error to be handled politely
Dim Rng As Range
Let Rng.Value = "AnyFink" ' Will error as my Rng has not been set so "doesn't exist"
' An Error to be punished
Dim RslTwat As Double, Destrominator As Long
Let RslTwat = 1 / 0
'
Exit Sub
ErrRaiseHandle:
If Err.Number = 11 Then
Err.Raise Number:=42, Source:="VBAProject", Description:="You stupid Twat, you tried to divide by 0, as punishment I will end the code", HelpFile:="C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1031\VbLR6. chm", HelpContext:=1000011
Else
MsgBox "The error was " & Err.Description & vbCrLf & "The code will continue at the line just after the one which caused the error"
Resume Next
End If
End Sub





In the above code I can't see any major advantage of using the Err.Raise in place of a message box for a simple message, ( or if I wanted to use the Help then I could use the VBA Input box )
If the error is not the divide by zero , then I use a more standard MsgBox using the Err Object Property information



Here the same code again using the VBA Input box in place of the Err.Raise , that is to say doing effectively the same as far as the user is concerned. In this second code as we are not using Err.Raise at all, then we are not doing the likely pseudo _..
_ [On Error GoTo -1 : Raise exception, use default VBA handler with these modified arguments of( , , , , , ) ]

_.. to do later, or find it....

[DocAElstein]

asljclskajcfjc

[DocAElstein]

lkcslcj

[DocAElstein]

ffffjjfsfa

[DocAElstein]

Final Summary table:
http://www.excelfox.com/forum/showthread.php/2079-test-BB-Code?p=10545#post10545

[DocAElstein]

Finallly….
A few Miscellaneous points that might have been missed, or not explicitly stated..

On Error GoTo 0 “works” in both normal and aroused states .. but
An error handler can be replaced in normal state without using On Error GoTo 0
_ If we have an error handler enabled but not active, then we know that On Error GoTo 0 will take us back to the default VBA error handling situation. That code line removes or disables or unplugs the error handler. So just to be clear: On Error GoTo 0 will also remove the error handler from VBA’s memory of it even if the error handler is active and has put us in the aroused exception state/ given us an erecting. So if the On Error GoTo 0 is used in the aroused exception state, then after a resume or On Error GoTo -1 , we will go back to the default VBA error handling situation
What may not be obvious or intuitive is that in the inactive state, we can effectively replace the error handler with a new one by passing either a On Error Resume Next or On Error Resume LabelOrLineNumber error handler statement code line

Ending a code effectively does On Error GoTo -1 and On Error GoTo 0
As far as errors are concerned, then , at least theoretically , the ending of a routine removes the exception and returns error handling to the default VBA error handler. ( There are occasionally reports that after occurrence of errors, a full system restart may be advisable due to some bugs resulting in “something being left over” after an exception has been raised and theoretically cleared )

On Error GoTo -1 followed by a resume:
Probably not of much practical use: just an observation. As noted, the resumes effectively do a On Error GoTo -1 . There would not be a lot of use in including that in the error handling code section if you were then going to use any of the resumes.
But if you did then, curiously the resumes no longer take you to where you might expect: they all take you to the On Error GoTo -1.
In the demo code below, without the On Error GoTo -1 , any of the resumes will allow for another try of the formula, with a modified value of the variable Destrominator, which should work due to us adding a value which would mean that if it had been zero, then the addition of 1 will prevent it from being zero for the next try. However the On Error GoTo -1 causes any resume to go to the On Error GoTo -1 code line

Sub OnErrorGoTo_1resume()
On Error GoTo GetMilk ' Don't come back .. you're not welcome here .. stay away .. https://imgur.com/MKMjW0b .. FOB
'
Dim Destrominator As Long: Let Destrominator = 0
Dim RslTwat As Long
'
Try: Let RslTwat = 10 / Destrominator ' Will error due divide by zero, unless Destrominator is changed in error to a value other than zero
Let RslTwat = 10 / Destrominator ' for an attempt after Resume Next
' you never come here
Exit Sub
GetMilk:
Dim cnt
On Error GoTo -1 ' this causes any of the resumes to bring you here
Let cnt = cnt + 1 ' Count how many times I come here
MsgBox prompt:="This is the " & cnt & " time you were here" ' You come here three times
If cnt = 3 Then Exit Sub ' without this you loop infinitely
Let Destrominator = Destrominator + 1
Resume Try ' or Resume Next or Resume In this code these all have the same effect
End Sub















resumes “work” in the procedure that they are in.
If an error occurs in a called routine or function, then the call line is treated as a single line: the resuming will take place just before or just after the call line or at the specified line in the main code. To allow error handling within the function at the error occurrence, an error handler must be placed within function.
The first code below to demo how resume works in the case of a called routine , only has an error handler in the main code, but the error occurs in a called routine.
Resume Next is used at the end of the error handling code section in the main routine , which means we resume just after the called routine, and never get to the end of the called routine.

Sub ErrorInFunctionWithNoFunctionErrorHandler() ' Main routine
On Error GoTo Bed
'
Dim Rng As Range ' Preparing the variable for the range type object. I have not assigned a specific range to it yet.
Let Rng.Value = "AnyFink" ' Errors, as I have no range assigned to the variable Rng and so cannot give a non existant range a .Value
'
Call EmBed(0)
' You come here after Resume Next
Exit Sub
Bed:
MsgBox prompt:="An error occured in the main routine or the Called routine" & vbCrLf & "If the error was in the called routine then I will resume just after the Call line" & vbCrLf & " if using Resume Next"
Resume Next
End Sub
Sub EmBed(ByVal Destructinator As Long) ' Called routine
'
Dim RslTwat As Double
Let RslTwat = 10 / Destructinator
' You never come here
MsgBox prompt:="You will never see this", Title:="Purgatory"
End Sub






























The code below includes an error handler in the called routine.
Note also that in this code I have included a second error in the main code after the Call of the called routine. Once the second routine is ended, the same error handler as that which handled the first error in the main routine, once again kicks in to handle the third overall error which is the second error in the main routine. I assume VBA somehow stores “on hold” , as they say “in the stack” , everything about the main routine, including any registered user error handler. It does this as the main routine is “put on hold” / pauses / “freezes”, at the time that the function starts. Then when the function ends ( in the first effectively code after the error and the second code at the normal function End ) the main code restarts “unfreezes” as it was left.


Sub GoInBed() ' main routine
Dim cnt As Long ' to count how many times I was at the error handler in this main routine
On Error GoTo Bed
'
Dim Rng As Range ' Preparing the variable for the range type object. I have not assigned a specific range to it yet.
Let Rng.Value = "AnyFink" ' Errors, as I have no range assigned to the variable Rng and so cannot give a non existant range a .Value
'
Call InBed(0)
' You come here after the error in this main code.
Let Rng.Value = "AnyFink" ' Errors, as I have no range assigned to the variable Rng and so cannot give a non existant range a .Value
'
MsgBox prompt:="You are leaving the main code now" & vbCrLf & "You used the error handler in the main code " & cnt & " times."
Exit Sub
Bed:
Let cnt = cnt + 1 ' Increase the count of how many times you were here
MsgBox prompt:="An error occured in the main routine of" & vbCrLf & Err.Description & vbCrLf & "The count of how many times you were here is " & cnt
Resume Next
End Sub
Sub InBed(ByVal Destructinator As Long) ' Called routine
On Error GoTo EmBed
Dim RslTwat As Double
Let RslTwat = 10 / Destructinator
' come here after error in this called routine
Exit Sub
EmBed:
MsgBox prompt:="You have an error in the Called routine of " & vbCrLf & Err.Description
Resume Next
End Sub















Ref
https://msdn.microsoft.com/en-us/vba/access-vba/articles/elements-of-run-time-error-handling
https://usefulgyaan.wordpress.com/2013/07/10/tracking-the-line-number-where-error-occurs/
https://msdn.microsoft.com/en-us/library/s6da8809.aspx
http://www.freetutes.com/learn-vb6-advanced/lesson11/p14.html
Binding and list stuff from snb
Rory told me stuff and often got it right
Here is the link again to the Notes I nade a couple of years ago. I have added the notes from this “Blog” to them. I have also changed the Word File to a .docm file and added all the codes to it : __ Errors and Error Handling in VBA 2018 (https://app.box.com/s/0qaizoj4lsto8wmhwnml90anlcxv1ki1)
ORNeRe GoRoT N0Nula 1 : https://www.youtube.com/watch?v=6RRv35Ig2mg
https://www.mrexcel.com/forum/general-excel-discussion-other-questions/547261-error-goto-only-works-once.html#post4357648

[DocAElstein]

Link to get to Page 4 ( using 3 above right from post #21, or the 3 below right in the page list, does not work due to number at end of title ) :
https://www.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=19891&viewfull=1 page 4
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*/page4 Page 4

[DocAElstein]

Links that might work to get to Page 3 ( using 3 above right from post #31, or the 3 below right in the page list, does not work due to number at end of title ) :
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=19877#post19877 Page 3
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*/page3 Page 3
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=10565&viewfull=1#post10565 - perhaps close to bottom of page 3

[DocAElstein]

Some extra notes in support of this main forum Post
https://www.excelforum.com/excel-programming-vba-macros/1401857-handling-error-in-vba.html#post5803728
http://www.eileenslounge.com/viewtopic.php?f=30&t=39437




The __Resumes versus the error handler On Error Resume Next
__On Error GoTo -1
Some re thoughts around March 2023
Slightly compacter way to do simple error handling in VBA


The threads referenced above got me into one of my annual re thinks about these things.

__Resumes versus the error handler On Error Resume Next
I already made the point in this Thread (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) that we have two different things,
_ the 3 Resumes ( Resume and Resume Next and Resume LabelOrLineNumber )
, and the perhaps confusingly* named
_ On Error Resume Next
*The second thing is perhaps confusingly named, since the similarities with one of the Resumes are not as much as one might think.

In previous posts we did detailed pseudo codes. Here we will just summarise them, ( and add a link to the full pseudo codings ).
What is a new idea specific to what I am saying here is like… how about this, just a suggestion: The first thing that happens when an error occurs is not an immediate Exception State. Instead, the coding pauses. The Err object gets filled. Possibly the Erl() , is a more fundamental original Visual Basic thing, and is first given the information about the line that was last executed, the erroring code line. But the important new suggestion here is that the macro is paused and information about the error is registered. This will be _(i) in the pseudo codings below,
Then a decision is made

**Before considering first the Resumes ( Resume and Resume Next and Resume LabelOrLineNumber ) , we must remind ourselves that they only work in the exception State. They only come into play, that is to say, have a possible use, after a On Error GoTo LabelOrLineNumber.

_ The 3 Resumes
Resume ( 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=10556&viewfull=1#post10556 )
_(i) An error occurs. (Assume we have a On Error GoTo LabelOrLineNumber ). The macro is paused. Information relating to the error is registered/ registers get filled in. The macro continues at the place specified by LabelOrLineNumber. Code lines are carried out mostly normally , but we are in the Exception State
The decision on what to do for if a Resume is encountered will be:
_(ii) On Error GoTo -1 ' ( This will clear Err and Erl() and takes us out of the exception State, and so back to normal code running.)
_(iii) we continue to run the coding at the same line that caused the error

Resume Next ( 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=10557&viewfull=1#post10557 )
_(i) An error occurs. (Assume we have a On Error GoTo LabelOrLineNumber ). The macro is paused. Information relating to the error is registered/ registers get filled in. The macro continues at the place specified by LabelOrLineNumber. Code lines are carried out mostly normally , but we are in the Exception State
The decision on what to do for if a Resume is encountered will be:
_(ii) On Error GoTo -1 ' ( This will clear Err and Erl() and takes us out of the exception State, and so back to normal code running.)
_(iii) we continue to run the coding at the line after the line that caused the error

Resume LabelOrLineNumber ( 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=10558&viewfull=1#post10558 )
_(i) An error occurs. (Assume we have a On Error GoTo LabelOrLineNumber ). The macro is paused. Information relating to the error is registered/ registers get filled in. The macro continues at the place specified by LabelOrLineNumber. Code lines are carried out mostly normally , but we are in the Exception State
The decision on what to do for if a Resume is encountered will be:
_(ii) On Error GoTo -1 ' ( This will clear Err and Erl() and takes us out of the exception State, and so back to normal code running.)
_(iii) we continue to run the coding at the line specified

So That’s the 3 resumes out of the way. They are very similar, differing only on where the coding finally continues in the normal state. There is nothing new there. I have been preaching that for many years. Others have also, all be it, often a bit patchy, rarely complete guide.


_ On Error Resume Next Some new thoughts
Previously I considered pseudo coding using the On Error GoTo -1 at some point. Just now I am wondering about that. Perhaps the exception state is never reached. The following is just a new suggestion:
( We assume that an On Error Resume Next has been passed somewhere )
_(i) An error occurs. The macro is paused. Information relating to the error is registered.
_(ii) we continue at the next code line after the one that caused the error.

The slight difference in this new suggestion is that we never go into an exception state, or if we do it is somehow very quickly removed, but not in the same way as any of the Resumes are….


Another suggestion:
Just an idea.
_ On Error Resume Next :- this means we never go into The State of Exception. For no particular reason Microsoft limit us to carry on at the line after that erroring. One might wonder why they did not allow us to do something like ____On Error Resume .. at some other place of your convenience..

_ On Error GoTo LabelOrLineNumber:- this means we go into exception (when an error occurs). We can choose where we want a copy of the macro to continue (all be it in the State of Exception )







Some new neat ideas arising from the referenced thread, Slightly compacter way to do simple error handling in VBA
http://www.eileenslounge.com/viewtopic.php?p=305642#p305642

[DocAElstein]

A few Miscellaneous points that might have been missed, or not explicitly stated, or just added much later, to clarify, or revise things…..
around March 2023..

On Error GoTo 0 “works” in both normal and aroused states .. but
An error handler can be replaced in normal state without using On Error GoTo 0
_ If we have an error handler enabled but not active, then we know that On Error GoTo 0 will take us back to the default VBA error handling situation. That code line removes or disables or unplugs the error handler. So just to be clear: On Error GoTo 0 will also remove the error handler from VBA’s memory of it even if the error handler is active and has put us in the aroused exception state/ given us an erecting. So if the On Error GoTo 0 is used in the aroused exception state, then after a resume or On Error GoTo -1 , we will go back to the default VBA error handling situation
What may not be obvious or intuitive is that in the inactive state, we can effectively replace the error handler with a new one by passing either a On Error Resume Next or On Error Resume LabelOrLineNumber error handler statement code line

Ending a code effectively does On Error GoTo -1 and On Error GoTo 0
As far as errors are concerned, then , at least theoretically , the ending of a routine removes the exception and returns error handling to the default VBA error handler. ( There are occasionally reports that after occurrence of errors, a full system restart may be advisable due to some bugs resulting in “something being left over” after an exception has been raised and theoretically cleared )

On Error GoTo -1 followed by a resume:
Probably not of much practical use: just an observation. As noted, the resumes effectively do a On Error GoTo -1 . There would not be a lot of use in including that in the error handling code section if you were then going to use any of the resumes.
But if you did then, curiously the resumes no longer take you to where you might expect: they all take you to the On Error GoTo -1.
In the demo code below, without the On Error GoTo -1 , any of the resumes will allow for another try of the formula, with a modified value of the variable Destrominator, which should work due to us adding a value which would mean that if it had been zero, then the addition of 1 will prevent it from being zero for the next try. However the On Error GoTo -1 causes any resume to go to the On Error GoTo -1 code line

Sub OnErrorGoTo_1resume()
On Error GoTo GetMilk ' Don't come back .. you're not welcome here .. stay away .. https://imgur.com/MKMjW0b .. FOB
'
Dim Destrominator As Long: Let Destrominator = 0
Dim RslTwat As Long
'
Try: Let RslTwat = 10 / Destrominator ' Will error due divide by zero, unless Destrominator is changed in error to a value other than zero
Let RslTwat = 10 / Destrominator ' for an attempt after Resume Next
' you never come here
Exit Sub
GetMilk:
Dim cnt
On Error GoTo -1 ' this causes any of the resumes to bring you here
Let cnt = cnt + 1 ' Count how many times I come here
MsgBox prompt:="This is the " & cnt & " time you were here" ' You come here three times
If cnt = 3 Then Exit Sub ' without this you loop infinitely
Let Destrominator = Destrominator + 1
Resume Try ' or Resume Next or Resume In this code these all have the same effect
End Sub















resumes “work” in the procedure that they are in.
If an error occurs in a called routine or function, then the call line is treated as a single line: the resuming will take place just before or just after the call line or at the specified line in the main code. To allow error handling within the function at the error occurrence, an error handler must be placed within function.
The first code below to demo how resume works in the case of a called routine , only has an error handler in the main code, but the error occurs in a called routine.
Resume Next is used at the end of the error handling code section in the main routine , which means we resume just after the called routine, and never get to the end of the called routine.

Sub ErrorInFunctionWithNoFunctionErrorHandler() ' Main routine
On Error GoTo Bed
'
Dim Rng As Range ' Preparing the variable for the range type object. I have not assigned a specific range to it yet.
Let Rng.Value = "AnyFink" ' Errors, as I have no range assigned to the variable Rng and so cannot give a non existant range a .Value
'
Call EmBed(0)
' You come here after Resume Next
Exit Sub
Bed:
MsgBox prompt:="An error occured in the main routine or the Called routine" & vbCrLf & "If the error was in the called routine then I will resume just after the Call line" & vbCrLf & " if using Resume Next"
Resume Next
End Sub
Sub EmBed(ByVal Destructinator As Long) ' Called routine
'
Dim RslTwat As Double
Let RslTwat = 10 / Destructinator
' You never come here
MsgBox prompt:="You will never see this", Title:="Purgatory"
End Sub






























The code below includes an error handler in the called routine.
Note also that in this code I have included a second error in the main code after the Call of the called routine. Once the second routine is ended, the same error handler as that which handled the first error in the main routine, once again kicks in to handle the third overall error which is the second error in the main routine. I assume VBA somehow stores “on hold” , as they say “in the stack” , everything about the main routine, including any registered user error handler. It does this as the main routine is “put on hold” / pauses / “freezes”, at the time that the function starts. Then when the function ends ( in the first effectively code after the error and the second code at the normal function End ) the main code restarts “unfreezes” as it was left.


Sub GoInBed() ' main routine
Dim cnt As Long ' to count how many times I was at the error handler in this main routine
On Error GoTo Bed
'
Dim Rng As Range ' Preparing the variable for the range type object. I have not assigned a specific range to it yet.
Let Rng.Value = "AnyFink" ' Errors, as I have no range assigned to the variable Rng and so cannot give a non existant range a .Value
'
Call InBed(0)
' You come here after the error in this main code.
Let Rng.Value = "AnyFink" ' Errors, as I have no range assigned to the variable Rng and so cannot give a non existant range a .Value
'
MsgBox prompt:="You are leaving the main code now" & vbCrLf & "You used the error handler in the main code " & cnt & " times."
Exit Sub
Bed:
Let cnt = cnt + 1 ' Increase the count of how many times you were here
MsgBox prompt:="An error occured in the main routine of" & vbCrLf & Err.Description & vbCrLf & "The count of how many times you were here is " & cnt
Resume Next
End Sub
Sub InBed(ByVal Destructinator As Long) ' Called routine
On Error GoTo EmBed
Dim RslTwat As Double
Let RslTwat = 10 / Destructinator
' come here after error in this called routine
Exit Sub
EmBed:
MsgBox prompt:="You have an error in the Called routine of " & vbCrLf & Err.Description
Resume Next
End Sub

[DocAElstein]

:CC<C<YCN

[DocAElstein]

s.cjscsaj

[DocAElstein]

-LFCASLCJD

[DocAElstein]

-LLSCJSAJCSALK

[DocAElstein]

ÖVJLJVFY::FJLKADS

[DocAElstein]

<KCJLKXJCo




































































These may take you to page #5, if one does not get you there, then try the other
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=19909&viewfull=1#post19909 Page 5
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*/page5 Page 5

[DocAElstein]

.vnvnnvn

[DocAElstein]

CLJC

[DocAElstein]

yxjncvlkaja/c

[DocAElstein]

slcjaljcc

[DocAElstein]

ascmyxymc

[DocAElstein]

<ykch<ykhc

[DocAElstein]

CLJC

[DocAElstein]

<KCJLKXJCo

[DocAElstein]

<ykch<ykhc

[DocAElstein]

yxjncvlkaja/c

[DocAElstein]

slcjaljcc

[DocAElstein]

ascmyxymc

[DocAElstein]

.vnvnnvn