Locked lesson.
About this lesson
Setting up an error handling section for your macro.
Quick reference
Building error handlers
Buidling proper frameworks to handle errors
When to use
To create a VBA error handler that can be used anytime an error is triggered in your code
Instructions
Setting a Label
- Instead of using the On Error Resume Next command to begin a test, use On Error GoTo <Label>
- A label allows us to jump to that section of code whenever it is referenced
- Every time an error is triggered, the code will jump to the error handler label and execute the code in the error handler
- To create a label, type your label name followed by a colon (:)
Running the Error Handler
- As the error handler will be near the bottom of the routine, you will usually want to exit the subroutine before you hit that point. (Failure to do so will run your error handling code even if an error is not present.)
- To exit the subroutine before your error handler label, type Exit Sub
- After the steps of the error handler are performed, use the Resume command to return to the line of code immediately after the line where the error occurred
Syntax format for an error handler
Sub MacroName ()
Dim Variable1 As VariableType
On Error GoTo LabelName
<Code that may contain the error>
Exit Sub
LabelName:
Select Case Err.Number
Case Is = <Error Number>
<Action>
Resume
Case Else
<Action>
End Select
End Sub
Hints & tips
- After you create your label by adding the colon to your label name, the Visual Basic Editor will automatically align this line to the far left
- 00:05 We're now looking at the last of the error handling routines and
- 00:09 this one is where we actually start to look at proper error handing technique.
- 00:15 Now there are errors, and
- 00:16 when I got here, there are things that will work nicely as well.
- 00:19 But what I wanna show you is that generally,
- 00:21 this routine actually looks a little different in the last when we looked at.
- 00:24 You'll see that instead of saying On Error resume next,
- 00:27 we're turning On Error handling when saying On Error GoTo ErrHandler.
- 00:31 Now, ErrHandler is what we call a label, it set up down here.
- 00:36 We can see ErrHandler with a : after it, okay.
- 00:39 That is a label, that's all we need to do is type in whatever the heck we want, and
- 00:42 put a colon after it.
- 00:43 It will align left, and this is a label that can be jumped to, so
- 00:47 every time we trigger an error, it will kick us over to ErrHandler, and
- 00:51 start running the information down below.
- 00:54 So, at this point, we're gonna try and do all the stuff that we want to do.
- 00:56 We're gonna try, and set this worksheet to target.
- 00:58 You'll notice that we don't have a target worksheet listed here.
- 01:02 We're then going to try and take the worksheet that was set to target,
- 01:05 which may or may not work, and we're going to try and activate it, and
- 01:08 then we're going to try to hit this range, which is garbage and set a value to it.
- 01:11 Okay, so, the final thing if everything works right
- 01:14 is we're going to exit the sub routine.
- 01:16 And this is really, really, really important because if you
- 01:19 don't put this in, it'll then run right into your error handler, and
- 01:22 it'll run through all those steps as well.
- 01:24 So that's a bad idea.
- 01:25 You really want to exit the sub routine before you trigger your error handler.
- 01:29 Now, how does this actually work?
- 01:31 You know what, I'm going to step through it to show you,
- 01:33 because this is actually kind of an interesting little component.
- 01:36 So, the first thing we're gonna do is we're gonna turn on the setting to go to
- 01:40 our error handler, should there be an error.
- 01:43 What we're gonna do now is we're gonna try and
- 01:45 set the worksheet to the Target worksheet.
- 01:46 And, of course, we can see already that the target is out of range,
- 01:49 it's not there.
- 01:50 So we're going to try it, Step into, and you'll notice that it jumps straight
- 01:54 into the error handler, skipping all the other lines of code.
- 01:57 And it says, Select Case Err.Number.
- 02:00 Well, as it happens, let's go and find out what the error actually is.
- 02:04 We'll say, err.number, that is error code 9.
- 02:09 So I have a select case statement that says if this is error code 9,
- 02:13 this is a subscript out of range.
- 02:14 Well, that means that the worksheet wasn't there.
- 02:16 So what I'm gonna do is I'm gonna say, well,
- 02:18 in that case let's set ws to a worksheet.
- 02:21 Say Worksheet.Add, so
- 02:23 we'll use that to add a new worksheet, we're then gonna take WS Name and
- 02:28 set it to Target, because that was the worksheet that was trying to activate.
- 02:33 And then we're gonna use the Resume keyword, and this guy here sends us
- 02:37 back-up to the line immediately after where we caused the problem.
- 02:42 So now we'll go and say it was gonna skip all the case else stuff,
- 02:46 it will come back up and it says okay, let's try this again, there we are.
- 02:52 Now we say, okay, that's cool, so
- 02:53 we can see that we have a target worksheet that's been added.
- 02:56 So, let's activate it, and then we're going to set range 5A5.value,
- 03:00 well of course this is a garbage reference, this is gonna work,
- 03:04 this is gonna trigger error.
- 03:06 But maybe I haven't anticipated this one, so I'm gonna step in here and it says,
- 03:10 hang on a second that doesn't work either.
- 03:13 So we're gonna go, select case err.number,
- 03:16 let's see what do we get from error this time here,
- 03:20 this one's an error 1004, different error code, perfect.
- 03:25 So it's not 9 and it says I didn't anticipate this one.
- 03:28 I haven't done anything with it.
- 03:30 So at this point, it's gonna say, hey, you know what?
- 03:33 This is an application to find our object to find error.
- 03:36 So look at that, okay, well, I'm not a hundred percent sure what that mean, so
- 03:39 I'll have to go back and debug my code.
- 03:41 And at that point, I would step through and try and
- 03:43 figure out where it went wrong and I say, you know what this is crazy,
- 03:46 this is not good, so I'm going to fix this.
- 03:48 Now let's try this again, we now have a target worksheet.
- 03:53 So, let's step through our code.
- 03:57 We're gonna turn on our error handler, we're gonna try and
- 04:00 set ws to the target worksheet.
- 04:03 That works nicely,
- 04:04 we didn't need to skip to the error handler because it already exists.
- 04:06 How awesome is that?
- 04:07 So, now we can activate the worksheet and we can set range A5,
- 04:11 because we fixed up this piece of code.
- 04:14 To a value of Hello, and say and with, and now, how do we not put this line
- 04:18 here again, what will happen is it would actually step in the select in cases for
- 04:22 error numbers, which there wouldn't be one, but
- 04:25 in this case x is routine gracefully and we're done, okay?
- 04:28 So this is a good error handler.
- 04:31 You can actually set up all kinds of case in here, if I knew that I
- 04:35 was expecting case 9 or I was expecting case 1004, I could do all these things.
- 04:40 This particular issue captures two things,
- 04:42 number one it anticipates it captures something that I anticipate.
- 04:45 If I'm worried the user might have deleted a worksheet,
- 04:47 I can actually recreate it using this kind of thing, looking,
- 04:50 checking to see if a sub script is out of range and fixing that particular problem.
- 04:54 If I got a coding error that's in my piece,
- 04:56 I'm gonna find that at design development time, I'm not gonna generally find out
- 05:00 when I'm running out there in the wild, although sometimes we do as well.
- 05:04 So if you anticipate it you can set it up in your error handler and
- 05:08 that's gonna help you out in a long term.
Lesson notes are only available for subscribers.