vbObjectError Not Working as Expected

[johnywhy]

This page:
https://bettersolutions.com/vba/error-handling/raising-errors.htm

says:

"If you are generating errors, then you should add your number to the vbObjectError constant. Eg Err.Raise Number = vbObjectError + 1000
The range 513 - 65535 is available for user defined errors"

Ok, but i did, and got a native error:


Err.Raise vbObjectError+515
Automation error
A syntax error occurred trying to evaluate a query string

This site
https://exceldevelopmentplatform.blogspot.com/2018/03/vba-error-codes-and-descriptions.html

says the error code for that error-message is

EVENT_E_QUERYSYNTAX As Long = &H80040203

The VBA immediate pane confirms this:

?&H80040203
-2147220989

If i'm supposed to add vbObjectError, and if 513-65535 is usable, then why does `Err.Raise vbObjectError+515` give me a native error!?

bettersolutions.com also says:

"The range 513 - 65535 is available for user defined errors"

Ok, let's say they mean my custom codes should start at 513, and forget about vbObjectError.

But their Codes page shows native errors of 520, 521, some 700's, and some 31000's.
https://bettersolutions.com/vba/error-handling/error-codes.htm

also O'Reilly says:

"we are left with numbers in the range vbObjectError + 512 to vbObjectError + 65535"
https://www.oreilly.com/library/view/vbnet-language-in/0596003080/ch09s04.html

So i'm totally confused.

Thx!

also asked here
https://www.excelforum.com/excel-programming-vba-macros/1360272-vbobjecterror-not-working-as-expected.html#post5574397

[johnywhy]

Microsoft documentation seems to give yet different advice:



"User-defined error numbers should be greater than vbObjectError."
https://docs.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.constants.vbobjecterror

So, i can start at vbObjectError + 1?

[Paul_Hossler]

Did you have any error handling specified (like in examples below)?


Post #2 at

https://www.vbforums.com/showthread.php?570075-vbObjectError-what-is-it-for

has good writeup. Old but still valid as far as I know

It seems the intent by MS was to not let things get confusing


Because Microsoft has reserved the error numbers from 1 to 2147221504 for their use and the reason why you add 2147221504=vbObjectError to what ever error number you want to raise is because things don't get confusing. Lets say you have X number of error traps within your code and you have numbered them from 1 to X without the addition of the vbObjectError constant. Then you die for some reason or are no longer working at the place you designed this code and your customer recieves error number 11 with your description but when your customer looks this error number up on MS's web site it will come back as a division by zero error.




Option Explicit


'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/number-property-visual-basic-for-applications


Sub ForceError()
Dim x As Long, y As Long
Dim o As clsDivide

Set o = New clsDivide

x = Application.InputBox("Enter X as numerator")
y = Application.InputBox("Enter Y as denominator")

On Error GoTo ErrHandler

MsgBox o.DivideXY(x, y)

On Error GoTo 0




Exit Sub


ErrHandler:

' My error
If (Err.Number - vbObjectError) = 515 Then
MsgBox (Err.Number - vbObjectError) & " -- " & Err.Description, vbCritical, Err.Source

'another error
Else
MsgBox Err.Number & " -- " & Err.Description, vbCritical, Err.Source
End If



End Sub


Sub ForceError1()
Dim x As Long, y As Long

x = Application.InputBox("Enter X as numerator")
y = Application.InputBox("Enter Y as denominator")

On Error GoTo ErrHandler

If y = 0 Then Err.Raise 515, "Force Error 1", "Can't divide by zero"

On Error GoTo 0




Exit Sub


ErrHandler:

MsgBox (Err.Number) & " -- " & Err.Description, vbCritical, Err.Source


End Sub








Class clsDivideXY



Option Explicit


Private x As Long, y As Long


Function DivideXY(x As Long, y As Long) As Long


If y = 0 Then Err.Raise vbObjectError + 515, "DivideXY", "Can't divide by zero"


DivideXY = x / y


End Function




My thoughts, and you can experiment with the attachment, but I don't think it really makes a lot of difference

[johnywhy]

"I don't think it really makes a lot of difference"
WHAT doesn't make a lot of difference? It doesn't matter what err numbers i use for custom errors? And it's ok if i use native err numbers for custom errors, as you're doing in your code above?

Why would i want to do that? Why wouldn't i want to avoid the native range, so my code knows whether it's a native or custom error? If i'm logging errors and i use native numbers for custom errors, that will create confusion for future devs looking at the thing and for me too. If i'm displaying an error message to the user, and i get a native error description instead of my custom description, that would be incorrect.


"Microsoft has reserved the error numbers from 1 to 2147221504 for their use"
"The range 513 - 65535 is available for user defined errors"
Those two statements seem contradictory.



