Trapping and Handling Errors

Quick reference

• 00:05 In this module, I want to show you the concept of trapping errors.
• 00:09 Trapping errors is a really important thing to know, because when you start
• 00:14 developing your own VBA code, you're gonna run into situations where errors happen.
• 00:18 Some of them you're going to anticipate, and that's where the trapping comes in.
• 00:21 Some of them, you're not, and that's where trapping can be even more useful.
• 00:25 The big key here is that we need to know how to do this.
• 00:28 If you take a look at the routine that we have here starting down at the end,
• 00:31 you can see that we've got a particular worksheet, or WSObject,
• 00:35 here that's always trying to activate and then write into a particular range.
• 00:39 And a little bit further up, the routine you can see that this worksheet has been
• 00:42 set to be worksheets target.
• 00:45 The challenge though,
• 00:46 if we look down on the left-hand side is that there is no target worksheet.
• 00:49 There is only sheet 1.
• 00:50 So by reading our code today, we can tell that this is going to trigger an error.
• 00:55 What you'll see here is that as this actually walks through the routine,
• 00:59 the very first thing that happens is the coder must have anticipated this.
• 01:02 Because they've actually got a line in here that says,
• 01:04 I'm not 100% confident this is always going to be here.
• 01:07 So I'm going to say, continue on, even if you do find an error, so
• 01:12 the On Error Resume Next line has been triggered.
• 01:15 It's gonna step and
• 01:15 it's gonna try to assign this Worksheets("Target") to the ws variable.
• 01:21 And at that point, we know it will fail.
• 01:25 What happens then,
• 01:26 is that we've got a test in here to say, if the error number does not equal zero.
• 01:30 And this is a key piece of trapping errors,
• 01:32 is understanding that when you are running your code, if there are no errors,
• 01:36 the error number always equals zero.
• 01:38 Because there's no errors.
• 01:40 As soon as an error is triggered however, it has an error code, and
• 01:44 error number, and a description.
• 01:47 And that's what we're looking to try and capture.
• 01:49 So the key that we're gonna do is,
• 01:51 we're gonna actually turn on the On Error Resume Next, execute the line in question
• 01:54 and then test to see if the error number does not equal zero.
• 01:58 If it doesn’t equal zero, then we can actually, in this case,
• 02:01 feedback a message box with the error number and the error description.
• 02:05 And then we can exit the routine so somebody can deal with it, okay?
• 02:07 On the other hand, if the error number does equal zero, it says, well, okay,
• 02:12 never mind.
• 02:12 We'll continue on.
• 02:13 We'll skip this entire block because it's not equal to zero.
• 02:17 And the next line you encounter is On Error GoTo 0.
• 02:20 Now, this is important because it's setting the error handling back to break
• 02:25 on a handle to exceptions, okay.
• 02:27 That's what the standard line is that we're using when something comes
• 02:30 in and says.
• 02:31 Hey, this didn't work.
• 02:32 It's gonna kick us into a break mode where we can go and debug and edit our code.
• 02:36 So, the key part that we wanna be aware of here,
• 02:39 says this particular line, On Error GoTo 0, should always be set
• 02:43 after we've done our On Error Resume Next and done the test that we need to do.
• 02:48 But it must be set after the test of the error number.
• 02:52 And the reason being, is because when you execute the line On Error GoTo 0,
• 02:57 not only does it turn off the On Error Resume Next, but
• 03:00 it also resets the error counter, alright?
• 03:05 Looking further down the routine at this piece, if we have gone through, check for
• 03:10 errors, we've executed this line of code, there was no error, so
• 03:14 we've turned the errors back on.
• 03:16 At that point, we can go through and do the rest of the code.
• 03:19 Had there been an error, it would have actually picked up any <> 0,
• 03:23 and it would have exited the subroutine.
• 03:26 So let's go take a look and see exactly how this goes.
• 03:29 If we go through, we say, On Error Resume Next,
• 03:31 cuz we think there might be an error coming up.
• 03:33 We try and set to Worksheets("Target").
• 03:36 No feedback, because we've turned off the prompting by using On Error Resume Next.
• 03:40 But, it says, if the error number does not equal zero.
• 03:43 Well, what's happening here?
• 03:44 Ah-ha, it did not equal zero.
• 03:46 Obviously it couldn't activate the target so it's giving us something else.
• 03:50 So now we'll feed back a message box to tell us what happened.
• 03:52 It says, 9 Subscript out of range.
• 03:55 Now that doesn't mean a lot to you potentially, except that that same
• 03:58 error number is gonna be the case every single time you run into this problem.
• 04:01 So if you can't activate your worksheet or you can't set it to a particular piece,
• 04:05 It's gonna give you this error message.
• 04:06 And that's important information, because it means that we can actually deal
• 04:09 with it, which is what we're gonna do in our next module.
• 04:12 So this point, it's action trigger the error, it's fed it back to user,
• 04:16 will now exit the subroutine.
• 04:18 What would happen if this worksheet was actually named, Target.
• 04:25 At that point, we'd step through.
• 04:27 We turn on the error handling, or turn off the ability to break.
• 04:32 We'll set it to a variable.
• 04:34 Is there an error?
• 04:36 Nope, doesn't look like it.
• 04:37 So we'll turn off the On Error Resume Next statement, and now we can go through and
• 04:42 we can actually activate and put in the pieces that we're actually looking for.
• 04:45 So, this is a way to deal with potential problems that may come up
• 04:49 where you expect them.
• 04:51 The more you work with VBA,
• 04:52 the better you will get it recognizing what potentially could be an issue.
• 04:56 A lot of these times it's a trial and error to get stated here.
• 04:59 But you'll start to recognize, hey, if I'm gonna activate a worksheet,
• 05:02 what are my users likely to do and how will they break things?
• 05:04 That's when you start looking to trap things.

Lesson notes are only available for subscribers.