error trap/handler placement

[SteveDB1]

morning all.
a colleague and I are expanding our macros to start including a specific
error trap or handler so we can move files to a lateral directory, and deal
with them after we're done processing all of our files-- around 5000 files in
all.

One of the things that I've noticed about previous macros that some of you
have helped with is that with the application.inputboxes an error trap is
placed. I.e., if I had 5 input boxes, there would be 5 error traps.

So, my questions are about the placement of error traps, or error handling
statements:

Is it best/wisest to place an error trap/handler (and if I'm saying this
wrong, please forgive me, I'm still vague on sematics with this kind of
thing) at every location where it's plausible that an error will occur?

Or, can we place a single error handler within a macro, and if an error
occurs, it calls to our lateral transfer macro?


Thank you.
Best,
SteveB.

 

[Chip Pearson]

My personal preference (and it is just my preference, not gospel) is
to test all conditions that may cause an error (e.g., missing files,
invalid parameter values, etc) at the top of the procedure and if an
error condition is found, either bail out of the proc before doing any
real work or prompt the user for a valid value. If user input is
required within the procedure, I put those prompts at the beginning of
the proc so that should the user enter an invalid value or wants to
cancel the proc, the code can exit before making any changes to a
worksheet.

Most of the time, my code runs with "On Error Goto 0" or "On Error
Goto Label" in effect. I'll turn on "On Error Resume Next" only for
short sequences of code. A blanket "On Error Resume Next" is too
dangerous.

at every location where it's plausible that an error will occur?

That can be overkill, perhaps, but if you test for error conditions at
the top of the proc, you can reduce the "plausible locations" to a
relatively few number of places.

Or, can we place a single error handler within a macro,

I use a single error handler block to handle run time errors, and then
use the (hidden) Erl function to determine how far in the procedure
the code ran before throwing an error. If you put numeric labels
within the procedure, the Erl function will return the last label
successfully passed. E.g.,

Sub AAA()
On Error GoTo ErrH:
10:
Debug.Print 10
20:
Debug.Print 20
30:
Debug.Print 1 / 0
40:
Exit Sub
ErrH:
Debug.Print "An error occurred at location: " & Erl
End Sub

Here, Erl will return 30 since that was the last label passed before
the error occurred. (A tool like MZTools
http://www.mztools.com/index.aspx can automatically insert all the
line numbers you'll ever need.) Erl is good for debugging and
diagnostics, but a properly written error handler will gracefully
handle the error, returning the workbook to a consistent state and
inform the user (and/or the calling proc) of the nature of the error.

Generally, I like to handle an error within the proc in which it is
thrown, rather than relying on an upstream proc to properly handle it.
I have some notes about error handling at
http://www.cpearson.com/Excel/ErrorHandling.htm .

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[SteveDB1]

Thanks Chip.
I've printed this out, and will review the two site you linked to.
I shared it with my colleague as well, and he said this is like Gold....
as I told Dave P yesterday on another topic-- another satisfied customer.
Best.
Enjoy your weekend.
SteveB