"the reason why you add 2147221504=vbObjectError to what ever error number you want to raise is because things don't get confusing. "
You say vbObjectError = 2147221504. VBA says vbObjectError = -2147221504



"you should OR your own error messages with vbObjectError when generated within a class module."
Three points of confusion: Do i add, or OR? Do i use vbObjectError ONLY when generated within a class module? What about regular modules?


dilettante wrote:

Basically &H80040000 through &H80040200 are reserved, don't use them. There are also case where the reserved range includes &H80040201 through &H800403FF, so &H80040400 really seems to be the base value to use for your own exceptions. I think there are even other reserved ranges in different scenarios, so I always begin at a base of &H80044700 myself.


Ok, so dilettante agrees that Microsoft isn't clear about WHICH ranges are reserved. Unclear whether to use decimal or hex, whether the values are negative or unsigned, and whether to use OR or +. dilettante's recommendations seem to contradict the Microsoft documentation.


For a moment, let's mentally truncate the "-21472" prefix on all numbers, so we can more easily read and compare the numbers. dilettante's "top" of range as int (&H80040200 = -----20992) is smaller than his "bottom" of range (&H80040000 = -----21504), suggesting we are talking about negative numbers. The number he recommends, &H80044700, is even lower than that, -----03328. -----03328 is seventeen-thousand numbers away from the closest known native error. Why such a vast safety-margin? And what then is the available range? -----03328 to what? to 0?


I'm no less confused.


Did you have any error handling specified (like in examples below)?
"Specified"? you mean example code? Same as my first post above.


Sub TestErr()
Err.Raise vbObjectError + 515
' at this point, Error = "A syntax error occurred trying to evaluate a query string", which is a native error.
End Sub

[Paul_Hossler]

"Specified"? you mean example code? Same as my first post above.


You RAISE the error but do you HANDLE the error?

29015

[johnywhy]

sorry, here it is:


Sub TestEntry()
On Error Goto HANDLER
TestErr

HANDLER:
MsgBox Error
End Sub


Sub TestErr()
Err.Raise vbObjectError + 515
End Sub

[Paul_Hossler]

Chip has a good overview

http://www.cpearson.com/Excel/ErrorHandling.htm


https://bettersolutions.com/vba/error-handling/raising-errors.htm (https://bettersolutions.com/vba/error-handling/raising-errors.htm)

has examples showing application vs automation error codes

Like you said

Long, identifying the type of the error. Visual Basic errors range from 0 to 65535
The range 0 - 512 is reserved for system errors
The range 513 - 65535 is available for user defined errors


You add vbObjectError (probably a bad name) to your error so that they don't get tangled up

According to

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/number-property-visual-basic-for-applications



The first example illustrates a typical use of the Number property in an error-handling routine. The second example examines the Number property of the Err object to determine whether an error returned by an Automation object was defined by the object, or whether it was mapped to an error defined by Visual Basic.Note that the constant vbObjectError is a very large negative number that an object adds to its own error code to indicate that the error is defined by the server. Therefore, subtracting it from Err.Number strips it out of the result.
If the error is object-defined, the base number is left in MyError, which is displayed in a message box along with the original source of the error. If Err.Number represents a Visual Basic error, the Visual Basic error number is displayed in the message box.



Here's an example that I hope will explain what I mean

There's 2 Error 11's, one by the macro and one by the system (probably as very bad idea to overlap like that)

There's also what I would consider a more realistic example returning error number = 1000



Option Explicit


Sub TestEntry()
Dim MyErr As Long

On Error GoTo HANDLER

TestErr ' Raises my error 11

TestZero ' Raises system error 11

TestErr2 ' My normal way to raise error condition


Exit Sub




HANDLER:
' First, strip off the constant added by the object to indicate one of its own errors.
MyErr = Err.Number - vbObjectError


'If you subtract the vbObjectError constant, and the number is still
' in the range 0-65,535, it is an object-defined error code.
If MyErr > 0 And MyErr < 65535 Then
Select Case MyErr
Case 11
MsgBox "You should leave system error numbers alone"
Case 1000
MsgBox MyErr & " -- " & Err.Source & " -- " & Err.Description
Case Else
MsgBox "Something happened"
End Select

' Otherwise it is a Visual Basic error number.
Else
MsgBox Err.Number & " -- " & Err.Source & " -- " & Err.Description
End If

Resume Next
End Sub

Sub TestErr()
Err.Raise vbObjectError + 11, "TestErr", "This is My Error"
End Sub

Sub TestZero()
Dim x As Long
x = 1 / 0
End Sub

Sub TestErr2()
Err.Raise vbObjectError + 1000, "TestErr2", "You did SOMETHING wrong, so go back and fix it!!!"
End Sub

[johnywhy]

I don't see anything on Chip's page about error number.


