1
00:00:00,800 --> 00:00:07,370
In this video we're going to talk a little bit about error handlers so you don't always want your error

2
00:00:07,430 --> 00:00:11,930
that you experience in VBA to open up the actual code window.

3
00:00:11,960 --> 00:00:17,540
That's a way that you could accidentally give your users a backdoor into your direct code and give them

4
00:00:17,540 --> 00:00:21,900
the chance to mess something up in the code or to try to fix something and actually make it worse.

5
00:00:21,920 --> 00:00:29,090
And so instead we build error handlers which let the user typically know hey you did experience an error

6
00:00:29,090 --> 00:00:35,630
and you should probably ask somebody about this but it does not open up the code window like VBA error

7
00:00:35,660 --> 00:00:37,070
would usually do.

8
00:00:37,070 --> 00:00:42,500
So I've got some examples of this here of what your error handlers might look like I'd use the two most

9
00:00:42,500 --> 00:00:46,160
common error handlers which are resume next and go to.

10
00:00:46,160 --> 00:00:50,970
So to break this down for you we'll look up at this top section first.

11
00:00:51,210 --> 00:00:58,130
And this we've created two variables A and B they're both a double type area variable which is going

12
00:00:58,130 --> 00:01:05,780
to be a number and then down here we've set a equal to nine and then B equal to six divided by zero.

13
00:01:05,780 --> 00:01:08,770
Now this should throw an error because we can't divide by zero.

14
00:01:08,780 --> 00:01:13,850
So we should be expecting an error on this line and then just to give us another line after this to

15
00:01:13,850 --> 00:01:14,880
test out the code.

16
00:01:14,900 --> 00:01:17,540
We're going to reset a equal to three.

17
00:01:17,570 --> 00:01:21,550
So you'll notice this is the same across all of these three subs that I have here.

18
00:01:21,740 --> 00:01:27,920
We have creating our variables and then setting our variables with an intentional error creating variables

19
00:01:27,950 --> 00:01:34,400
setting variables with an intentional error and setting variables with intentional error down here as

20
00:01:34,400 --> 00:01:35,150
well.

21
00:01:35,210 --> 00:01:41,210
The difference is this top sub right here has no error handling in it.

22
00:01:41,240 --> 00:01:47,800
So when we rub run this it should go ahead and break into the backdoor of our code like it would normally.

23
00:01:48,260 --> 00:01:53,000
This middle subroutine uses On Error Resume Next which we'll talk about.

24
00:01:53,000 --> 00:01:58,320
And the third subroutine uses on error go to example line which we'll talk about.

25
00:01:58,370 --> 00:02:04,220
So starting up with this code I'm going to go ahead and press F eight to step into this and walk through

26
00:02:04,220 --> 00:02:05,990
it.

27
00:02:06,080 --> 00:02:08,080
So first we set a equal to nine.

28
00:02:08,090 --> 00:02:10,630
Now we're setting B equal to six divided by zeros.

29
00:02:10,640 --> 00:02:17,060
When I press update again we get an error and this says runtime error the error is eleven.

30
00:02:17,210 --> 00:02:19,060
We've tried to divide by zero.

31
00:02:19,100 --> 00:02:23,560
So when we click debug we can see right here this is the line that's giving us an error.

32
00:02:23,690 --> 00:02:24,680
And that's what we expect.

33
00:02:24,680 --> 00:02:32,540
So if I actually end this really quick and I go back to excel and I click on macros and I run that subroutine

34
00:02:32,540 --> 00:02:38,710
which was called intentional error and I click run it will go ahead and break into the back end of the

35
00:02:38,710 --> 00:02:39,730
code for me.

36
00:02:39,730 --> 00:02:41,220
And this is what we don't want to do.

37
00:02:41,230 --> 00:02:46,640
We don't want to provide the users access to our code direct access to the code.

38
00:02:46,810 --> 00:02:50,790
And it's often VBA can be overwhelming for people who haven't seen it before.

39
00:02:50,830 --> 00:02:53,670
So we don't want this window to pop up for people.

40
00:02:53,740 --> 00:02:55,740
So we're gonna go ahead and click end.

41
00:02:55,930 --> 00:03:01,960
Now if I jump back to my code this time we'll look in this particular subroutine which is an example

42
00:03:01,960 --> 00:03:04,420
of On Error Resume Next.

43
00:03:04,420 --> 00:03:10,420
So these four words if you place them at the top of your subroutine they tell VBA if you do experience

44
00:03:10,420 --> 00:03:15,130
an error I want you to just ignore that line just jump to the next line and keep going.

45
00:03:15,130 --> 00:03:17,860
Don't break into the code don't pop up any errors.

46
00:03:17,860 --> 00:03:20,770
Just keep moving forward like you would normally.

47
00:03:20,770 --> 00:03:24,190
This is a little bit risky to use for obvious reasons.

48
00:03:24,190 --> 00:03:28,630
You usually want to know when you have an error you usually do want to be alerted that an arrow was

49
00:03:28,630 --> 00:03:34,360
experienced but sometimes you may have an error that you're aware of that you haven't fixed yet or there

50
00:03:34,360 --> 00:03:37,210
may be unique cases where this is applicable.

51
00:03:37,270 --> 00:03:41,620
So it it is a fairly common tool to address errors.

52
00:03:41,620 --> 00:03:47,910
So if I press F eight to step into this we'll see I've hit this On Error Resume Next line so everything

53
00:03:47,940 --> 00:03:52,140
after this should just skip errors that we experience.

