0

I'm learning error handling and I think I'm getting the hang of it. But I've come across an interesting behavior. I'm handling my errors in my class modules, and I bubble the errors up the stack. I have traceability so when the error reaches the caller (or top of stack) an error is displayed telling me where it occurred at. It works just fine if the class is being called from a module. BUT if the class is being called from the sheet or workbook, then it generates an unhandled runtime error.

For example, take the following code:

sub testError
   err.raise -1000,,"This is a custom error"
end sub

If I run this in a regular module I get the following:

enter image description here

But if I run that exact same code in a sheet or workbook, I get the following:

enter image description here

So some of my classes are being called from macros located on a sheet. Others are being called from workbook and/or sheet events. And if a handled error happens down the line, I get an unhandled runtime error without any useful information.

I can move my macros to a standard module. But I can't move my events (value changes, new caluclation, etc) to a standard module.

So I have two questions. Why can't I do err.raise in a sheet / workbook?

What should I do instead?

Thanks.

braX
  • 11,506
  • 5
  • 20
  • 33
Anthony Meeks
  • 89
  • 7
  • 2
    Change your VBA error setting to "break in class module" and see what changes – Tim Williams Jan 04 '23 at 17:56
  • So that breaks the code in the class. Which isn't what I want either. I want it to break where the class is called, and also contain the descriptive information the class bubbled up. Even if it the class was called in a module, if that module was called from a sheet/workbook, that descriptive info wont come with it. – Anthony Meeks Jan 04 '23 at 18:58
  • 1
    Would help to post the code you're using in your class, and show exactly how you're calling it. Also maybe explain what your error handling class is supposed to *do* in this context. It doesn't seem to be handing the errors: if it was then you wouldn't be getting that pop-up, so what should it be doing? – Tim Williams Jan 04 '23 at 19:09
  • Well, my code is kind of big. The error handler takes the class name and the method the error occurred in and places that in the description. It works just fine if the top of the stack is in a module. But I've since found that this is the case for default errors too. If the error happens in a sheet object or workbook object, VBA will NOT give you an error that will have a "debug" button. I found a relatively easy work around though and I'll post it as an answer. – Anthony Meeks Jan 04 '23 at 19:16
  • "Break in class module" explains the observation in your post though - if there's an error in a "class module" (ie. in the code module for a worksheet/workbook/custom class) then you will get that second pop-up (no "debug" option) unless you have that option checked in your VBA settings.. – Tim Williams Jan 04 '23 at 19:27
  • Also let me further clarify what I mean by error handling. I'm monitoring the inputs being submitted to the object that can break the code else where. So my error handling catches those invalid inputs and throws an error. I don't want the object itself to handle the error, I want it to catch it, and send it up the stack with an appropriate message. That way who ever is writing the caller procedure, will know about the error, and how to fix it. – Anthony Meeks Jan 04 '23 at 19:42

1 Answers1

-2

So digging into this further. There are two kinds of error popups VBA will give you:

  • One with a continue/end/debug/help button
  • One with an okay/help button.

It turns out you can only modify the description with the popup that has the end/debug button. (via the err.raise).

What determines which popup you see is where the top of your stack is. If its in a standard module, you'll get the "end/debug" popup. If its in a sheet/workbook object, then you'll get the "okay/help" popup.

Unhandled errors bubble up all the way to the top stack. If that stack is in a standard module, then you'll have the option of pressing the debug button, which will take you to the line that you're error occurred. If the top stack is in a sheet/workbook, you'll have no idea where it happened.

To illustrate my point, take the following code that will throw a standard runtime error:

sub testError
   dim a as long
   a = clng("X")
end sub

If this method is ran in a standard module you'll get this:

enter image description here

If you run this in a sheet / workbook you'll get this:

enter image description here

The later doesn't really work well with err.raise (you can't edit the description).

My problem is I have macros and events that are initiated in the sheets / workbooks. Those methods then call other methods in a module. But since the top stack is located in a sheet / workbook, the wheels come off of my error handling.

So my work around is to initiate my module level methods in a way that puts those methods at the top of the stack, and then it works:

'standard module
sub createClass
   dim myObj as myClass
   set myObj = new myClass
   call myObj.raiseError
end sub

Instead of calling this method from the sheet / workbook as so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   call createClass
End Sub

Do this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   application.run "createClass"
End Sub

Doing it this way will place the createClass method at the top of the stack. and since this is in a standard module, the error handling will work as expected.

Anthony Meeks
  • 89
  • 7
  • This is a very bad advice. Please explore the [error handling modes](https://stackoverflow.com/q/12687105/11683), pick the one you need (almost always it's Break in class module), and don't use poor error handling that is not compatible even with that option. – GSerg Jan 04 '23 at 21:19
  • The problem I'm seeing that led me down this road has to do with input parameters of a public method inside my class. Its of type long, and it has to be greater than zero. If its not greater than zero, code ending faults will happen down the line. So my error handler watches for this. I do not think its the classes responsibility to change this number to prevent those errors from happening. It should be the callers responsibility, because the caller most likely made a mistake. So I want the error to bubble up to to the caller and not show up in the class. – Anthony Meeks Jan 04 '23 at 22:14
  • If the caller has an active error handler then it *will* bubble up... If it doesn't have an error handler then as far as the user is concerned it will still "crash". – Tim Williams Jan 05 '23 at 16:51
  • The purpose of making the changes to your code as I proposed here is to help a developer find all the errors in the code so they can handle them. My class will not interface directly with any user. Some other class or method will always call it. From a developer standpoint, its nice to not have to chase a bug in a error free class because they submitted an invalid input. Its better to have the class return an error, telling the developer to fix the input. But if a developer had an invalid input without implementing the above changes, they wouldn't have any idea where the error is happening. – Anthony Meeks Jan 05 '23 at 20:05