On further consideration and correspondence with Russell from bettersolutions.com (same as your link), i believe the range for custom errors is -2147221504 to -1.


Adding vbObjectError to my custom number will push my custom number down to around -2 billion.
If my custom numbering starts counting from there, and if VB reserves 0 to 513, then the top of the custom range must be -1.


If all that is true, then the following just confuses me again:


"The range 513 - 65535 is available for user defined errors"

Plus, as mentioned, bettersolutions Codes page shows native errors of 520, 521, some 700's, and some 31000's.


Also, if i'm correct, then this isn't exactly correct (from bettersolutions):

"For any user defined errors, these should be greater than vbObjectError"

In fact, custom numbers should be less than 0.


Another wrench in the works: If i'm supposed to add vbObjectError, and if 513-65535 is usable, then why does `Err.Raise vbObjectError+515` give me a native error!?

[Paul_Hossler]

Chip's page was just a general overview about handling errors

IMHO VBA's error handling leaves something to be desired

FWIW, in my test, TestErr() and TestZero() both use error = 11 (not recommended) since my error and VBA's error have the same number (i.e. 11).
Since I have vbObjectError + 11 in TestErr() they can still be told apart by the error handler, but I think it'd be confusing to have 11 used for 2 different errors



Sub TestErr()
Err.Raise vbObjectError + 11, "TestErr", "This is My Error"
End Sub

Sub TestZero()
Dim x As Long
x = 1 / 0
End Sub

Sub TestErr2()
Err.Raise vbObjectError + 1000, "TestErr2", "You did SOMETHING wrong, so go back and fix it!!!"
End Sub



Plus, as mentioned, bettersolutions Codes page shows native errors of 520, 521, some 700's, and some 31000's.

Yes. No idea why. Maybe MS is inconsistent??? :devil2:





520
Can't empty Clipboard


521
Can't open Clipboard


735
Can't save file to TEMP directory


744
Search text not found


746
Replacements too long


31001
Out of memory


31004
No object


31018
Class is not set


31027
Unable to activate object


31032
Unable to create embedded object


31036
Error saving to file


31037
Error loading from file






If i'm supposed to add vbObjectError, and if 513-65535 is usable, then why does `Err.Raise vbObjectError+515` give me a native error!?

vbObjectError+515 = -2147220989

and

-2147220989 - vbObjectError = 515

-2147220989 is a 'native' error based on the MS does things but the " - vbObjectError" gives you back the original 515 so you know it's in the user error range


The error handling code from the MS doc can be used to determine if it was a user generated application error or a system automation error

Like I said, the error handling process leaves a lot to be desired (another IMHO)
However, I normally use error handling to catch the error (either VBA's or one of mine), determine what it is, and take corrective action. The actual description or type isn't that important TO ME.

Select Case errornumber statements to branch to the appropriate correction / error message / etc. after i find out if it's an application error or an automation error (normally it's mine)



HANDLER:
' First, strip off the constant added by the object to indicate one of its own errors.
MyErr = Err.Number - vbObjectError


'If you subtract the vbObjectError constant, and the number is still
' in the range 0-65,535, it is an object-defined error code.
If MyErr > 0 And MyErr < 65535 Then
Select Case MyErr
Case 11
MsgBox "You should leave system error numbers alone"
Case 1000
MsgBox MyErr & " -- " & Err.Source & " -- " & Err.Description
Case Else
MsgBox "Something happened"
End Select

' Otherwise it is a Visual Basic error number.
Else
MsgBox Err.Number & " -- " & Err.Source & " -- " & Err.Description
End If
Resume Next

[johnywhy]

will take me some time to digest your reply. Meantime, i did this:
This proc lists every unique error in any number range you set.

Sub ListErrs()
Dim lRow As Long, lCol As Long, lErr As Long, sError As String
lCol = 4
Application.ScreenUpdating = False

For lErr = -2000000 To 1000000
Application.StatusBar = lErr
On Error Resume Next
Err.Raise lErr
If (Error <> sError) Then
lRow = lRow + 1
If lRow > 1048576 Then
lRow = 1
lCol = lCol + 1
End If
sError = Error
Cells(lRow, lCol) = lErr & " : " & sError
Windows(1).ScrollRow = lRow
End If
On Error GoTo 0
DoEvents
Next lErr


Application.ScreenUpdating = True
End Sub

I ran it for EVERY number from -2 million to +1 million, and for many (not all) numbers between -2 billion (vbObjectErr) and -2 million. I didn't run for all numbers between -2 billion (vbObjectErr) and -2 million, because that would take about a week (maybe someone out there can make it faster).

Here's what i found. These results seem to disagree with the info on bettersolutions. As you can see, i didn't get all the errors you listed in your last reply.



-2147221504
Automation error