54
00:03:52,290 --> 00:03:54,310
So we're going to set a goal line.

55
00:03:54,420 --> 00:03:59,640
Next we're going to be equal to six divided by zero which we know we can't do and see we've skipped

56
00:03:59,640 --> 00:04:00,570
past this line.

57
00:04:00,570 --> 00:04:03,980
We haven't thrown an error message we're not aware that something's gone wrong.

58
00:04:04,050 --> 00:04:09,510
We'll press a vote one more time one more time and we've ended our subroutine without knowing that we

59
00:04:09,510 --> 00:04:10,610
have an error.

60
00:04:10,680 --> 00:04:18,810
If I go back into my code and I go to macros and I run my resume next public subroutine it's going to

61
00:04:18,810 --> 00:04:20,450
run and it's not going to throw an error.

62
00:04:20,460 --> 00:04:22,930
It's not going to break us into the back of the database at all.

63
00:04:22,950 --> 00:04:25,820
So I'll click Run and we can see nothing happens.

64
00:04:25,830 --> 00:04:27,440
I don't get an error message.

65
00:04:27,510 --> 00:04:29,410
The code runs all the way through.

66
00:04:29,520 --> 00:04:32,760
It just ignored this error that we had.

67
00:04:32,760 --> 00:04:37,880
Now typically like we mentioned you don't necessarily just want to ignore your errors.

68
00:04:37,920 --> 00:04:42,600
It's typically important to know that never was experienced at some point.

69
00:04:42,600 --> 00:04:50,640
So instead we can use on error go to and this is a a way of identifying an error that we experienced

70
00:04:50,640 --> 00:04:57,220
without actually breaking into the back end of the code so we type this line up here on air.

71
00:04:57,220 --> 00:05:03,250
Go to example line and you can change this term to be whatever you want it to be whatever you plan on

72
00:05:03,250 --> 00:05:08,740
calling your section and then essentially rebuild the section further down in our code usually at the

73
00:05:08,740 --> 00:05:09,850
end of your code.

74
00:05:09,970 --> 00:05:13,110
You'll type example line or whatever you named it with a colon.

75
00:05:13,210 --> 00:05:21,070
And this tells VBA This is essentially a label and we can jump to this label using go to this example

76
00:05:21,070 --> 00:05:22,390
line like we had here.

77
00:05:22,390 --> 00:05:29,530
So now we've told VBA on an error when you experience an error I want you to go to this label line which

78
00:05:29,530 --> 00:05:35,470
we created down here and all that we've built here is a little message box that says a we experienced

79
00:05:35,470 --> 00:05:39,850
an error when we were running the code and there are times where you could actually get a little bit

80
00:05:39,850 --> 00:05:41,080
more specific with this.

81
00:05:41,080 --> 00:05:46,750
You could build a couple different of these on air or go tos you could use them a couple different places

82
00:05:46,750 --> 00:05:51,760
in your code where you might experience different different errors and then provide a different message

83
00:05:51,760 --> 00:05:53,140
box to the user.

84
00:05:53,140 --> 00:05:56,610
But in our case we're gonna run through this code.

85
00:05:56,750 --> 00:06:02,560
We put this exit sub line to make sure that if our code runs successfully if we didn't have an error

86
00:06:02,560 --> 00:06:08,080
here if we commented this line out for example when it runs through it's going to exit the sub before

87
00:06:08,080 --> 00:06:12,610
it starts hitting any of these labels and displaying messages that it shouldn't display.

88
00:06:12,610 --> 00:06:19,180
So as a precaution we go ahead and tell it to exit sub at the end of what our actual code is by in our

89
00:06:19,180 --> 00:06:19,830
purposes.

90
00:06:19,840 --> 00:06:24,040
If we run through this we should see it jumped to are labeled line down here.

91
00:06:24,130 --> 00:06:28,680
So going to click into this code I'll press f 8 enter.

92
00:06:28,810 --> 00:06:32,890
Now we've gone ahead and told the system this is what I want you to do when you have an error when you

93
00:06:32,890 --> 00:06:36,820
experience an error here's our airlines.

94
00:06:36,830 --> 00:06:41,210
When I press F eight we see it jumps down to our example line here.

95
00:06:41,270 --> 00:06:45,440
We experienced the error it's going to jump pass everything else and it's going to throw this this message

96
00:06:45,440 --> 00:06:52,120
box up for us and we see an error was experienced during the code will hit OK and now it's just gonna

97
00:06:52,130 --> 00:06:55,970
go ahead and the subroutine without continuing to run.

98
00:06:56,000 --> 00:07:03,740
So again if I go back to my Excel code here and I go to macros and click on the go to location macro

99
00:07:03,740 --> 00:07:07,830
and say run it throws this error message we click OK.

100
00:07:07,870 --> 00:07:10,300
And at no point does it open up the VBA window.

101
00:07:10,690 --> 00:07:15,820
So these are two of the most common ways to address errors in your VBA code.

102
00:07:15,820 --> 00:07:22,000
My preference is to use the go to a certain location and that way we let the user know they did experience

103
00:07:22,000 --> 00:07:27,910
an error but we're not breaking into the back end of our code and showing them this VBA window at all.

104
00:07:27,910 --> 00:07:32,470
So this is definitely something to keep in mind as you're building your code and yet you definitely

105
00:07:32,470 --> 00:07:38,020
need to have some sort of error handling built in so resume next and go to location are both great tools

106
00:07:38,020 --> 00:07:38,920
to have for that.