Error Types

Quick reference

• 00:05 We're now gonna step into looking at errors,
• 00:07 because errors are a fact of life when you're developing your VBA code.
• 00:12 So I wanna show you something that can happen to us here.
• 00:16 We have something where we're setting up to activate a specific worksheet, and
• 00:19 then we're going to put something into the cell value.
• 00:22 The challenge that we have here, is that there are two worksheets in this workbook.
• 00:26 One of them is called Sheet1 and the other one's called myTarget, but
• 00:29 we don't actually have one called target.
• 00:32 So what's gonna happen here, when I try and run this routine,
• 00:35 is it's gonna tell me that we're getting a subscript out of range.
• 00:39 And when I hit Debug, it's gonna highlight the line that's a problem,
• 00:42 Worksheets("Target").Activate.
• 00:44 It can't activate this particular worksheet.
• 00:46 Now, that means something to us, obviously, but
• 00:49 maybe somebody deleted the worksheet or whatever.
• 00:52 There's lots of reasons that can come up.
• 00:54 The big key here is that when it hits something,
• 00:56 it's going to automatically run you into a situation where you need to or
• 01:00 you get dropped into the Visual Basic Editor in order to debug this code.
• 01:04 That's obviously not always advisable because sometimes we can expect errors and
• 01:09 sometimes we don't.
• 01:10 I want to start building up and
• 01:12 showing you the different words that we can use in order to work around this.
• 01:16 There's a specific keyword that actually allows us to execute a line of code, and
• 01:20 step over it, despite the fact that it is causing an error.
• 01:25 And that set of keywords is called On Error Resume Next.
• 01:28 What this does, is it'll actually activate and run this particular line.
• 01:32 And whether there's an error or not, it will continue on.
• 01:35 Now, in this case that might seem, hey, okay, well, that's not a bad idea.
• 01:39 Except that, notice that Range ("A5") is not fully qualified here.
• 01:44 We're not talking about which worksheet it's on.
• 01:46 What we're expecting is that we would activate the target worksheet, and
• 01:49 then write to Range A5 on that worksheet.
• 01:52 The challenge that we have here, is that On Error Resume Next is gonna kick in and
• 01:56 say, just ignore errors and keep going.
• 01:58 So, no problem, we'll step over the activate, not a big deal.
• 02:02 It didn't do anything because it couldn't find the worksheet, but that's okay.
• 02:05 But now what you'll see, is when I run this, it actually writes Hello!,
• 02:09 into Sheet1, because there's no fully qualified worksheet name.
• 02:13 So it says, I tried to activate the worksheet, I couldn't do it,
• 02:16 I'll just write to wherever I am.
• 02:17 That could be really bad news, depending on what's going on in your solution.
• 02:21 So you want to be a little bit careful of that.
• 02:22 This is what I would call a very irresponsible use of this particular set
• 02:26 of keywords.
• 02:27 These keywords are very powerful, they're very useful.
• 02:29 But you don't want to just throw it at the top of your module and hope for the best.
• 02:32 That's really, really poor programming.
• 02:36 Let's take a look at the next routine that I have here,
• 02:38 which is called basic handling.
• 02:40 And this is where we actually use On Error Resume Next in potentially a little bit
• 02:44 more responsible fashion.
• 02:46 You'll see that we have it here, so we're turning off the error handling,
• 02:49 saying you can continue on and do your thing.
• 02:51 And once we're done, we're gonna go back to this On Error GoTo 0.
• 02:55 And that means resume the normal error set up that we're expecting to see,
• 02:59 which is break every time there is an error.
• 03:02 What I mean by break every time there is an error, is that it pops up a message to
• 03:06 say, hey, I've run into an error, would you like to debug?
• 03:09 Hey, that's breaking on error.
• 03:11 Now, let's take a look at the code that's in here.
• 03:15 We have a ws As Worksheet variable and sName As String.
• 03:19 We set sName to myTarget.
• 03:21 And so we can see right here we're looking for a worksheet called myTarget, okay?
• 03:26 And it does exist.
• 03:27 What we're trying to do next is we're trying to change the name of Worksheet1,
• 03:31 so that's Sheet1, to myTarget.
• 03:36 Of course, there's already a worksheet in this workbook called myTarget.
• 03:39 So that will trigger an error, okay?
• 03:41 So if I were to go and say, let's comment this line out for a second.
• 03:46 And I were to step through this, it says, hey, that sheet is already taken.
• 03:51 This is a runtime error 1004, the last one we had was runtime error 9,
• 03:54 try a different name.
• 03:55 Okay, so we'll say End.
• 03:57 Now, what happens when we do this?
• 03:59 We're gonna delete this guy.
• 04:02 So we're gonna try and rename the first worksheet but when I've turned on
• 04:05 On Error Resume next, even if there's an error, it'll step right over that.
• 04:08 The next thing I'm gonna do is, I'm going to set WS to be worksheets sName.
• 04:13 So had I renamed the worksheet, I would be able to do that.
• 04:16 Because the sheet exists, I'm able to do that.
• 04:19 So let's try this now.
• 04:22 There we go, it stepped over the first one.
• 04:24 It stepped over the second one.
• 04:26 Now the On Error Resume Next is still there, so it's still skipping all errors.
• 04:29 So I'm gonna turn that off.
• 04:30 And then I'm gonna set the value in A5 to "Hello!".
• 04:34 And, if we go over to myTarget, you'll see that that's been done.
• 04:37 So this is a more responsible use of this particular piece.
• 04:41 Now, this is kind of an odd routine.
• 04:42 We probably wouldn't see this necessarily in practice,
• 04:45 although stranger things have been done, believe me.
• 04:47 But the big key here is that we've tried something that we expected might fail.
• 04:51 And then we did something else that we know that, had this worked, or
• 04:55 had the sheet already existed, it will work.
• 04:57 And then we turn off our error handling, okay?
• 04:59 That's a proper way of actually dealing with these things, is trying to anticipate
• 05:02 what's involved, and what's going to to change, and making sure it's all set up.
• 05:06 And then turning the On Error Resume Next Status off to
• 05:10 return to proper error handling.

Lesson notes are only available for subscribers.