-2147221230
ActiveX component can't create object


-2147221229
Automation error


-2147221164
ActiveX component can't create object


-2147221163
Automation error


-2147221021
ActiveX component can't create object


-2147221020
Automation error


-2147221018
File name or class name not found during Automation operation


-2147221017
Automation error


-2147221014
File name or class name not found during Automation operation


-2147221013
Automation error


-2147221006
Automation error


-2147221005
ActiveX component can't create object


-2147221004
Automation error


-2147221003
ActiveX component can't create object


-2147221002
Automation error


-2147220994
ActiveX component can't create object


-2147220993
Automation error


-2000000
Automation error


0
Invalid procedure call or argument


1
Application-defined or object-defined error


3
Return without GoSub


4
Application-defined or object-defined error


5
Invalid procedure call or argument


6
Overflow


7
Out of memory


8
Application-defined or object-defined error


9
Subscript out of range


10
This array is fixed or temporarily locked


11
Division by zero


12
Application-defined or object-defined error


13
Type mismatch


14
Out of string space


15
Application-defined or object-defined error


16
Expression too complex


17
Can't perform requested operation


18
User interrupt occurred


19
Application-defined or object-defined error


20
Resume without error


21
Application-defined or object-defined error


28
Out of stack space


29
Application-defined or object-defined error


35
Sub or Function not defined


36
Application-defined or object-defined error


47
Too many DLL application clients


48
Error in loading DLL


49
Bad DLL calling convention


50
Application-defined or object-defined error


51
Internal error


52
Bad file name or number


53
File not found


54
Bad file mode


55
File already open


56
Application-defined or object-defined error


57
Device I/O error


58
File already exists


59
Bad record length


60
Application-defined or object-defined error


61
Disk full


62
Input past end of file


63
Bad record number


64
Application-defined or object-defined error


67
Too many files


68
Device unavailable


69
Application-defined or object-defined error


70
Permission denied


71
Disk not ready


72
Application-defined or object-defined error


74
Can't rename with different drive


75
Path/File access error


76
Path not found


77
Application-defined or object-defined error


91
Object variable or With block variable not set


92
For loop not initialized


93
Invalid pattern string


94
Invalid use of Null


95
Application-defined or object-defined error


96
Unable to sink events of object because the object is already firing events to the maximum number of event receivers that it supports


97
Can not call friend function on object which is not an instance of defining class


98
A property or method call cannot include a reference to a private object, either as an argument or as a return value


99
Application-defined or object-defined error


321
Invalid file format


322
Can't create necessary temporary file


323
Application-defined or object-defined error


325
Invalid format in resource file


326
Application-defined or object-defined error


380
Invalid property value


381
Invalid property array index


382
Set not supported at runtime


383
Set not supported (read-only property)


384
Application-defined or object-defined error


385
Need property array index


386
Application-defined or object-defined error


387
Set not permitted


388
Application-defined or object-defined error


393
Get not supported at runtime


394
Get not supported (write-only property)


395
Application-defined or object-defined error


422
Property not found


423
Property or method not found


424
Object required


425
Application-defined or object-defined error


429
ActiveX component can't create object


430
Class does not support Automation or does not support expected interface


431
Application-defined or object-defined error


432
File name or class name not found during Automation operation


433
Application-defined or object-defined error


438
Object doesn't support this property or method


439
Application-defined or object-defined error


440
Automation error


441
Application-defined or object-defined error


442
Connection to type library or object library for remote process has been lost. Press OK for dialog to remove reference.


443
Automation object does not have a default value


444
Application-defined or object-defined error


445
Object doesn't support this action


446
Object doesn't support named arguments


447
Object doesn't support current locale setting


448
Named argument not found


449
Argument not optional


450
Wrong number of arguments or invalid property assignment


451
Property let procedure not defined and property get procedure did not return an object


452
Invalid ordinal


453
Specified DLL function not found


454
Code resource not found


455
Code resource lock error


456
Application-defined or object-defined error


457
This key is already associated with an element of this collection


458
Variable uses an Automation type not supported in Visual Basic


459
Object or class does not support the set of events


460
Invalid clipboard format


461
Method or data member not found


462
The remote server machine does not exist or is unavailable


463
Class not registered on local machine


464
Application-defined or object-defined error


481
Invalid picture


482
Printer error


483
Application-defined or object-defined error


735
Can't save file to TEMP


736
Application-defined or object-defined error


744
Search text not found


745
Application-defined or object-defined error


746
Replacements too long


747
Application-defined or object-defined error


65536
Invalid procedure call or argument

[Paul_Hossler]

I'm sure MS adds error codes whenever it feels like

What are you trying to really do? Maybe someone can suggest a simple solution even if it doesn't follow MS's recommendations

