welcome to this wisel tutorial in this video we're going to cover errors and debugging in Excel VBA or what to do when things go wrong and trust me there's plenty of things that can go wrong when you're programming in VBA we'll start by looking at the various types of Errors you're likely to encounter in VBA and those are syntax errors compile errors and runtime errors we'll cover each of the different types tell you when they're likely to occur and what you can do to solve the problem when they do occur after we've done that we'll show you a few debugging techniques as as well so we talk about how you can step through your code line by line how you can set break points which allows you to run your code up to a certain stage and then how you can display and use the debug toolbar as well so let's get started so in this video we're going to show you some of the things that can go wrong when you're writing and running VBA code and I speak from bitter experience when I say there are many things that can go wrong when you're when you're working with VBA we're going to show you some of the most common things I think you'll encounter the first type of problem you're likely to encounter in VBA is called a syntax error and this is essentially an error in the grammar or the structure of an instruction that you've written so usually misplaced punctuation like double quotes or full stops Etc it's very easy to spot syntax errors because pretty much as soon as you make the mistake um you're informed that you that you've done something wrong so for instance if I remove this closed parenthesis on one line of code as soon as I try to move move away from that line of code it's highlighted for me in a couple of different ways that I've made a mistake first of all if you can see it the text has turned red in the background and I've also got a dialog box which appears that tells me I've made a what it refers to as a compile error um and it actually gives me a sensible suggestion in this case it says it expects a list separator or a closed bracket I don't know if you can also see it's highlight to the piece of code where it thinks that list separator or Clos bracket should appear if I click okay syntax errors are really easy to fix you simply have to modify the grammar so that your sentence makes sense so if I pop my Clos round bracket back in again click on a different line to confirm it and there's my syntax error fixed now it's actually very easy to confuse the syntax error message dialog box by making a different type of mistake in this example what I'm going to do is remove the set of double quotes after the cell reference and if I click on another line of code to confirm that I've entered that line I get the same dialog box appearing telling me it's Miss it's expecting a list separator or Clos bracket and it's highlighted the word created in my uh in my line of code it's clearly a syntax error because I've got the line of text highlighted in red but it's clearly picked out completely the wrong thing this time so although some of these some of these error messages can be useful the syntax error messages that can be generated very very easily confused I'm going to click okay and I'm going to fix the problem that I've deliberately created click on another line to confirm it my personal preference is to not display the dialog box if I make a syntax error you can actually switch those off by heading to the tools menu in the VB editor choosing options and then unchecking the box that says Auto syntax check once I've done that if I click okay I can still make syntax errors and I can still get my synta XEX eror is highlighted for me if I didn't make the same mistake again I can take away the double quotes click on another line of code and as long as you can see it it's highlighted in Red so I've clearly got a problem with that line from a previous video we mentioned that if you can't see uh red and green colors you can always head to the tools menu choose options choose editor format select the type of text you want to modify such as syntax error you can actually choose a different color to make it stand out for you so personal preference as I say is not to have the uh the syntax error message dialogue box popping up I find it gets in the way I think it's more than sufficient to have a line highlighted in red to indicate that I've made a mistake that needs to be fixed okay so the next level of error we can look at is something called a compile error compiling is something that happens for you automatically whenever you try to run a sub routine it's basically XL vba's way of sense checking the lines of code that you've written so here's an example of of a compile error if I accidentally or deliberately misspell the word range in one of my lines of code notice that when I click away from that line it's not highlighted as a syntax error so syntactically or grammatically that line does make sense I've got all the right words and all the right punctuation characters in the correct position it's just a misspelling of the word that is the problem so if I try to run this sub routine what's going to happen is I'm going to get a compile error message and compile errors some of the most useful ones because they highlight almost exactly where the problem is it tells me that that keyword has not been recognized or subil function not defined in VBA speak if I click okay I can fix the problem by simply retyping the word as range you might also notice that the first line of your sub routine is highlighted in yellow indicating that it's trying to run at the moment what you're in at this point is something called Break mode so you can see at the top of the screen there's a little word break in square brackets usually the best policy is to reset your sub routine before you attempt to do it again so if you hit the reset button this little blue square we could also head to the Run menu and choose reset from there as well and then you can attempt to run your program again now compile errors don't actually crop up at the point you run a sub routine compile errors crop up immediately before your sub routine runs so so it's possible to highlight compile errors without choosing to run a sub routine you can do it by if I again make the same mistake by misspelling range I can head to the debug menu and choose to compile my entire project so this will go through and sense check every line that I've written without attempting to run anything so when I do that again I'll get exactly the same message as I saw earlier compile error sub function not defined I have to click okay fix the problem before I can successfully run this program notice this time that your subroutine isn't in break mode so I don't have to hit the reset button before I can try to do anything else while we're talking about compile errors it's worthwhile mentioning another technique that you can use to make the compiler even more fussy than it currently is so it actually gives you more useful error messages as you try to run and and compile your projects so for instance without this technique turned on I can make mistakes such as misspell names of functions such as uh date I take take away the E I can also um make spelling mistakes in in references to to constants such as color constants so again if I if I misspell this in some way I can't spell turquoise at the best of times so I take away the the I there now what would happen normally if I try to run or compile my project if I if I choose to to compile it first of all I don't get any compile errors if I try to run my sub routine I'll find that it actually works it's performed a job so it should have made put the date in cell B2 and it should have changed the background color Asel to to pale turquoise but if I go back to my um to excel itself I'll find that first of all the date doesn't appear in lb2 instead of pale turquoise the background color of those cells is black now I can make my compiler pick up on these mistakes that I've made by adding a couple of extra keywords to the top of my module so if I go back to the Visual Basic editor and before the sub routine that I've written at the very very top of the entire module I'm going to write these two words option explain it now these two keywords aren't actually designed for this purpose option explicit is designed for working with variables which is a subject for aat video but we're going to use it at this point to help us pick up on the spelling mistakes that we've made so with option explicit turned on if I head to the debug menu and choose compile project it's going to pick up on the very first misspelled keyword that it finds so it doesn't recognize the word dat and I haven't declared a variable called that and as I say we'll talk about variables later on that's just to explain the the the message that you actually see so if I click okay I know that there's a problem with that specific keyword there if I spell that one correctly again I can now try to compile the project again and this time it should pick up on another keyword that I've misspelled so again it says variable not defined it doesn't recognize a keyword and I haven't declared a variable called RGB pale turquise so if I click okay if I can I'll spell turquoise correctly there we go and this time when I debur and compile my project everything passes the compilation personally I always choose to have option explicit at the top of a module and because I'm too lazy to type it in myself every time you can set up the Visual Basic Editor to add those words for you automatically so to do that head to the tools menu and choose options and on the dialog box which appears find the box which says require variable declaration check that box click okay and now the next time you create a new module you'll find that it automatically has the words option explicit written in at the top and that'll be true from now on in this uh this Visual Basic editor the final type of error we're going to look at is something called a runtime error and as the name suggests this is a problem that occurs when your program is actually running so you can generate runtime errors in a huge vast variety of different ways this is probably the most common type of error you're likely to ENC counter I think so let's have a quick look one reasonably easy way to generate a runtime error is to try to refer to a cell which doesn't actually exist so I'm going to try to refer to a cell whose reference is ZZ Z1 and I'm pretty certain there aren't any cells with that cell reference in my workbook the last cell you can refer to in the last column is XF D in the latest versions of excel so if I try to compile this project just show you that the compilation doesn't pick up on this type of error if I compile my project everything passes compilation it's not until I run my sub routine that I'm going to generate this error so if I choose to play it or run my sub routine I get a fairly standard runtime error message dialogue box so it says method range of object Global failed that's not necessarily a particularly useful bit of information what is a bit more useful is the ability to click this button here called debug now what this will do is take you into break mode as we saw earlier on and a line of your code will be highlighted in yellow but fortunately with this type of message a runtime error the line that's highlighted in yellow is the one that has caused the problem to occur so the um the the debug button is the most sensible one to click on when you're when you've got a runtime error message dialog box so this is a nice easy and obvious one to solve if I change the cell reference back to B1 I've got several choices at this point now as as to what I can do my sub rutin has actually successfully executed all of the lines of code up to this point in fact if I go back into Excel itself quickly you can see that I've got a new worksheet and the first three titles have been added to cells so back in the VB editor if I hit the reset button now at this point I'd be left with a half finished sub routine what I can do instead is simply choose to run it from its current position so where the yellow arrow is in the left hand side of my uh my module if I simply click play or continue that will run the sub routine all the way through to the end hopefully without any further errors if I go back to excel quickly I'll see that it's completed so we've seen the three main levels of errors that can occur to you when you're working with VBA code we've seen syntax errors which occur when you're writing your code we've seen compile errors which happen when the project is compiled and we've seen runtime errors which happen when your program is actually running so what we'd like to do next is show you a couple of other useful techniques you can use to help you to work out when things are going wrong in your programs so earlier on we talked to you about something called Break mode which is uh a mode you can access when you've caused a runtime error so if I make another one of my most common mistakes I think misspelling the word color in the uh the English way rather than the the American way if I try to run this code now I'm going to get a runtime error telling me that something has gone wrong if I click the debug button that's the point at which I enter the break mode so at this point I can see that there's clearly a problem with this particular line I need to fix that problem by removing and spelling the word color incorrectly and then running that one through to the end so as I say we can click the continue button to carry on running that one now breake mode is actually a particularly useful tool because it allows you to work through a program step by step you can actually force a subroutine to go into break mode this the instant you start running it to go into break mode make sure that you've selected something inside the sub routine you want to to debug and then you can either head to the debug menu and choose this option called step into or you can simply just press the f8 key on your keyboard if I choose this option you'll see that the first line of your sub routine is highlighted in yellow so what this means is that this line has not yet been executed but I can step through my program Now by either carrying on clicking debug step in two or much more conveniently pressing the f8 key so as I press f8 it moves to the next line of code and it will execute that line when I press f8 again now it can also be quite useful at this point to watch what is happening in Excel itself so if you restore down your window your main Visual Basic editor window you can then change the size of your screen to expose excel in the background and if I then carry on pressing f8 watch what happens when I press f8 on worksheets do add you should find that a new worksheet appears in Excel and there it is if I press f8 on the line that changes the value of cell A1 you should see see that cell a1's value will change and so on and so on and so on as you work through now if you reach a line in which there is likely to be a syntax error sorry a a runtime error makes my eror types there for instance if I enter a a cell reference which I know doesn't exist when I press f8 on this line it will cause the runtime error as usual so I can click debug here that tell back to the line that I've just made a mistake on fix the problem and then carry on pressing f8 to step through to the end of the sub routine it's very important that you don't remain in break mode so if you're using the f8 key to step through make sure that you either finally press f8 while you're on the words end sub or hit the reset button but make sure that you're not still in break mode when you finished debugging your program when you're stepping through very large programs it can be quite annoying to have to step through many many many lines of code to get to the small part that you might be interested in stepping through so to help you with that you can set something called a breako you can set break points in a couple of different ways first of all the quickest and easiest although least reliable I think is to Simply click next to a line of code in this gray bar which appears at the left hand side so if I click here in the gray bar you can see a break point appears it got it gets a little sort of reddish brown Dot and the entire line will turn the same reddish brown color in the background to take away a breako you can simply click back on the the same icon the other way to set a break point is to click somewhere on the line you're interested in and you can then head to the debug menu and choose toggle breakpoint you could also just press the F9 key as well so toggle break point will turn it on I can press F9 to turn it off all these techniques by the way are also available from an extra toolbar if I right click at the top of the screen I can choose the debug toolbar and that appears floating over the the main part of the the screen if I drag it up to the top of the screen Nest it up there at the top I can then set break points with a little hand symbol toggle on and off and I can also choose to step into a program using step through a program using the step into button here or pressing f8 so let set a break point on my first line which tries to change a color and what I'd like to do is run through the entire program now up to that point so I can do that by simply choosing to run my sub routine so if I click on my little green triangle or press F5 I'll find that the entire program has run all the way up to this point but at this stage now it's paused and is in break mode so from now on I can choose to use the FH key to step through or again click on my step into button on the debug toolbar so I can press f8 to step through all the way through to the end of the sub routine finally I'm just going to turn off my break point to make sure that my program will run properly the next time I do it if you've enjoyed this training video you can find many more online training resources at www.y l.c.