{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 仙女界的扛把子 的问题《Properly Handling Errors in VBA (Excel)》','https://www.manongdao.com/q-1167979.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
I've been working with VBA for quite a while now, but I'm still not so sure about Error Handling.
A good article is the one of CPearson.com
However I'm still wondering if the way I used to do ErrorHandling was/is completely wrong: Block 1
On Error Goto ErrCatcher
If UBound(.sortedDates) > 0 Then
// Code
Else
ErrCatcher:
// Code
End If
The if clause, because if it is true, will be executed and if it fails the Goto will go into the Else-part, since the Ubound of an Array should never be zero or less, without an Error, this method worked quite well so far.
If I understood it right it should be like this:
Block 2
On Error Goto ErrCatcher
If Ubound(.sortedDates) > 0 Then
// Code
End If
Goto hereX
ErrCatcher:
//Code
Resume / Resume Next / Resume hereX
hereX:
Or even like this: Block 3
On Error Goto ErrCatcher
If Ubound(.sortedDates) > 0 Then
// Code
End If
ErrCatcher:
If Err.Number <> 0 then
//Code
End If
The most common way I see is that one, that the Error "Catcher" is at the end of a sub and the Sub actually ends before with a "Exit Sub", but however isn't it a little confusing if the Sub is quite big if you jump vice versa to read through the code?
Block 4
Source of the following Code: CPearson.com
On Error Goto ErrHandler:
N = 1 / 0 ' cause an error
'
' more code
'
Exit Sub
ErrHandler:
' error handling code'
Resume Next
End Sub
Should it be like in Block 3 ?
I keep things simple:
At the module level I define two variables and set one to the name of the module itself.
Within each Sub/Function of the module I define a local variable
I set ThisRoutineName to the name of the sub or function
I then send all errors to an ERR_RTN: when they occur, but I first set the sLocalErrorMsg to define what the error actually is and provide some debugging info.
At the bottom of each sub/function, I direct the logic flow as follows
I then have a seperate module I put in all projects called "mod_Error_Handler".
The end result is a pop-up error message teling me in what module, what soubroutine, and what the error message specifically was. In addition, it also will insert the Windows error message and code.
I definitely wouldn't use Block1. It doesn't seem right having the Error block in an IF statement unrelated to Errors.
Blocks 2,3 & 4 I guess are variations of a theme. I prefer the use of Blocks 3 & 4 over 2 only because of a dislike of the GOTO statement; I generally use the Block4 method. This is one example of code I use to check if the Microsoft ActiveX Data Objects 2.8 Library is added and if not add or use an earlier version if 2.8 is not available.
Block 2 doesn't work because it doesn't reset the Error Handler potentially causing an endless loop. For Error Handling to work properly in VBA, you need a
Resumestatement to clear the Error Handler. TheResumealso reactivates the previous Error Handler. Block 2 fails because a new error would go back to the previous Error Handler causing an infinite loop.Block 3 fails because there is no
Resumestatement so any attempt at error handling after that will fail.Every error handler must be ended by exiting the procedure or a
Resumestatement. Routing normal execution around an error handler is confusing. This is why error handlers are usually at the bottom.But here is another way to handle an error in VBA. It handles the error inline like Try/Catch in VB.net There are a few pitfalls, but properly managed it works quite nicely.
Sources:
The key to making this work is to use a
Resumestatement immediately followed by anotherOn Errorstatement. TheResumeis within the error handler and diverts code to theEndTry1label. You must immediately set anotherOn Errorstatement to avoid problems as the previous error handler will "resume". That is, it will be active and ready to handle another error. That could cause the error to repeat and enter an infinite loop.To avoid using the previous error handler again you need to set
On Errorto a new error handler or simply useOn Error Goto 0to cancel all error handling.Two main purposes for error handling:
So, how would you do this?
First of all, create an error form to display when an unexpected error occurs.
It could look something like this (FYI: Mine is called frmErrors):
Notice the following labels:
Also, the standard command buttons:
There's nothing spectacular in the code for this form:
Basically, you want to know which button the user pressed when the form closes.
Next, create an Error Handler Module that will be used throughout your VBA app:
You may have errors that will be custom only to your application. This would typically be a short list of errors specifically only to your application. If you don't already have a constants module, create one that will contain an ENUM of your custom errors. (NOTE: Office '97 does NOT support ENUMS.). The ENUM should look something like this:
Create a module that will throw your custom errors.
You are now well equipped to trap errors in your program. You sub (or function), should look something like this:
A copy/paste of the code above may not work right out of the gate, but should definitely give you the gist.
BTW, if you ever need me to do your company logo, look me up at http://www.MySuperCrappyLogoLabels99.com
You've got one truly marvelous answer from ray023, but your comment that it's probably overkill is apt. For a "lighter" version....
Block 1 is, IMHO, bad practice. As already pointed out by osknows, mixing error-handling with normal-path code is Not Good. For one thing, if a new error is thrown while there's an Error condition in effect you will not get an opportunity to handle it (unless you're calling from a routine that also has an error handler, where the execution will "bubble up").
Block 2 looks like an imitation of a Try/Catch block. It should be okay, but it's not The VBA Way. Block 3 is a variation on Block 2.
Block 4 is a bare-bones version of The VBA Way. I would strongly advise using it, or something like it, because it's what any other VBA programmer inherting the code will expect. Let me present a small expansion, though:
Note that second
Resume. This is a trick I learned recently: It will never execute in normal processing, since theResume <label>statement will send the execution elsewhere. It can be a godsend for debugging, though. When you get an error notification, choose Debug (or press Ctl-Break, then choose Debug when you get the "Execution was interrupted" message). The next (highlighted) statement will be either theMsgBoxor the following statement. Use "Set Next Statement" (Ctl-F9) to highlight the bareResume, then press F8. This will show you exactly where the error was thrown.As to your objection to this format "jumping around", A) it's what VBA programmers expect, as stated previously, & B) your routines should be short enough that it's not far to jump.