1
1

00:00:01,110  -->  00:00:06,440
When executing Visual Basic code, the
chances are quite high that
2

2

00:00:06,440  -->  00:00:09,750
you will have at some point in the past
triggered an error.
3

3

00:00:09,750  -->  00:00:12,340
Chances are, as you've been experimenting
during
4

4

00:00:12,340  -->  00:00:15,240
this title, that you will have created
errors.
5

5

00:00:15,240  -->  00:00:17,990
And effectively there are three types of
error.
6

6

00:00:17,990  -->  00:00:21,730
There are syntax errors where you've
effectively typed something wrong.
7

7

00:00:21,730  -->  00:00:23,940
Those errors are fairly easy to spot.
8

8

00:00:23,940  -->  00:00:27,150
Because when you try to execute the code,
something goes wrong.
9

9

00:00:27,150  -->  00:00:30,040
You go into the code, you spot what's
wrong and you fix it.
10

10

00:00:30,040  -->  00:00:32,530
There are then two other types of error.
11

11

00:00:32,530  -->  00:00:36,180
Runtime errors and books, which are
programming errors.
12

12

00:00:36,180  -->  00:00:39,140
So they fall into two different counts.
13

13

00:00:39,140  -->  00:00:43,190
Let's have a look at how we can stop
runtime errors.
14

14

00:00:43,190  -->  00:00:44,910
Well, let's not say stop.
15

15

00:00:44,910  -->  00:00:50,130
How we can catch an Excel runtime error,
which is completely unhelpful.
16

16

00:00:50,130  -->  00:00:53,230
And usually unintelligible, to the normal
user, and
17

17

00:00:53,230  -->  00:00:56,670
replace it with a, much more user friendly
error.
18

18

00:00:56,670  -->  00:00:59,720
So if we use the error trap file,
available in your working
19

19

00:00:59,720  -->  00:01:05,610
folder, you'll find this has already a
little snippet of code, a sub
20

20

00:01:05,610  -->  00:01:09,140
procedure called cube root, that will work
out the cube root of
21

21

00:01:09,140  -->  00:01:13,100
the active cell, and place that result in
the cell to the right.
22

22

00:01:13,100  -->  00:01:16,590
So we can see here, X takes the value of
the active cell.
23

23

00:01:16,590  -->  00:01:22,240
Y then works out the cube root of X, moves
to the right, so
24

24

00:01:22,240  -->  00:01:25,930
one cell to the right, and then places the
value of Y in that cell.
25

25

00:01:27,380  -->  00:01:30,308
I've also assigned a keyboard shortcut to
it, which is
26

26

00:01:30,308  -->  00:01:33,730
ctrl+shift+Y so that we can make use of it
very quickly.
27

27

00:01:33,730  -->  00:01:38,117
If I were to place 27 in A1 and then do
28

28

00:01:38,117  -->  00:01:44,180
ctrl+shift+Y, I get 3 in B1 because that's
the cube root.
29

29

00:01:45,380  -->  00:01:49,240
If I were to place 64 in A2, ctrl+shift+Y,
30

30

00:01:49,240  -->  00:01:53,220
I get 4, because that's the cube root of
64.
31

31

00:01:53,220  -->  00:01:54,500
That's where it stops getting easy.
32

32

00:01:54,500  -->  00:02:02,130
If we were to pick on some randomly large
number, 8756, ctrl+shitf+Y, the cube root
33

33

00:02:02,130  -->  00:02:05,830
is actually 20.61113546, depending on how
many
34

34

00:02:05,830  -->  00:02:08,010
decimal places you want to be accurate to.
35

35

00:02:09,090  -->  00:02:13,120
So that works quite happily, and we could
select four and proceed with
36

36

00:02:13,120  -->  00:02:16,080
the cube root of that, and it wrote that
in the next cell along.
37

37

00:02:16,080  -->  00:02:21,730
So everything works fine, until we try and
do something that the sub procedure is
38

38

00:02:21,730  -->  00:02:28,140
not capable of, such as working out the
cube root of a negative number.
39

39

00:02:28,140  -->  00:02:32,130
I've got negative four there, ctrl+shift+Y
and
40

40

00:02:32,130  -->  00:02:34,910
we hit our standard error message here.
41

41

00:02:34,910  -->  00:02:36,150
Runtime error five.
42

42

00:02:36,150  -->  00:02:39,870
This means absolutely nothing to anybody
using the program and
43

43

00:02:39,870  -->  00:02:43,720
they have a choice of ending the code or
debugging.
44