[johnywhy]

I'm sure MS adds error codes whenever it feels like
no doubt, but no matter as long as we know which range is reserved for custom errors.


What are you trying to really do?
raise custom errors, with my own customer handlers, based on custom error number. Native errors will get different handling.

[Paul_Hossler]

FWIW, I usually do something like this, normally without worrying about Using vbObjectError, but I added it in my example




Option Explicit


Sub OneWay()
Dim X As Long, Y As Long, Z As Double
Dim errTemp As Long, errNum As Long
Dim errMsg As String
Dim A(1 To 10) As Long

On Error GoTo ErrHandle

X = 0
Do While X < 5 Or X > 10
X = InputBox("Enter X, between 5 and 10")
If X < 5 Or X > 10 Then Err.Raise 1000, "Sub OneWay", "X must be between 5 and 10. Try again"
Loop


Y = 0
Do While Y <> 0
Y = InputBox("Enter Y")
If Y = 0 Then Err.Raise 1001, "Sub OneWay", "Y cannot = 0. Try again"
Loop



A(1234) = 1234


X = 10000
Y = 0


MsgBox X / Y


Err.Raise vbObjectError + 515


Exit Sub

ErrHandle:
With Err
errTemp = Err.Number - vbObjectError

'automation error
If errTemp > 0 And errTemp < 65535 Then

Call MsgBox("Automation Error Description: " & .Description & Chr(10) & Chr(10) & _
"Error Number: " & .Number & Chr(10) & Chr(10) & _
"Error Source: " & .Source, vbCritical, "Program OneWay")

'application error
Else
Select Case .Number

'my errors
Case 1000
Resume Next ' the 'Loop' statement
Case 1001
Resume Next
Case 1003

'built in errors
Case Else
Select Case .Number
Case 9 ' subscript out of range
Call MsgBox("Subscript out of range, continuing", vbInformation, "Program OneWay")
Resume Next ' go to line after

Case 11 ' divide by 0
Call MsgBox("Can't divide by 0, so I'll call Y = 10", vbInformation, "Program OneWay")
Y = 10
Resume ' try the line again

Case Else
Call MsgBox("Built in Error: " & .Description & Chr(10) & Chr(10) & _
"Error Number: " & .Number & Chr(10) & Chr(10) & _
"Error Source: " & .Source, vbCritical, "Program OneWay")
End Select
End Select
End If
End With
End Sub

[johnywhy]

FWIW, I usually do something like this, normally without worrying about Using vbObjectError, but I added it in my example
thx for example. Why did you use vbObjectError?

[Paul_Hossler]

I'm thinking about using it from now on to handle automation errors better, but I usually don't use it when I raise my own errors (1000, 1001, ...)

Also, there's no reason to have two "Select Case .Number" in the error handler. I just did that to separate my errors from other runtime errors

[johnywhy]

[QUOTE=Paul_Hossler;411626]



errTemp = Err.Number - vbObjectError

If i understand your math, automation errors would fall between vbObjectError (-2147221504) and 65,535 + vbObjectError (-2147155969). Are they native? Why are automation error separate from both native and custom errors?

[Paul_Hossler]

[QUOTE=Paul_Hossler;411626]
If i understand your math, automation errors would fall between vbObjectError (-2147221504) and 65,535 + vbObjectError (-2147155969). Are they native? Why are automation error separate from both native and custom errors?

I just assume that the type of error generating source determines that Err.Number

[johnywhy]

[QUOTE=johnywhy;412428]

I just assume that the type of error generating source determines that Err.Number

Why does your code separate automation errors from both native and custom errors?

[johnywhy]

@Paul_Hossler, you gave me an idea.

Here's a proposed solution, which i THINK might resolve this.

This solution doesn't CARE if my custom error numbers overlap native error numbers. It enables me to have a custom error-numbering strategy without fear of conflicting with native errors (or errors generated by other workbooks or applications.)

The solution exploits the .Source property of Err.

Table on worksheet called "tblCustomErrors"


ErrNum ErrDescription
11 User cancelled the process
53 User clicked pause



VBA:


Sub Test_Handler()
On Error GoTo HANDLER

Debug.Print 1 / 0 ' 11, Division-by-zero
Kill "C:\Doesn't-Exist.txt" ' 53, File not found
Err.Raise 11, "Custom" ' 11, User cancelled
Err.Raise 53, "Custom" ' 53, User paused

Exit Sub
HANDLER:
GlobalHandler
Resume Next
End Sub


Sub GlobalHandler()
Dim sDesc As String

Select Case Err.Source
Case "Custom"
' get description from worksheet
sDesc = WorksheetFunction.VLookup(Err.Number, [tblCustomErrors], 2, False)
Case Else
sDesc = Err.Description
End Select

MsgBox sDesc
End Sub

