Inconsistent VBA error message box?

Question

It seems that the message box that VBA pops up when an unhandled exception occurs behaves differently depending on ... something? To understand what I mean, create a new .xlsm, , then create a standard module Module1 ; paste inside this code , then insert this Worksheet object inside Sheet1 this code:

 Public Sub TestErrMsgBox() Debug.Print "Hello!" Call Err.Raise(Number:=vbObjectError, Source:="VBAProject.Sheet1", Description:="Lorem Ipsum") End Sub 

In my Excel 2010 Professional Plus, calling a routine in the VBE Immediate window (Ctrl + G):

Call Module1.TestErrMsgBox Affairs>

 Call Sheet1.TestErrMsgBox 

displays the error message Automation Error / Invalid OLEVERB structure .

Now, if you directly call the Raise method from the Immediate window:

 Call Err.Raise(Number:=vbObjectError, Source:="VBAProject.Sheet1", Description:="Lorem Ipsum") 

it will display the Lorem Ipsum error (expected).

What exactly changes in error handling or in the Err object from the first to the last? and how can it be manipulated? I realized that in the first case, the message depends only on the Number argument in the Raise call, but still it does not explain ...

I find this a bit annoying because I expected my posts to be displayed, not something else. The Pokemon approach is always available (catching all exceptions and displaying custom MessageBoxes based on Err properties), but I would like to understand what is happening. :-)

Later edit:

The behavior described above occurs when “Trap Error” is set to “Break on Unprocessed Errors”. After I put “Break on All Errors”, it displays the correct message with the “Debug” button available; however, it is not a good idea to abort on every error ...

In addition, thanks to the feedback from Alex K. , roryap, and Doug Glancy, I realized that this happens when the TestErrMsgBox subroutine TestErrMsgBox called from the Worksheet object and not from the standard module, as I reported incorrectly the first time. Publication fixed; however the question still exists .:-)

Answer 1

The reason this happens when your Sub is in the Sheet module and you set VBE to break due to unhandled errors is because the Sheet module is a class module. Set Error Trapping to break in the class modules and it will behave correctly.

I always use this option anyway, so I can debug inside UserForms and classes.

The search for “VBA err.raise is an invalid break in the structure of an oliverbey in a class module” seems to confirm that it is associated with class modules.

Answer 2

Declare sub as friend in class module instead of public