4

I am (unfortunately) developing an application in Excel 2000 VBA. I believe I have discovered that any error raised within a Custom Class property, function, or sub debugs as if the error were raised at the point in the VBA code where the property is called. That is, the VBE debugger does not take me to the point in the Class property where the error occurred, but instead where the property was first entered (from a Module Sub or Function, e.g.) This makes it frustrating to develop anything more than the most shallow OO Excel 2000 VBA code since I have to step line-by-line through every Class method to discover the instructions causing an error.

Am I missing something or is this a known bug I have to deal with in Excel 2000? Has this been fixed in 2003 or 2007?

Example code: 

'''''''''''''''
'In Module1:

Public Sub TestSub1()
    Dim testClass As Class1
    Dim testVariant As Variant
    Set testClass = New Class1
    testVariant = testClass.Property1 'Debugger takes me here...
End Sub

''''''''''''''
' In Class1

Property Get Property1() As Variant
    Err.Raise 666, , "Excel 2000 VBA Sux!" 'But error is actually thrown here.
End Property
Community
  • 1
  • 1
Carl G
  • 17,394
  • 14
  • 91
  • 115

4 Answers4

4

For Office 2003 you will get this behaviour when the debugger is configured to break on unhandled errors (the default configuration).

If you want it to break on the Err.Raise line, you need to configure it to break on all errors (Tools/Options/General/Error Trapping/Break on All Errors).

I believe it's the same for Office 2000 but don't have a copy to check.

Joe
  • 122,218
  • 32
  • 205
  • 338
  • Thanks! Actually of the three options (Break on All, Break in Class, and Break on Unhandled Errors) Break in Class is what I think I was looking for. – Carl G Nov 21 '08 at 17:37
1

This page is a very good resource on error handling in VBA:

Carl G
  • 17,394
  • 14
  • 91
  • 115
0

This "feature" is the same in Excel 2003 and I'd be surprised if it's different in 2007.

dbb
  • 2,827
  • 18
  • 16
  • This 'feature' sounds more like a ploy to encourage Visual Studio purchases. Le sigh. Thanks! – Carl G Nov 21 '08 at 04:46
  • I think Microsoft has deliberately neglected VBA for about 10 years, in favour of .Net etc. They just don't get the value that VBA adds in everyday business. – dbb Nov 21 '08 at 04:49
  • It's the same feature. It's the same code in fact. Has been for about a decade now. It seems there is such a thing as too much stability. – Mike Woodhouse Nov 21 '08 at 21:13
0

The same still holds true in Excel 2010 - that's where I met this behaviour.

To quote Chip Pearson's site:

There is absolutely no reason to use an error trapping setting other than Break In Class Module.

His description of the difference between the error modes:

When you are testing and running your code, you have three error trapping modes. The first is Break On All Errors. This will cause the debugger to open if any error occurs, regardless of any On Error handling you might have in the code. The second option is Break On Unhandled Errors. This will cause the debugger to open if the error is not handled by an existing On Error directive. This is the most often used option and is the default setting. The third option, Break In Class Module is the most important and least used. It is not the default error trapping mode, so you have to set it manually.

The Break In Class Module is the most important because it will cause the debugger to break on the line of code within an object module that is actually causing the problem. The Break In Class Module setting is in the Options dialog accessible on the Tools menu. It is on the General tab of the Options dialog, as shown below.

Community
  • 1
  • 1
Scorchio
  • 2,763
  • 2
  • 20
  • 28
  • Are you sure there is no reason? As far as I know with the suggested setting `Err.Raise` cannot be use in a class. – stenci Jun 30 '16 at 15:17