Who knows, this is might be how .Source was intended to be used.

[Paul_Hossler]

Like I said, I usually just start my errors at 1000 and forget vbObjectError


I've never gotten an all-purpose error handler that I liked

To me, that precluded the ability to

1. automatically take corrective action (case 11 below)

2. to customize the error message to provide needed / useful information (case 53 below)

3. allow user choice (case errBad, errVeryBad below)




Option Explicit


Public Enum errCustomNumbers
errUserCanceled = 1011
errClickedPause = 1053
errBad = 2000
errVeryBad = 2001
errReallyBad = 2002
End Enum


Const errDescUserCanceled As String = "User cancelled the process"
Const errDescClickedPause As String = "User clicked Pause"
Const errDescBad As String = "This is a bad error"
Const errDescVeryBad As String = "This is a VERY bad error"
Const errDescReallyBad As String = "This is a REALLY bad error"




Const MyApp As String = "Test Errors"


Sub Test_Handler()
Dim N As Long, D As Long
Dim F As String


N = 1
D = 0
F = "C:\Doesn't-Exist.txt"


On Error GoTo HANDLER
MsgBox N / D ' 11, Division-by-zero

Kill F ' 53, File not found

Err.Raise errUserCanceled, MyApp, errDescUserCanceled
Err.Raise errClickedPause, MyApp, errDescClickedPause

Err.Raise errBad, MyApp, errDescBad
Err.Raise errVeryBad, MyApp, errDescVeryBad
Err.Raise errReallyBad, MyApp, errDescReallyBad

Exit Sub


HANDLER:
With Err
Select Case .Number
Case 11
D = 1 ' correct error and try again
Resume

Case 53
If MsgBox("File '" & F & "' not found" & vbCrLf & vbCrLf & "Stop Processing?", vbYesNo + vbQuestion, MyApp) = vbYes Then
Exit Sub
Else
Resume Next
End If

Case errUserCanceled, errClickedPause
Call MsgBox(.Description & " (#" & .Number & ")", vbOKOnly & vbInformation, MyApp)
Resume Next

Case errBad, errVeryBad
If MsgBox(.Description & " (#" & .Number & ")" & vbCrLf & vbCrLf & "Stop Processing?", vbYesNo + vbQuestion, MyApp) = vbYes Then
Exit Sub
Call MsgBox(.Description & " (#" & .Number & ")", vbOKOnly & vbInformation, MyApp)
Else
Resume Next
End If

Case errReallyBad
Call MsgBox(.Description & " (#" & .Number & ")", vbOKOnly & vbCritical, MyApp)
Exit Sub

Case Else
Call MsgBox(.Description & " (#" & .Number & ")", vbOKOnly & vbInformation, MyApp)
Resume Next
End Select
End With
End Sub




Just my thoughts

[johnywhy]

Like I said, I usually just start my errors at 1000 and forget vbObjectError
It seems there may be native errors in the region you're using, in which case you are overlapping native numbers. See
https://exceldevelopmentplatform.blogspot.com/2018/03/vba-error-codes-and-descriptions.html


'Private Const ERROR_CANTOPEN As Long = 1011 ' The configuration registry key could not be opened.
'Private Const ERROR_SERVICE_REQUEST_TIMEOUT As Long = 1053 ' The service did not respond to the start or control request in a timely fashion.'Private Const ERROR_INVALID_PIXEL_FORMAT As Long = 2000 ' The pixel format is invalid.
'Private Const ERROR_BAD_DRIVER As Long = 2001 ' The specified driver is invalid.
'Private Const ERROR_INVALID_WINDOW_STYLE As Long = 2002 ' The window style or class attribute is invalid for this operation.
Update: I guess these are Windows errors, not Excel errors. So this might not affect VBA programming.