44

00:02:43,720  -->  00:02:45,390
Now they could click debug.
45

45

00:02:45,390  -->  00:02:46,990
It will drop them into the code and it
says the
46

46

00:02:46,990  -->  00:02:49,860
problems here but it doesn't actually
explain what the problem is.
47

47

00:02:50,890  -->  00:02:53,220
So let's reset that, go back to Excel.
48

48

00:02:53,220  -->  00:02:55,510
So it can't work out the cube root of a
49

49

00:02:55,510  -->  00:02:59,180
negative number or we'll get that awful
Excel error message.
50

50

00:02:59,180  -->  00:03:01,880
We'll also get the awful Excel error
message if
51

51

00:03:01,880  -->  00:03:04,360
we try to cube root a piece of text.
52

52

00:03:04,360  -->  00:03:09,150
My name for example, so Guy ctrl+shift+Y
and we hit the same problem.
53

53

00:03:09,150  -->  00:03:11,640
A different error number, 13 this time,
because
54

54

00:03:11,640  -->  00:03:13,950
we have, effectively we've got a different
error.
55

55

00:03:13,950  -->  00:03:15,920
Last time we were trying to cube root a
negative
56

56

00:03:15,920  -->  00:03:18,940
number, now we're trying to cube root a
piece of text.
57

57

00:03:18,940  -->  00:03:19,570
Okay.
58

58

00:03:19,570  -->  00:03:21,140
So I still have to end or debug.
59

59

00:03:21,140  -->  00:03:23,020
Well debug will just drop me against that
line
60

60

00:03:23,020  -->  00:03:26,310
there and takes me back into Excel and
nothing happens.
61

61

00:03:26,310  -->  00:03:29,920
Now we could continue with other problems
that could occur.
62

62

00:03:29,920  -->  00:03:32,220
We could perhaps be in the last cell on
the right
63

63

00:03:32,220  -->  00:03:35,040
hand side, and we wouldn't be able to move
to the right.
64

64

00:03:35,040  -->  00:03:38,220
That would also cause this procedure to
fall over.
65

65

00:03:38,220  -->  00:03:44,320
So what we need to do is either build into
our little routine here.
66

66

00:03:44,320  -->  00:03:47,680
All the possible errors we could think of
catching.
67

67

00:03:47,680  -->  00:03:50,140
So is X a negative number?
68

68

00:03:50,140  -->  00:03:52,210
Is X a piece of text?
69

69

00:03:52,210  -->  00:03:54,720
Is X the last cell on the right hand side?
70

70

00:03:54,720  -->  00:03:58,100
And any other possible variations that
would not work
71

71

00:03:58,100  -->  00:04:01,000
that would cause an error message to crop
up.
72

72

00:04:01,000  -->  00:04:03,720
Or, we go down the simple route and we
73

73

00:04:03,720  -->  00:04:07,140
capture all our messages and deal with
them on mass.
74

74

00:04:07,140  -->  00:04:10,030
So, how do we turn our little sub routine
here so
75

75

00:04:10,030  -->  00:04:14,140
that it can handle any error that might
occur when it runs?
76

76

00:04:14,140  -->  00:04:19,370
Well, right at the top after declaring the
sub routine, we
77

77

00:04:19,370  -->  00:04:24,734
need to add a new line that says on error
78

78

00:04:24,734  -->  00:04:30,510
go to, and then I'm gonna ask it to jump
to another part of the sub procedure.
79

79

00:04:30,510  -->  00:04:32,460
So that it knows where to go, I'm gonna
80

80

00:04:32,460  -->  00:04:34,962
give that new part of the sub procedure a
label.
81

81

00:04:34,962  -->  00:04:39,260
So I'm gonna say on error go to error
route.
82

82

00:04:39,260  -->  00:04:44,580
Now I can call my lumps of code anything I
like.
83

83

00:04:44,580  -->  00:04:47,170
So this errRoot is effectively a label
that
84

84

00:04:47,170  -->  00:04:50,130
refers to another part of the procedure
later on.
85

85

00:04:50,130  -->  00:04:52,380
Now when I do my return, you'll see we get
all
86

86

00:04:52,380  -->  00:04:55,000
the color coding that takes place and the
right title casing.
87

87

00:04:55,000  -->  00:04:56,720
So it's on error go to errRoot.
88

88

00:04:56,720  -->  00:05:02,180
Right then what I need to do further down
the procedure is firstly
89

89

