How to pass error in VBA userform to calling method

Question

Errors within VBA userforms, outside of those that occur in the initialize event, do not appear to be bubbled up to the calling method. Is there any way to force the error to bubble up?

VBA userforms contain an event named userform_error which is defined as

Private Sub UserForm_Error(
    ByVal Number As Integer, 
    ByVal Description As MSForms.ReturnString, 
    ByVal SCode As Long, 
    ByVal Source As String, 
    ByVal HelpFile As String, 
    ByVal HelpContext As Long, 
    ByVal CancelDisplay As MSForms.ReturnBoolean
)

It seems logical that the event UserForm_Error is called when an error occurs in a user form, but that doesn't seem to be the case. In fact, I can't find any documentation for Userform_Error.

I've searched MSDN, Bing, Google, StackOverflow, DuckDuckGo, and I haven't been able to find a good method, or any documentation on what UserForm_error actually does.

Answer 1

It is difficult to give you accurate info for this, the component is ancient. Just some background.

The UserForm object is implemented by Microsoft Forms 2.0, an ActiveX component library. It was a general purpose library to add forms to any application, it wasn't just limited to Office apps. You can find it back on your machine in c:\windows\syswow64\fm20.dll (system32 for a 32-bit machine). Documentation for this component used to be supplied by fm20.chm. This help file is no longer available from Microsoft, you can still find it back with a google query. However, most sites that offer it look very shady. This one looked the least slimy. Actually viewing this file is quite troublesome, I can browse the table of content but none of the pages display text anymore.

One workaround I found was to decompile the file with the HTML Workshop utility. That produced a file named f3evtError.htm, it looks like this (edited for content):

---

Error Event

Occurs when a control detects an error and cannot return the error information to a calling program.

Syntax

Private Sub object_Error( ByVal Number As Integer, ByVal Description As MSForms.ReturnString, _
    ByVal SCode As SCode, ByVal Source As String, ByVal HelpFile As String, _
    ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)

The Error event syntax has these parts:

• object: Required. A valid object name.
• index: Required. The index of the page in a MultiPage associated with this event.
• Number: Required. Specifies a unique value that the control uses to identify the error.
• Description: Required. A textual description of the error.
• SCode: Required. Specifies the OLE status code for the error. The low-order 16 bits specify a value that is identical to the Number argument.
• Source: Required. The string that identifies the control which initiated the event.
• HelpFile: Required. Specifies a fully qualified path name for the Help file that describes the error.
• HelpContext: Required. Specifies the context ID of the Help file topic that contains a description of the error.
• CancelDisplay: Required. Specifies whether to display the error string in a message box.

Remarks

The code written for the Error event determines how the control responds to the error condition.

The ability to handle error conditions varies from one application to another. The Error event is initiated when an error occurs that the application is not equipped to handle.

---

That's all there is, unfortunately. It is vague because the component can be used in so many different kind of ActiveX hosts and error trapping is a host implementation detail. I think the last paragraph is what you are really asking about. I'd say it is fairly safe to assume that, since the Office documentation doesn't mention it, that Office apps in fact do not trigger this event. The fact that the event is still visible in the VBA editor is just a side-effect of how the object model works. There is no easy way for the editor to filter it out, it simply displays all of the published events of the object.

Answer 2

So, I looked into this because I've had the same problem. Super weird about the Error Event but I was able to find some information about it. If you look up Error Event in the Developer Reference in the IDE, but not the offline content the online content. This is what they say: "Occurs when a control detects an error and cannot return the error information to a calling program...The Error event is initiated when an error occurs that the application is not equipped to handle." Now that leads me to believe that this event is only raised when there is some catastrophic error. So looks like you may be out of luck using that.

There is no way to bubble up the error because of the Non-Basic Code

My way around this was for each Userform to have its own Error Handling mechanism, kind of annoying but that's the best I could do. Other than that, in some light and I mean very light and had no/limited interaction with the system I was able to do an On Error Resume Next at the beginning of the code and once I unloaded the userform checked If Err.Number>0 Then Err.Raise Err.Number so the error handler catches it. However, as you probably know, if you choose the second option proceed with caution.

Hope that helps. Let me know what you decide.