Iac, the solution i offered above (http://www.vbaexpress.com/forum/showthread.php?69230-vbObjectError-Not-Working-as-Expected&p=412438&viewfull=1#post412438) allows to use native numbers without conflict.



I've never gotten an all-purpose error handler that I liked
Does this mean your example is a handler that you do, or don't like?

I suggest that severity is a different kind of information than type, and shouldn't share the same enum as you have:


Public Enum errCustomNumbers
errUserCanceled = 1011
errClickedPause = 1053
errBad = 2000
errVeryBad = 2001
errReallyBad = 2002
End Enum

[Paul_Hossler]

Does this mean your example is a handler that you do, or don't like?

I suggest that severity is a different kind of information than type, and shouldn't share the same enum as you have:



1. I meant that I've never been able to get something like your sub GlobalHander to be the way I wanted. If it does work for you, that's great. I just usually have the error handling inside the sub and not call another sub since IMO that decreases response flexibility

2. I hadn't thought about a 'severity score' based on the error number (1000-1999 = mild, 2000-2999 = medium, 3000-3999 = sever) but that's not a bad idea

[johnywhy]

I hadn't thought about a 'severity score' based on the error number (1000-1999 = mild, 2000-2999 = medium, 3000-3999 = sever) but that's not a bad idea

Heh, not what i had in mind. I wouldn't combine two distinct pieces of information, if it can be avoided. Severity may be contextual. A corrupt workbook might be fatal for some apps, but ok to ignore and skip for other apps. I would have done:


Enum CustomErr
UserCancel = vbObjectError
UserPause
ProtectedWorkbook
CorruptWorkbook
ShellError
End Enum


Enum ErrorSeverity
Mild
Medium
Severe
End Enum


And then put Severity in Err.Description. Or maybe it could be And'ed or Or'd with error-number, and together put into Err.Number?

[johnywhy]

My solution does work, but i still have to add vbObjectError to my custom errors!

For some reason, my prior example of 11 and 53 worked ok, but passing 0 as error number fails. The raised error is lost, and a new Automation Error occurs:


Err.Raise 0, "Custom"

So, VB won't allow me to overlap native numbers (or certain ones, i guess), even if i specify source. Starting my numbering at vbObjectError works.


Enum CustomError
UserCancel = vbObjectError
UserPause
ProtectedWorkbook
CorruptWorkbook
ShellError
End Enum

Since vbObjectError = -2147221504 , that means my numbering will start at -2147221504 and go up from there. No, i'm wrong, it's not a negative number, it's unsigned, i'm told. Somehow, -2147221504 = 513???

Using "Custom" as error-source to disambiguate the custom error number from native numbers maybe not even needed. Or rather, may be used to differentiate between different types of custom errors, but my custom numbers still can't overlap the native ones.

It seems it would have been better if VB allowed every app to define it's own error numbers (even overlapping ones) with a .Source disambiguator, but what do i know.

[Paul_Hossler]

Debug show vbObjectErrror is a Long



?vartype(vbObjectError) 3




Long (long integer) variables (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#long-data-type) are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.\


BUt, .... if you're not in a class module then you probably want to forget about vbObjectError


Required. Long (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#long-data-type) integer that identifies the nature of the error. Visual Basic errors (both Visual Basic-defined and user-defined errors) are in the range 0–65535. The range 0–512 is reserved for system errors; the range 513–65535 is available for user-defined errors.

When setting the Number (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/number-property-visual-basic-for-applications) property to your own error code in a class module, you add your error code number to the vbObjectError constant (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#constant). For example, to generate the error number (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#error-number) 513, assign vbObjectError + 513 to the Number property.



You could bit map if you didn't have too many errors




Option Explicit


Enum CustomErr
UserCancel = 1
UserPause = 2
ProtectedWorkbook = 4
CorruptWorkbook = 8
ShellError = 16
End Enum




Enum ErrorSeverity
Mild = 2 ^ 13
Medium = 2 ^ 14
Severe = 2 ^ 15
End Enum




Sub test()
Dim sErr As String, sLvl As String
On Error GoTo ErrHandle


' Err.Raise UserCancel + Mild
' Err.Raise UserCancel + Medium
' Err.Raise UserCancel + Severe


Err.Raise ShellError + Medium



Exit Sub




ErrHandle:
If (Err.Number And UserCancel) <> 0 Then
sErr = "User Cancel"
ElseIf (Err.Number And UserPause) <> 0 Then
sErr = "User Pause"
ElseIf (Err.Number And ProtectedWorkbook) <> 0 Then
sErr = "Protected Workbook"
ElseIf (Err.Number And CorruptWorkbook) <> 0 Then
sErr = "Corrupt Workbook"
ElseIf (Err.Number And ShellError) <> 0 Then
sErr = "Shell Error"
End If




If (Err.Number And Mild) <> 0 Then
sLvl = "Mild"
ElseIf (Err.Number And Medium) <> 0 Then
sLvl = "Medium"
ElseIf (Err.Number And Severe) <> 0 Then
sLvl = "Severe"
End If



MsgBox "Error was " & sErr & " with severity of " & sLvl
End Sub

[johnywhy]

Debug show vbObjectErrror is a Long
So, not unsigned?


"The values are not signed Longs, so none of this is about "negative" anything. These values are unsigned and 32 bits long (DWORD)."
https://www.vbforums.com/showthread.php?570075-vbObjectError-what-is-it-for&p=5538131&viewfull=1#post5538131


BUt, .... if you're not in a class module then you probably want to forget about vbObjectError

Question: Is there any drawback to using vbObjectError in a module?

News flash: My "Custom" source method actually does work. As long as there's no harm in re-using native error numbers, i CAN disambiguate with .Source. Just have to start the Enum at 1, not 0. Which means, my custom errors seem to work fine without using vbObjectError. Am i missing anything?

So now i have to choose:
- i can re-use native error numbers, and disambiguate by setting .Source to "Custom"
- or, avoid native numbers by starting my enum at vbObjectError


You could bit map...



...
Medium = 2 ^ 14
Severe = 2 ^ 15
...
CorruptWorkbook = 8
ShellError = 16
...
On Error Goto ErrHandle ' DID YOU FORGET THIS?
...
If (Err.Number And UserCancel) <> 0 Then
sErr = "User Cancel"
...
If (Err.Number And Mild) <> 0 Then
sLvl = "Mild"
...


wOW, niCE woRK!
Stealing :D


... if you didn't have too many errors
How many?

[johnywhy]

.

[Paul_Hossler]

I'm going by the MS documentation:




Long (long integer) variables (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#long-data-type) are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.



Long (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#long-data-type) integer that identifies the nature of the error. Visual Basic errors (both Visual Basic-defined and user-defined errors) are in the range 0–65535. The range 0–512 is reserved for system errors; the range 513–65535 is available for user-defined errors.

When setting the Number (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/number-property-visual-basic-for-applications) property to your own error code in a class module, you add your error code number to the vbObjectErrorconstant (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#constant).








How many?


65535 = 2^16-1

I used the 3 high order bits to mark severity, so that leaves 12 or 13 error messages





On Error Goto ErrHandle ' DID YOU FORGET THIS?


Second line

[johnywhy]

65535 = 2^16-1
I used the 3 high order bits to mark severity, so that leaves 12 or 13 error messages
I would say that's not a useful solution for this application, but still useful for other things.

[Paul_Hossler]

2. I hadn't thought about a 'severity score' based on the error number (1000-1999 = mild, 2000-2999 = medium, 3000-3999 = sever) but that's not a bad idea

I'd think about this

[johnywhy]

If I'm using vbObjectError as recommended, how to compose a conditional which can determine if my error is native or custom?




Enum CustomEr
LaidEgg = vbObjectError
UserEaten
Paused
Cancelled
End Enum


Sub GlobalHandler
If IsCustomErr Then MsgBox "Custom"
End Sub


Function IsCustomErr()As Boolean
ONE OF THESE?
With Err
IsCustomErr = .Number < 0
IsCustomErr = .Number >= vbObjectError
IsCustomErr = (.Number >= vbObjectError) And (.Number < 0)
IsCustomErr = .Number Or vbObjectError
IsCustomErr = TwosComplement(.Number) Or TwosComplement(vbObjectError)
End With
End Function

(also asked here)

[Paul_Hossler]

If I'm using vbObjectError as recommended, how to compose a conditional which can determine if my error is native or custom?

Well, TBH I don't think that you are using vbObjectError as recommended since the way I read the below, you only use vbObjectError in a class module and you don't seem to be using a class module


When setting the Number (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/number-property-visual-basic-for-applications) property to your own error code in a class module, you add your error code number to the vbObject Error constant (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#constant).


Since 746 (attachment) seems to be the highest system error number used, I'd just keep my errors between 1000 and 9999. Anything below is Excel's, anything between is my custom



Visual Basic errors (both Visual Basic-defined and user-defined errors) are in the range 0–65535.

The range 0–512 is reserved for system errors;

the range 513–65535 is available for user-defined errors.

[johnywhy]

you don't seem to be using a class module
Let's assume i am.

My reading of the spec is that vbObjectError isn't required for modules, not that it's forbidden. Do you think i may overlap native numbers if i used vbObjectError for custom errors in modules?


Since 746 (attachment) seems to be the highest system error number used, I'd just keep my errors between 1000 and 9999. Anything below is Excel's, anything between is my custom
maybe, but i don't feel that's reliable. Also, i think the unsigned-int nature of error numbers may be relevant.

Hoping to find an algorithm based on vbObjectError, rather than a hardcoded range.

thx!

[Paul_Hossler]

Well, you can try this




Option Explicit


Enum CustomErr
LaidEgg = vbObjectError + 100
UserEaten = vbObjectError + 200
Paused = vbObjectError + 300
Cancelled = vbObjectError + 400
End Enum




Sub drv()


On Error GoTo ErrHandler

Err.Raise UserEaten, "Sub drv", "User Eaten Error"

MsgBox 1 / 0


Exit Sub

ErrHandler:
GlobalHandler
Resume Next
End Sub




Sub GlobalHandler()
Dim E As Long

E = Err.Number - vbObjectError ' needed to avoid Overflow Error

If (Err.Number < 0) Then
MsgBox "Custom Error, 'Number' = " & E
MsgBox Err.Description
Else
MsgBox Err.Description
End If
End Sub