vbObjectError Not Working as Expected

This page:
https://bettersolutions.com/vba/erro...ing-errors.htm

says:

Quote:

---

"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:

Code:

---

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

---

This site
https://exceldevelopmentplatform.blo...criptions.html

says the error code for that error-message is

Code:

---

EVENT_E_QUERYSYNTAX As Long = &H80040203

---

The VBA immediate pane confirms this:

Code:

---

?&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:

Quote:

---

"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/erro...rror-codes.htm

also O'Reilly says:

Quote:

---

"we are left with numbers in the range vbObjectError + 512 to vbObjectError + 65535"

---

https://www.oreilly.com/library/view...0/ch09s04.html

So i'm totally confused.

Thx!

also asked here
https://www.excelforum.com/excel-pro...ml#post5574397

Microsoft documentation seems to give yet different advice:

Quote:

---

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

---

So, i can start at vbObjectError + 1?

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


Post #2 at

https://www.vbforums.com/showthread....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

Quote:

---

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.

---

Code:

---

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

Code:

---

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

Quote:

---

"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.

Quote:

---

"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.

Quote:

---

"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

Quote:

---

"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:

Quote:

---

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.

Quote:

---

Originally Posted by Paul_Hossler

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

---

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

Code:

---

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

---

Quote:

---

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

---

You RAISE the error but do you HANDLE the error?

Attachment 29015

sorry, here it is:

Code:

---

Sub TestEntry()
    On Error Goto HANDLER
    TestErr

HANDLER:
    MsgBox Error
End Sub


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

---

Chip has a good overview

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


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

has examples showing application vs automation error codes

Like you said

Quote:

---

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/off...r-applications

Quote:

---

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

Code:

---

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

---

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:

Quote:

---

"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):

Quote:

---

"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!?

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

Code:

---

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

---

Quote:

---

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:

Quote:

---

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

---

Quote:

---

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)

Code:

---

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

---

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.

Code:

---

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

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

Quote:

---

Originally Posted by Paul_Hossler

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.

Quote:

---

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.

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

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

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?

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

[QUOTE=Paul_Hossler;411626]

Code:

---

        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?

[QUOTE=johnywhy;412428]

Quote:

---

Originally Posted by Paul_Hossler

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

[QUOTE=Paul_Hossler;412434]

Quote:

---

Originally Posted by johnywhy

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?

@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"

Code:

---

ErrNum  ErrDescription
11  User cancelled the process
53  User clicked pause

---

VBA:

Code:

---

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.

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)

Code:

---

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

Quote:

---

Originally Posted by Paul_Hossler

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.blo...criptions.html

Quote:

---

'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 allows to use native numbers without conflict.

Quote:

---

Originally Posted by Paul_Hossler

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:

Quote:

---

Code:

---

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

---

---

Quote:

---

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

Quote:

---

Originally Posted by Paul_Hossler

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:

Code:

---

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?

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:

Code:

---

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.

Code:

---

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.

Debug show vbObjectErrror is a Long

Quote:

---

?vartype(vbObjectError) 3

---

Quote:

---

Long (long integer) variables 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

Quote:

---

Required. Long 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 property to your own error code in a class module, you add your error code number to the vbObjectErrorconstant. For example, to generate the error number 513, assign vbObjectError + 513 to the Number property.

---

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

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

Debug show vbObjectErrror is a Long

---

So, not unsigned?

Quote:

---

"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....=1#post5538131

---

Quote:

---

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

Quote:

---

You could bit map...

Code:

---

...
        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

Quote:

---

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

---

How many?

.

I'm going by the MS documentation:

Quote:

---

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.



Long 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 property to your own error code in a class module, you add your error code number to the vbObjectErrorconstant.

---

Quote:

---

How many?

---

65535 = 2^16-1

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

Quote:

---

On Error Goto ErrHandle ' DID YOU FORGET THIS?

---

Second line

Quote:

---

Originally Posted by Paul_Hossler

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.

Quote:

---

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

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

Code:

---

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)

Quote:

---

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

Quote:

---

When setting the Number property to your own error code in a class module, you add your error code number to the vbObject Error 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

Quote:

---

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.

---

Quote:

---

Originally Posted by Paul_Hossler

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?

Quote:

---

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!

Well, you can try this

Code:

---

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

---