Excel VBA Introduction Part 3 - What to do When Things Go Wrong (Errors and Debugging)

welcome to this was our 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 well so talked about how you can step through your code line by line how you can set breakpoints 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 bit of experience when I say there are many things that can go wrong when you're when you're working with VBA but let me 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 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 you revolve that you can pretend something wrong so for instance if I remove this closed parenthesis on one line of code as soon as I try to 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 is turned red in the background and I've also got a dialog box which appears that tells me I've made what's referred to as a compile error 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 is highlighted the piece of code where it thinks that lists a separator or close bracket should appear if I click OK simplex R is a really easy to fix you simply have to modify the grammar so that your sentence make sense so if I pop my closed round bracket back in again click on a different line to confirm it and as my syntax are affixed 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 is expecting the list separator or close bracket it's highlighted the word created in my 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 up 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 OK and I'm going to fix the problem that I've deliberately created click on under the lines 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 VBA editor choosing options and then unchecking the box that says auto syntax check once I've done that if I click OK I can still make syntax errors and I can still get my syntax errors highlighted for me if I'd 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 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 you stand out for you so personal preference as I say is not to have the the syntax error message dialog box pop-up I find it gets in the way I think it's more than sufficient tap a line highlighted in red to indicate that I've made a mistake there 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 subroutine it's basically Excel VBA a way of sense checking the lines of code that you've written so here's an example 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 is not highlighted as a syntax error so syntactical occur automatically 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 subroutine what's going to happen is I'm going to get a compile error message and compile errors as some of the most useful ones because they highlight almost exactly where the problem is it tells me that that key word has not been recognized or civil function not defined in VBA speak if I click OK I can fix the problem by simply retyping the word as range you might also notice that this first line of your subroutine is highlighted in yellow indicating that is 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 is a little word break in square brackets usually the best policy is to reset your subroutine before you attempt to do it again so 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 subroutine compile errors crop up immediately before your subroutine runs so it's possible to highlight compile errors without choosing to run a subroutine you can do it by if I get 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 in sense check every line that I've written without attempting to run anything so I do that again I'll get exactly the same message that I saw earlier compile error Sybil function not defined I have - 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 to actually give you more useful error messages as we try to learn and and compile your projects so for instance without this technique turned on I can make mistakes such as misspell the names of functions such as a date that's a cover take whether ye I can also make spelling mistakes in in references to constants such as color constants so again if I misspelled this in some way I can't spell turquoise or the best of time 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 compile it first of all I don't get any compile errors if I try to run my subroutine I'll find that it actually works it's performed a job so it should have MIT put the date in cell b2 and it should have changed the background color resulted to pale turquoise but if I go back to my to excel itself I'll find that first of all the date doesn't appear in cell b2 instead of pale turquoise 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 if I go back to the visual basic editor and before the subroutine that I've written at the very very top of the entire module I'm going to write these two words option explicit now these two key words aren't actually designed for this purpose option explicit is designed for working with variables which is a subject for a later 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 that 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 that the message that you actually see so if I click OK 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 you should pick up on another keyword that I've misspelled so again it says variable not defined it recognize a keyword and I haven't declared a variable called RGB pale Turk was so I click OK if I can I'll spell turquoise correctly there we go and this time when I debug 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 OK and now the next time you create a new module you'll find that it automatically has the words option to explicit written in at the top might be true from now on in this 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 encounter I think so let's have a quick look at 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 a Zed Zed Zed 1 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 Rd in the latest versions of Excel so if I try to compile this project to show that the compilation doesn't pick up on this type of error if I compile my project everything passes compilation is not until I run my subroutine I'm going to generate this error so I choose to play it or run my subroutine I get to a fairly standard run time error message dialog 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 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 out as well as to what I could do my subroutine is 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 subroutine 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 my module if I simply click play or continue that will run the subroutine all the way through to the end hopefully without any further errors I'll 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 to 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 a mode you can access when you've caused a runtime error so I make another one of my most common mistakes I think misspelling the word color in the English way rather than the American Way if I try to run this code now I'm gonna 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 there's clearly a problem with this particular line I need to fix that problem by removing spelling of 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 break mode is actually a particularly useful tool because it allows you to work through a program step by step you actually force a subroutine to go into break mode at the instant you start running it to go into break mode make sure that you've selected something inside the subroutine you want to to debug and they 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 subroutine 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 into 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 worksheet stopped ad 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 that cell a1 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 a runtime error mixing with my arrow type there for instance if I enter 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 taking 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 subroutine 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 and sub or hits the reset button but make sure that you're not still in brake 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 a small part that you might be interested in stepping through so to help you with that you can set something called a break point you can set breakpoints 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 on the left hand side so if I click here in the gray bar you can see a breakpoint 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 breakpoint you can simply click back on there the same icon the other way to set a breakpoint 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 can also just press the f9 key as well so toggle breakpoint what's in a non 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 there the main part of it the screen if I drag it up to the top of the screen s it up there at the top I can then set breakpoints with a little hand symbol toggle on and off and I can also choose to step into a program we use the step through a program using the step into button here or pressing f8 so let's set a breakpoint on my first line which tries to change a color 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 subroutine so I click on my little green triangle or press f5 I'll find that the entire program is run all the way up to this point but this stage now is paused and is in brake mode so from now on I can choose to use the f8 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 subroutine finally I'm just going to turn off my breakpoints 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 ww-why Zelko UK

Channel: WiseOwlTutorials

Views: 343,626

Rating: 4.9486651 out of 5

Keywords: excel, vba, visual basic, macros, error, syntax, compile, runtime, wise owl

Id: DlqKfYTgc9Q

Length: 18min 19sec (1099 seconds)

Published: Fri May 10 2013