When executing Visual Basic code, the chances are quite high that 2 2
you will have at some point in the past triggered an error. 3 3
Chances are, as you've been experimenting during 4 4
this title, that you will have created errors. 5 5
And effectively there are three types of error. 6 6
There are syntax errors where you've effectively typed something wrong. 7 7
Those errors are fairly easy to spot. 8 8
Because when you try to execute the code, something goes wrong. 9 9
You go into the code, you spot what's wrong and you fix it. 10 10
There are then two other types of error. 11 11
Runtime errors and books, which are programming errors. 12 12
So they fall into two different counts. 13 13
Let's have a look at how we can stop runtime errors. 14 14
Well, let's not say stop. 15 15
How we can catch an Excel runtime error, which is completely unhelpful. 16 16
And usually unintelligible, to the normal user, and 17 17
replace it with a, much more user friendly error. 18 18
So if we use the error trap file, available in your working 19 19
folder, you'll find this has already a little snippet of code, a sub 20 20
procedure called cube root, that will work out the cube root of 21 21
the active cell, and place that result in the cell to the right. 22 22
So we can see here, X takes the value of the active cell. 23 23
Y then works out the cube root of X, moves to the right, so 24 24
one cell to the right, and then places the value of Y in that cell. 25 25
I've also assigned a keyboard shortcut to it, which is 26 26
ctrl+shift+Y so that we can make use of it very quickly. 27 27
If I were to place 27 in A1 and then do 28 28
ctrl+shift+Y, I get 3 in B1 because that's the cube root. 29 29
If I were to place 64 in A2, ctrl+shift+Y, 30 30
I get 4, because that's the cube root of 64. 31 31
That's where it stops getting easy. 32 32
If we were to pick on some randomly large number, 8756, ctrl+shitf+Y, the cube root 33 33
is actually 20.61113546, depending on how many 34 34
decimal places you want to be accurate to. 35 35
So that works quite happily, and we could select four and proceed with 36 36
the cube root of that, and it wrote that in the next cell along. 37 37
So everything works fine, until we try and do something that the sub procedure is 38 38
not capable of, such as working out the cube root of a negative number. 39 39
I've got negative four there, ctrl+shift+Y and 40 40
we hit our standard error message here. 41 41
Runtime error five. 42 42
This means absolutely nothing to anybody using the program and 43 43
they have a choice of ending the code or debugging. 44 44
Now they could click debug. 45 45
It will drop them into the code and it says the 46 46
problems here but it doesn't actually explain what the problem is. 47 47
So let's reset that, go back to Excel. 48 48
So it can't work out the cube root of a 49 49
negative number or we'll get that awful Excel error message. 50 50
We'll also get the awful Excel error message if 51 51
we try to cube root a piece of text. 52 52
My name for example, so Guy ctrl+shift+Y and we hit the same problem. 53 53
A different error number, 13 this time, because 54 54
we have, effectively we've got a different error. 55 55
Last time we were trying to cube root a negative 56 56
number, now we're trying to cube root a piece of text. 57 57
Okay. 58 58
So I still have to end or debug. 59 59
Well debug will just drop me against that line 60 60
there and takes me back into Excel and nothing happens. 61 61
Now we could continue with other problems that could occur. 62 62
We could perhaps be in the last cell on the right 63 63
hand side, and we wouldn't be able to move to the right. 64 64
That would also cause this procedure to fall over. 65 65
So what we need to do is either build into our little routine here. 66 66
All the possible errors we could think of catching. 67 67
So is X a negative number? 68 68
Is X a piece of text? 69 69
Is X the last cell on the right hand side? 70 70
And any other possible variations that would not work 71 71
that would cause an error message to crop up. 72 72
Or, we go down the simple route and we 73 73
capture all our messages and deal with them on mass. 74 74
So, how do we turn our little sub routine here so 75 75
that it can handle any error that might occur when it runs? 76 76
Well, right at the top after declaring the sub routine, we 77 77
need to add a new line that says on error 78 78
go to, and then I'm gonna ask it to jump to another part of the sub procedure. 79 79
So that it knows where to go, I'm gonna 80 80
give that new part of the sub procedure a label. 81 81
So I'm gonna say on error go to error route. 82 82
Now I can call my lumps of code anything I like. 83 83
So this errRoot is effectively a label that 84 84
refers to another part of the procedure later on. 85 85
Now when I do my return, you'll see we get all 86 86
the color coding that takes place and the right title casing. 87 87
So it's on error go to errRoot. 88 88
Right then what I need to do further down the procedure is firstly 89 89
type when it's done all that current code there to exit sub procedure. 90 90
Otherwise, it will run straight into what I'm gonna do next, which is the errRoot. 91 91
I then need to use the errRoot as a label. 92 92
And so that the VBA knows that it's a label, I simply 93 93
place a full colon at the end of the line there, so errRoot. 94 94
This is now the label that we will jump to if we get an error. 95 95
Notice how it left a line there dramatically. 96 96
And for simple's sake, we will just put out a nice little message statement. 97 97
Sorry an error occurred. 98 98
Please try it again. 99 99
Now in reality, I ought to be a little nicer 100 100
than that and make suggestions about what could have gone wrong. 101 101
How you used negative numbers? 102 102
Have you tried to used text? 103 103
Are you at the end of the sheet? 104 104
Those kind of things. 105 105
But for moment we are just going to say sorry a error occurred. 106 106
And then that will take us to the end to the 107 107
sub routine, so I don't need an exit at this point. 108 108
Let's see if that works. 109 109
So, I take my cell with gain, ctrl+shift+Y. 110 110
Sorry an error occurred. 111 111
Please try again. 112 112
Let's put a negative number in, ctrl+shift+Y. 113 113
Sorry an error occurred. 114 114
Please try again. 115 115
If I put in an actual positive number, that it 116 116
can work out cube root four, I get the result. 117 117
So you can see the error trapping is working. 118 118
Now that's a fairly straightforward error trap on Error GoTo 119 119
errRoot which is a label for further down in the code. 120 120
Further down in the code, I've then put errRoot with 121 121
a colon so that VBA knows that that's a label. 122 122
And then between the factory of that colon and 123 123
End Sub, I put as much as I would like. 124 124
It could be message boxes, it could be input boxes, it could be logging the 125 125
information somewhere else that you've got a store of all the errors that take place. 126 126
The important bit here at the end of the normal 127 127
code when there isn't an error is the exit sub. 128 128
So when these commands are executed, [INAUDIBLE] and 129 129
jumps out of the subroutine, it doesn't continue 130 130
down to then put out sorry an error 131 131
occurred, when actually, one hasn't occurred this time. 132 132
So this is a nice, simple error trap that really, you ought to add to all of your 133 133
sub-procedures, just in case somebody put something in that the code cannot execute.