00:05:02,180  -->  00:05:08,380
type when it's done all that current code
there to exit sub procedure.
90

90

00:05:08,380  -->  00:05:12,160
Otherwise, it will run straight into what
I'm gonna do next, which is the errRoot.
91

91

00:05:13,280  -->  00:05:17,540
I then need to use the errRoot as a label.
92

92

00:05:17,540  -->  00:05:20,040
And so that the VBA knows that it's a
label, I simply
93

93

00:05:20,040  -->  00:05:23,878
place a full colon at the end of the line
there, so errRoot.
94

94

00:05:23,878  -->  00:05:32,340
This is now the label that we will jump to
if we get an error.
95

95

00:05:32,340  -->  00:05:35,260
Notice how it left a line there
dramatically.
96

96

00:05:35,260  -->  00:05:40,940
And for simple's sake, we will just put
out a nice little message statement.
97

97

00:05:42,390  -->  00:05:44,429
Sorry an error occurred.
98

98

00:05:47,140  -->  00:05:49,140
Please try it again.
99

99

00:05:49,140  -->  00:05:51,590
Now in reality, I ought to be a little
nicer
100

100

00:05:51,590  -->  00:05:54,910
than that and make suggestions about what
could have gone wrong.
101

101

00:05:54,910  -->  00:05:56,570
How you used negative numbers?
102

102

00:05:56,570  -->  00:05:58,490
Have you tried to used text?
103

103

00:05:58,490  -->  00:05:59,480
Are you at the end of the sheet?
104

104

00:05:59,480  -->  00:06:00,780
Those kind of things.
105

105

00:06:00,780  -->  00:06:04,580
But for moment we are just going to say
sorry a error occurred.
106

106

00:06:04,580  -->  00:06:06,122
And then that will take us to the end to
the
107

107

00:06:06,122  -->  00:06:08,870
sub routine, so I don't need an exit at
this point.
108

108

00:06:08,870  -->  00:06:09,700
Let's see if that works.
109

109

00:06:11,720  -->  00:06:15,970
So, I take my cell with gain,
ctrl+shift+Y.
110

110

00:06:15,970  -->  00:06:17,100
Sorry an error occurred.
111

111

00:06:17,100  -->  00:06:17,860
Please try again.
112

112

00:06:19,290  -->  00:06:23,299
Let's put a negative number in,
ctrl+shift+Y.
113

113

00:06:24,850  -->  00:06:25,960
Sorry an error occurred.
114

114

00:06:25,960  -->  00:06:26,710
Please try again.
115

115

00:06:27,770  -->  00:06:31,660
If I put in an actual positive number,
that it
116

116

00:06:31,660  -->  00:06:35,790
can work out cube root four, I get the
result.
117

117

00:06:35,790  -->  00:06:37,700
So you can see the error trapping is
working.
118

118

00:06:37,700  -->  00:06:42,060
Now that's a fairly straightforward error
trap on Error GoTo
119

119

00:06:42,060  -->  00:06:46,410
errRoot which is a label for further down
in the code.
120

120

00:06:46,410  -->  00:06:48,730
Further down in the code, I've then put
errRoot with
121

121

00:06:48,730  -->  00:06:52,200
a colon so that VBA knows that that's a
label.
122

122

00:06:52,200  -->  00:06:55,120
And then between the factory of that colon
and
123

123

00:06:55,120  -->  00:06:57,750
End Sub, I put as much as I would like.
124

124

00:06:57,750  -->  00:07:02,540
It could be message boxes, it could be
input boxes, it could be logging the
125

125

00:07:02,540  -->  00:07:06,730
information somewhere else that you've got
a store of all the errors that take place.
126

126

00:07:06,730  -->  00:07:09,340
The important bit here at the end of the
normal
127

127

00:07:09,340  -->  00:07:12,470
code when there isn't an error is the exit
sub.
128

128

00:07:12,470  -->  00:07:17,113
So when these commands are executed,
[INAUDIBLE] and
129

129

00:07:17,113  -->  00:07:21,135
jumps out of the subroutine, it doesn't
continue
130

130

00:07:21,135  -->  00:07:22,760
down to then put out sorry an error
131

131

00:07:22,760  -->  00:07:25,290
occurred, when actually, one hasn't
occurred this time.
132

132

00:07:25,290  -->  00:07:30,595
So this is a nice, simple error trap that
really, you ought to add to all of your
133

133

00:07:30,595  -->  00:07:36,200
sub-procedures, just in case somebody put
something in that the code cannot execute.