Feelings on error handling

[Legacy 96851]

Hopefully this is the right forum for this, I'd say it's more a discussion than a problem.

I was curious how necessary/encouraged error handling (i.e. the use of "On Error" statements) is in VBA. The fact is, after the past 3 weeks of nothing but Excel, I'm at the point where I know the syntax and can make my macros work with minimal assistance, but I'd like to clean them up a little bit. I'm currently in the process of rewriting a few from scratch, and started to think about error catches, which I almost never use.

When I code in Java, error handling is extremely encouraged (partially because it's in a classroom environment). However, I've mostly found little use for exceptions, as I tend to build a lot of adaptability into my code so it can deal with problems by its very nature (for instance, the strategic placement of conditional statements).

I guess this is almost an issue about programming in general, but I was just curious as to how everyone else deals with their errors in VBA, and how they prefer to see them dealt with.

 

[BrianB]

It depends on what sort of error you are trapping.

Usually If ... End If is needed to check for errors in data entry. (because Excel will not otherwise know if the data is wrong)

On Error ... variants for instances where the VBA code would crash with an error message. When using On error ... make sure that you cancel the error handling with
On Error GoTo 0
afterwards so that you do get an error message for later unexpected problems.

 

[erik.van.geit]

Hi,

In some cases an error would do not too much harm, but in others ...

example:
you used this line

Application.EnableEvents = False

then an error occurs
the code never reaches the line

Application.EnableEvents = True

now you are in trouble !! no events will be launched anymore

kind regards,
Erik

 

[Legacy 96851]

So is it basically good then to run my macro on a blank workbook in a directory with no others (assuming it references other files), see what errors it causes, then go through and put On Error... in those places?

Typically I try to use as many conditional statements as possible, but sometimes I'm baffled with how idiotic the users can be.

 

[SydneyGeek]

Yep, it's amazing what users will try to do. When I've had to test a new workbook I go for both ends of the spectrum -- the really smart user who will come back with constructive criticism and comments, and the gorilla. Error handling is an important part of letting the user know what happened, and/or exiting gracefully.
I've found that error handling in Access is even more important, because users are more likely to be out of their depth if something goes wrong.

Denis

 

[Legacy 98055]

For my own projects I use a little as neccesary. For others, I use as much as I can which usually ends up being a significant percentage of the the total code.

 

[xenou]

I was wondering about this today ... serendipity? I worry more than anything else about the .EnableEvents = False statements in my code. If my error-handler doesn't turn it back on, what's the worst that can happen? To the best of my knowledge (with no events) this means:
--event code I have written will not be triggered
--command buttons and other controls will not work (click events, etc.)

Will there be other internal consequences or is the lack of events only going to affect only my macro-enabled workbooks?

Except for this worry about events I normally try to avoid bookending my routines with On Error Statements and keep the error-handling specifically targeting those blocks where I believe error's might actually occur...but if I've turned off events I become more paranoid about unforeseen problems...Just yesterday I went and added about 10 of these statements to a workbook that I'd finished testing. In any event, I always put in these global error-handlers last...until then, I'd rather go into debug mode when the error occurs.

 

[SydneyGeek]

Except for this worry about events I normally try to avoid bookending my routines with On Error Statements and keep the error-handling specifically targeting those blocks where I believe error's might actually occur...but if I've turned off events I become more paranoid about unforeseen problems...Just yesterday I went and added about 10 of these statements to a workbook that I'd finished testing. In any event, I always put in these global error-handlers last...until then, I'd rather go into debug mode when the error occurs.

One place where I always toggle events is in sheet code. For example, if you have written something for the Worksheet_Change or the Worksheet_Calculate event, you often need to turn off events until the code finishes running if you want your PC to respond any time soon.

Denis

 

[Legacy 96851]

I actually spent almost all of yesterday going through code I'd written in the past 3 weeks and basically break-testing it. The biggest mental block I have with error handling is that, in most cases, there's no way I can fix the problem if the user screws up. Say they run a macro that references a bunch of workbooks that aren't there - sure the macro could create them, but that doesn't really fix the problem at all, because it can't just make up the data. Since I'm not used to others using my code, I don't see much difference between getting Excel's error message, or the one I wrote.

Nevertheless, I think I'm starting to get a feel for the whole thing - for instance, I made sure that if a macro was run on an inappropriate workbook, it wouldn't make any changes, and that it would tell the user which necessary files were missing, all pretty much just using "On Error Resume Next", then code, then "If Err.Number <> 0".

 

[Legacy 98055]

Well. If you trap this error, you can do several things. The worst, which is better than an unhandled error, would shut the thing down graciously with some contact information, info on possible solutions, or in the least, a paraphrased Err.Description. In your case, you could allow the user to browse for the missing workbooks and generally let them know that this is their fault. If your users simply get a "Subscript Out Of Range..., it will be your fault and you will end up fixing it for free.

"On Error Resume Next", then code, then "If Err.Number <> 0".
Also knows as Inline error handling involves more additional code but, IMO, makes the code more readable than using strictly GoTo statements and then casing the err.number...