Error Trapping

[AG]

Can VBA Project Error Trapping be set via code in any version of Excel from
2000 up?
If so, how?

I would like to insure that when my workbook opens, it is set to Break on
Unhandled Errors and not Break on All Errors.

Thanks,

 

[Alan McQ via OfficeKB.com]

You can use one of 2 options

1. On Error Resume Next

This will go to the next line when an error is met

2. On Error GoTo TellError 'change TellError to whatever name you want to use

You will need to index TellError in the procedure by entering it at the
bottom of the procedure as follows

TerrError:

Where you want to stop the error trapping use On Error GoTo 0. For point 2,
also remember to use Exit Sub before you index TellError:

HTH

 

[Per Jessen]

Hi

Look at this link, I think this is what you need.

http://www.fmsinc.com/TPapers/vbacode/Debug.asp#Intro

Best regards,
Per

 

[AG]

Thanks for the reply Alan.
I guess I wasn't clear. I do know how to handle errors.

I would like to set the way the VBA project handles error trapping when the
workbook is open.
In any VBA project, from the menu Tools > Options > General > Error
Trapping.

 

[AG]

Thanks for the reply and link Per,

That link is about Access. I do a lot of Access work and regularly use
Application.GetOption, etc.

My question, however, is for an Excel project.
Excel does not seem to have an equivalent to Application.GetOption or
Application.SetOption like Access.

 

[Chip Pearson]

There is no programmatic way to do this. That said, in general, you
don't want to use "Break on Unhandled Errors". It is much better to
use "Break In Class Module". The reason is this: Suppose you have some
class module named Class1 and it has code that goes awry and throws an
error. E.g,

' In Module1
Sub AAA()
Dim C As Class1
Set C = New Class1
C.ABC
End Sub

' In Class1
Public Sub ABC()
Debug.Print 1/0 ' Force an error
End Sub

When you run AAA which creates the instance of Class1 and then calls
the ABC method, a Div/0 error occurs. If you have error handling set
to "Break On Unhandled Errors", the debugger will take you to
procedure AAA to the line C.ABC. That is rather confusing because
there is obviously nothing wrong with line of code "C.ABC". If you use
"Break In Class Module", the debugger will take you into Class1 to the
line that actually threw the 1/0 error. It is much more informative.
And if you don't know why the "wrong" line is highlighted by the
debugger, you can spend quite a bit of time trying to figure it out.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[AG]

Thanks Chip,

I had a feeling there was no way to do it. I had spent a lot of time
searching and could not find anything, but I don't do much work in Excel.
Mostly Access, .NET, etc. I am used to having more control.
I sure wish Microsoft would be more consistant between applications. After
all it is the same UI and VBA.

Your explanation of 'Break In Class Module' is the best I have seen. In my
case it makes little difference as I usually include error handlers in all
of my code. Of course if user has it set to 'Break on All Errors' it doesn't
matter.

 

[Chip Pearson]

Mostly Access, .NET, etc. I am used to having more control.
I sure wish Microsoft would be more consistant between applications. After
all it is the same UI and VBA.

I, too, now spend nearly all my time in NET, generally writing add-ins
and code-behinds for Excel in VBNET or C#. Remember that VBA hasn't
been updated in a decade, and is a stagnant, though not dead,
language. Soon enough, MS is going to put NET into Office, integrated
as tightly as VBA is now. When that happens, there will be a lot more
consistency and it will open a whole new world for development. WPF in
Excel? That's cool.

Your explanation of 'Break In Class Module' is the best I have seen.

Thanks, I'm glad it helped.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)