What numbers should be used with vbObjectError?

Question

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

also O'Reilly says:

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

Ok, I did, but I got a native, instead of a custom, error:

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

This site says the error code for that error-message is

EVENT_E_QUERYSYNTAX As Long = &H80040203

The VBA immediate pane confirms this:

?&H80040203
-2147220989  <-- equals vbObjectError + 515

bettersolutions.com also says:

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

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

Ok, let's say they mean my custom codes should start at 513, and forget about vbObjectError. But bettersolutions Codes page shows native errors of 520, 521, some 700's, and some 31000's.

I've read various articles and SO pages on this topic, but still totally confused.

Microsoft documentation I've read seems to give yet different advice:

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

So, can I start at vbObjectError + 1?

Update 1: Native Errors

This procedure 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 a week (maybe someone out there can make it faster).

Here's what I found. These results seem to disagree with the information on bettersolutions.

-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

Update 2: .Source

It seems it would have been better if VB allowed every app to define it's own error numbers (even overlapping ones) with a disambiguator, but what do i know? My answer below demonstrates a possible solution using .Source as diambiguator.

Answer 1

You could use the following code and find out yourself what are the actually free errors numbers:

Sub Errs()

    Dim i As Long
    
    On Error GoTo myErrorHandler
    For i = 1 To 1000 ' change loop control values to your needs 
        Err.Raise vbObjectError + i
    Next
    

myErrorHandler:
    If Err.Description = "Automation error" Then
        With Worksheets("MyErrorSheet") ' change "MyErrorSheet" to your actual "error log" sheet name
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2).Value = Array(i, Err.Description)
        End With
    End If
    Resume Next
    
End Sub

Answer 2

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

It doesn't. It is not a native VB error. Native VB errors do not have vbObjectError in them.

VBA is built upon COM, and is all about COM objects.
vbObjectError, being 0x80040000, represents a HRESULT error code that has:

• S = 1 (SEVERITY_ERROR), which indicates failure, and
• Facility = FACILITY_ITF

Regarding the FACILITY_ITF the documentation states:

For most status codes returned from interface methods. The actual meaning of the error is defined by the interface. That is, two HRESULTs with exactly the same 32-bit value returned from two different interfaces might have different meanings.

That is exactly what you are seeing here. You are legitimately using the value of vbObjectError + 515 as your own error code, but because you never provided a custom description for that custom error, the system [un]helpfully fetched a default COM description for that HRESULT instead, as defined by some other COM component.

So, i can start at vbObjectError + 1?

You can start at vbObjectError + 0. Just provide the textual description in the third argument to Err.Raise:

Err.Raise vbObjectError + 515, , "This is totally my own error and not a syntax error about evaluating some strings."

---

Having that said, do note that adding vbObjectError to your actual error code only applies if you are raising the error from a class module (and frankly, not following this requirement will not do you any harm as long as you stay within VBA). When raising errors from plain functions in modules, you are not even kindly asked to do that, and can just use the error code directly.

Now, there is a contradiction: Err.Raise instructs you that error codes 513–65535 are available for your custom errors, while Trappable errors claim that codes 0-1000 are reserved for future use (and list some codes in 30000's that are also occupied). Note that this is about raw numbers that do not involve adding any vbObjectErrors - this may be the source of the misconception that vbObjectError + 513 is the lowest available number.
Or, you may say that it is not a misconception but rather a rule of thumb in order to keep your error codes in synch: 513 when raised from a module, vbObjectError + 513 when raised from an object.

I personally often find that Err.Raise 5, , "Description" suits most purposes rather well.

Answer 3

There could be any number of workbooks generating custom errors. Trying to step around native numbers and step around each other creates a complicated and brittle error-handling situation.

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 add-ins.)

The solution exploits the .Source property of Err. Normally, .Source gets loaded with the name of the VBA project which caused the error. If you've named the VBA project, that custom name will get loaded into .Source.

Therefor, we can easily use .Source to disambiguate error-numbers.

Table on worksheet called "tblCustomErrors":

ErrNum  ErrDescription
11  User clicked pause
53  User cancelled the process
1   User had a chicken
2   Chicken ate user

VBA:

Enum CustomErrs
          LaidEgg = 1
          UserEaten = 2
          Paused = 11
          Cancelled = 53
End Enum


Sub Test_Handler()
          On Error GoTo HANDLER

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

          ' Custom errors 
          Err.Raise CustomErrs.Paused, xlCustom    ' re-uses native err number
          Err.Raise CustomErrs.Cancelled, xlCustom    ' re-uses native err number
          Err.Raise CustomErrs.LaidEgg, xlCustom
          Err.Raise CustomErrs.UserEaten, xlCustom
          
          Exit Sub
HANDLER:
          GlobalHandler
          Resume Next
End Sub


Sub GlobalHandler()
          Dim sDesc As String
          With Err
                    If (.Source = xlCustom) Then
                              ' get description from worksheet
                              sDesc = Application.VLookup(.Number, [tblCustomErrors], 2, False)
                              .Description = sDesc
                    End If 

                    MsgBox .Description
          End With
End Sub

Enum

An advantage of using Enum instead of explicit numbers is that you get meaningful intellisense and self-documenting code.

Err.Raise LaidEgg, xlCustom

Caveat: Don't use 0 for an error number, it will fail with 'Automation Error'.

I believe this may how .Source was intended to be used.

vbObjectError

This solution could be combined with vbObjectError to avoid native numbers, while still providing the convenience of centralized handling, this way:

Enum CustomErrs
          LaidEgg = vbObjectError
          UserEaten
          Paused
          Cancelled
End Enum

Coded Descriptions

Optionally, instead of storing the descriptions on a sheet-table, they could be hardcoded into the VBA this way. This is a modification of VBA above.

...
Sub GlobalHandler()
    ...
          Select Case Err.Number
                    Case LaidEgg
                              MsgBox "User had a chicken."
                              ' other custom actions
                    Case UserEaten
                              MsgBox "Chicken ate the user."
                              ' other custom actions
                    Case Paused
                              MsgBox "User clicked pause."
                              ' other custom actions
                    Case Cancelled
                              MsgBox "User cancelled the process."
                              ' other custom actions
              End Select
    ...