7

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

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

On my Excel 2010 Professional Plus, calling the subroutine in the VBE's "Immediate" (Ctrl+G) window:

Call Module1.TestErrMsgBox 
Call Sheet1.TestErrMsgBox

will show the error message Automation Error / Invalid OLEVERB structure.

Now, if one calls directly the Raise method from in the "Immediate" window:

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

it will show the (expected) error message Lorem Ipsum.

What exactly changes in the error handling, or in the Err object, from the first case to the last? and how it may be manipulated? I figured out that in the first case the message depends only on the Number argument in Raise call, but still it doesn't explain...

I find this a bit annoying, because I expected my messages displayed, not something else. There's always available the Pokemon approach (catching all the exceptions and showing custom MessageBoxes based on Err properties), but I'd like to understand what happens. :-)

Later edit:

The behavior described above happens when "Error Trapping" is set on "Break on Unhandled Errors." Once I put "Break on All Errors" it displays the correct message, with the "Debug" button available; however, this is not a good idea, to break execution on every error...

Also, thanks to the feedback from Alex K., roryap and Doug Glancy I figured out that this happens when the TestErrMsgBox subroutine is called from within a Worksheet object, not from a standard Module as I incorrectly reported the first time. The posting is fixed; however, the question is still there. :-)

Community
  • 1
  • 1
  • 1
    Odd, works for me in 2k7, however you are supposed to *add* a value to the base code: `Number:=vbObjectError + 1234`, try it that way? – Alex K. May 16 '14 at 16:30
  • What happens if you hit the "Debug" button when the "Invalid OLEVERB structure" error message appears? Where does the code break? – rory.ap May 16 '14 at 16:35
  • @Alex K.: Oh, I tried that... the message the is displays in this case (when raising the exception from the subroutine) is plain "Automation Error", no other description... I guess that, for `vbObjectError + 1234` it has no predefined supplemental information. –  May 16 '14 at 16:36
  • @roryap: Funny, when I configure "Error trapping" to "Break on All Errors" the message box shows the expected "Lorem Ipsum", and the highlighted line is the `Raise` call... The setting used to be "Break on Unhandled Errors" –  May 16 '14 at 16:40
  • 1
    Strange. I can't reproduce your issue on mine. – rory.ap May 16 '14 at 16:45
  • 1
    It's behaving the same in both situations for me. Excel 2010 Pro, Win 7, Break on Unhandled Errors. – Doug Glancy May 16 '14 at 16:48
  • @roryap: The Excel version that I use is 14.0.6112.5000 (32-bit). Unfortunately I cannot post print screens (company policy)... –  May 16 '14 at 16:48
  • @roryap & Doug Glancy: Hmm... okay, could you paste the code to a worksheet code (let's say `Sheet1`) and run in the "Immediate" window: `Sheet1.TestErrMsgBox`, please? –  May 16 '14 at 16:53
  • I did that, and came up with an answer, or at least a solution :). – Doug Glancy May 16 '14 at 17:04
  • All these requests to try things, and then when I answer, nothing? :-) – Doug Glancy May 16 '14 at 17:41
  • 1
    @DougGlancy -- Hey man, I'd accept your answer if I could. I think you nailed it. – rory.ap May 16 '14 at 17:56
  • @Doug Glancy: Sorry for testing your patience, I just couldn't give feedback while I was returning from work. :-) I will comment on your answer also. Again, sorry for the delay. –  May 16 '14 at 18:13
  • Of course. I was aiming for humorously fussy, but may have missed the mark. – Doug Glancy May 16 '14 at 18:28

2 Answers2

5

The reason it happens when your Sub is in a Sheet module and you've set the VBE to Break on Unhandled Errors, has to do with the fact that a Sheet module is a class module. Set Error Trapping to Break in Class Modules, and it will behave correctly.

I always use this setting anyways, so I can debug inside UserForms and classes.

Searching "VBA err.raise invalid oleverb structure break in class module" seems to support the fact that it's related to class modules.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • 1) Thanks again for looking out for a *solution*; however, I would like to know the *reason* for this behavior. I will keep the question open for 1 more day, just in case someone could give an explanation; if this doesn't happen, I will accept your answer, of course. I don’t want to seem ungrateful, just want to have a chance to find out something I’d like to know. ;-) –  May 16 '14 at 18:49
  • 2) *(This point is on general principle, and not a criticism to your answer)* The "Break on Class Modules" is okay for code written for personal use; the normal user of an .xlsm shouldn’t be concerned with tracing into somebody’s else code though. –  May 16 '14 at 18:49
  • 3) Also, "Break on Class Modules" might make impossible some code that *could do* some error handling. E.g. one has a file that may have 2 formats, but basically contains the same information. The two formats are parsed by 2 classes `C1` and `C2`, that raise errors when the specific format is unrecognised. With "Break on Class Modules" it would be impossible to handle the situation as *if is not `C1` then try `C2`, if it’s not `C2` then stop the execution*. –  May 16 '14 at 18:51
  • 4) Given points 2) and 3), I think that would be best practice to avoid adding `Public` subroutines of functions to Excel objects, if those may throw exceptions (like I did). Just overriding the object's hooks (like `Worksheet_Activate`, for example) would do, eventually adding `Private` methods that have all their possible errors handled. –  May 16 '14 at 19:08
  • Very good points. Regarding other users having to set it to Break on Class Modules, that's never been an issue for me because I have error handling that deals with it. However for debugging, it's very useful. – Doug Glancy May 16 '14 at 19:39
  • Regarding Public code in Excel objects, yeah, I don't do that. Finally, I'm curious about the actual answer too. – Doug Glancy May 16 '14 at 19:41
1

Declare sub as friend in class module instead of public

user4899068
  • 11
  • 1
  • Why does that work? My assumption was that different result of an exception is because of the ability to instantiate Sheet/ThisWorkbook, but not Module. As per this answer https://stackoverflow.com/questions/9673333/how-can-i-capture-a-microsoft-access-vba-debug-error-from-my-c-sharp-code/9709082#9709082 – hstdggsdtgsdafssarf456 May 14 '20 at 17:48