4 Attachment(s)
Resume On Error GoTo 0 -1 GoTo Error Handling Statements Runtime VBA Err Handling ORNeRe GoRoT N0Nula 1
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/showth...0559#post10559
_.__________________________
https://excelfox.com/forum/showthrea...9877#post19877 Page 3
https://excelfox.com/forum/showthrea...0Nula-1*/page3 Page 3
https://www.excelfox.com/forum/showt...891&viewfull=1 page 4
https://excelfox.com/forum/showthrea...0Nula-1*/page4 Page 4
https://excelfox.com/forum/showthrea...ll=1#post19909 Page 5
https://excelfox.com/forum/showthrea...0Nula-1*/page5 Page 5
https://excelfox.com/forum/showthrea...ll=1#post19906 Page 6
__________________________________________________ ______________________________________________
This is post https://excelfox.com/forum/showthrea...ll=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 )
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....305700#p305700 )
https://i.postimg.cc/L5tmj8Jc/VBA-De...or-Handler.jpg https://i.postimg.cc/L5KHq18k/VBA-De...r-Div-by-0.jpg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
On Error GoTo LabelOrLineNumber only works once
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
Code:
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)
Code:
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
Error Handling statement Resumes Resumes resume
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/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
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=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Error Handling statements Resumes .. Error Handling statement Resume
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.
Code:
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:
Code:
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
'
Error Handling statement Resumes... Error Handling statement Resume Next
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
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 .
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
Handling statement Resumes .. Error Handling statement Resume LabelOrLineNumber
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.
Code:
Sub PseudoResumeLabelOrLineNumberGoToGet5ButComeBackDarling()
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
'
Code:
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.
Code:
' OnErrorGoTo0 In Stiffy : With an erection I remove the user error handler
Sub VBAResumeLabelOrLineNumberOnErrorGoTo0InStiffyModus()
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 VBAResumeLabelOrLineNumberOnErrorGoTo0Schlappschwanz()
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/showth...0559#post10559
Some Notes on On Error Resume Next usage
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/developme...ml#post4820111
https://www.excelforum.com/excel-pro...ml#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:
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
Code:
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: _ ….
Code:
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
Code:
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
Some Notes on On Error Resume Next usage
Some Notes on On Error Resume Next usage
Err object. Err.Raise. Custom Error handler
Here is a link to the second but last post in Page 2, #19 ,
https://excelfox.com/forum/showthrea...ll=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/showthrea...9877#post19877 Page 3
https://excelfox.com/forum/showthrea...0Nula-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”
4 Attachment(s)
Part 1) Err object ... Err.Raise
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:
Code:
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
Code:
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
Attachment 4829Attachment 4830
If we use the number 11 then VBA recognises that as the error when trying to divide by zero, and adds appropriately the description
Code:
Sub Raise11()
Err.Raise Number:=11
End Sub
ErrRaise11Help.JPG : https://imgur.com/tL6uvxN https://i.postimg.cc/R0gz6NNm/Err-Raise11-Help.jpg
Attachment 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
Code:
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-Ra...ollox-Help.jpg
Attachment 4832
_.......continued in next post
Part 1) Err object ... Err.Raise
_..... 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.
Code:
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:
Code:
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...ml#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,
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:
Code:
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.
Part 2) Custom Error handler using Err object. Err.Raise.
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.
Code:
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....
Final Summary table ................... VBA Error Handling ORNeRe GoRoT N0Nula 1
A few Miscellaneous points that might have been missed, or not explicitly stated
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
Code:
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.
Code:
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.
Code:
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...error-handling
https://usefulgyaan.wordpress.com/20...-error-occurs/
https://msdn.microsoft.com/en-us/library/s6da8809.aspx
http://www.freetutes.com/learn-vb6-a...son11/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
ORNeRe GoRoT N0Nula 1 : https://www.youtube.com/watch?v=6RRv35Ig2mg
https://www.mrexcel.com/forum/genera...ml#post4357648
A few Miscellaneous points that might have been missed, or not explicitly stated
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/showt...891&viewfull=1 page 4
https://excelfox.com/forum/showthrea...0Nula-1*/page4 Page 4
Further discussion points Ideas from March 2023
Some extra notes in support of this main forum Post
https://www.excelforum.com/excel-pro...ml#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 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/showthrea...ll=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/showthrea...ll=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/showthrea...ll=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/viewtop...305642#p305642
Miscellaneous points that might have been missed, or not explicitly stated, or just added.
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
Code:
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.
Code:
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.
Code:
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
Further discussion points
