Debugging errors in VBA classes in Excel

Question

I have a module with a function similar to this:

MainModule

Sub Test()
  On Error Resume Next
  Dim O1 As New Class1
  O1.DoSomething
  On Error GoTo 0
End Sub

and a few classes similar to this:

Class1

Sub DoSomething()
  FindStuff
  
  'create similar objects who perform similar operations and raise similar errors
  Dim O2 As New Class2
  O2.DoSomething
End Sub

Function FindStuff() As Stuff
  'scan the WorkBook, the file system, etc. and organize the members of the object
  If CorruptedFileSystem Then Err.Raise 514, "File system corrupted"
  If CorruptedWorkBook Then Err.Raise 515, "WorkBook corrupted"
  If Found Then Set FindStuff = FoundStuff
End Function

There is an error trapping option in VBA menu Tools > Options > General tab:

If I set the error trapping to Break in Class Module then the On Error Resume Next will be ignored and every Err.Raise will stop the execution inside the class.

If I set the error trapping to Break on Unhandled Errors then the Err.Raise will stop the execution at the call on the main module, not inside the class.

So in one case I can't execute the code with handled errors, in the other case I can't debug unhandled errors.

The problem becomes unmanageable when the project grows and the main module creates an object that opens a form (which is another object) that creates more objects. Some of the methods handle their own errors and some are designed to abort and raise the error to be managed by the caller.

Is there a way to handle and debug errors in classes?

EDIT

Apparently my question wasn't clear enough. I changed the title and I will try with a clearer example.

Module1

Sub Test1()
  Dim O As New Class1
  O.UnhandledCall
End Sub

Sub Test2()
  On Error Resume Next
  Debug.Print 1 / 0
  Dim O As New Class1
  O.HandledCall
  On Error GoTo 0
End Sub

Class1

Sub UnhandledCall()
  Debug.Print 2 / 0
End Sub

Sub HandledCall()
  Debug.Print 3 / 0
End Sub

Test1

Set Error Trapping = Break on Unhandled Errors and execute Test1. The debugger will not stop at the unhandled error 2 / 0. Instead it will stop at O.UnhandledCall, making it impossible to know what line caused the error, what were the local variable values, the stack, etc.

Test2

Set Error Trapping = Break in Class Module and execute Test2. The debugger will not stop at 1 / 0, good, because the error is handled. But it will stop at 3 / 0 inside the class even if the error is handled inside the caller function, at the same level as 1 / 0.

Sad summary

So with the first setting I can't see where en error is trhown, with the second setting I cant run a macro that cleanly handles errors.

This is obviously an oversimplified example. The real world case I'm dealing with at this moment is a form that creates dozens of objects, some objects check some text files, other objects open drawings on a CAD via COM, other objects talk to a database, etc. If any of the conditions is inconsistent I want to abort the form opening.

As the objects are created, they execute thousands of lines of code, with hundreds of managed errors. When they find something unmanageable in a file, in a drawing or in a database, they defer the error handling to their caller, climbing the stack up to the form that should fail to open and up to the caller that should detect the error and do something about it.

I would expect the debugger to run smoothly through the managed errors and stop when there is an unmanaged error at the offending line. Instead the debugger works as expected in modules, but in classes it either stops at all the error or it never stops, regardless of whether they are managed or not.

For example if I set Error Trapping = Break in Class Module all the managed errors will break the execution as in Test2, and my debugging session will never end.

While if I set Error Trapping = Break on Unhandled Errors then I will never know what triggered the error, because the debugger will climb through all the classes up to the first object and tell me that that's the line that caused the error as in Test1.

Answer 1

As you've noticed, you can't bubble up runtime errors raised in a class module and debug on-the-spot just by tweaking the IDE/debugger settings.

There's another way though. Define a project-wide conditional compilation value, say DEBUG_MODE:

In your class modules' error handlers, use conditional compilation logic to make a programmatic break:

Public Function FetchResults(ByVal filter As String) As Collection
    On Error GoTo CleanFail

    Dim results As Collection
    Set results = this.Repository.Where(filter)

CleanExit:
    Set FetchResults = results
    Exit Function

CleanFail:
#If DEBUG_MODE = 1 Then
    Stop
#Else
    Err.Raise Err.Number 'rethrows with same source and description
#End If
    Set results = Nothing
    Resume CleanExit
End Sub

---

If you don't mind the VBE popping up on your puzzled users then you could also use Debug.Assert statements to break execution when a condition is not met:

Public Function FetchResults(ByVal filter As String) As Collection
    On Error GoTo CleanFail

    Dim results As Collection
    Set results = this.Repository.Where(filter)

CleanExit:
    Set FetchResults = results
    Exit Function

CleanFail:
    Debug.Assert Err.Number <> 0 ' will definitely break here
    Set results = Nothing
    Resume CleanExit
End Sub