The Three Types of Errors in Excel VBA

hello and welcome to this video on error handling in Excel VBA now this series of videos is based on a blog post that I wrote a few years ago and you'll find it on my website so you can see it here on the screen and I'll leave a link in the description you can also generally find it if you google VBA error handling it should be pretty near the top so what we're going to look at in this first video is the three types of errors in VBA because most people just see errors in VBA and they don't realize that just actually treat quite different types so I'm going to be explaining in this video what each of the types are and how we handle them so let's start off by looking at some code so this is very simple code here we're just declaring a variable has a long integer so we're just saying this variable is going to be a long integer now if we leave out syntax such as we forget the adds and then we press Enter you'll see that we got an error here and the error says expected end of statement now this is actually a syntax error even though it says compiler error it's actually a syntax error we can tell them because the line goes red and because it basically has to do the syntax of one line so we can click OK now if we put in the as and leave out the long then hit enter again you can see that we get another error and again this is a syntax error expected new or type name now the errors are very formal because this was the way things were done back in the 90s when VB was kind of invented and you can if you look at them first you might think too complicated but if you kind of look at them closer to giving you kind of a general idea of the problem so this is basically saying we should have a type named or we should have the keyword new now the same goes syntax errors are basically anytime we basically write out something and the syntax is wrong so a far loop should be something like this it should be for I equals 1 to 10 so if we leave out something like if we leave out the I for example and then we go down it says expected variable and you can see in this case actually highlighted the problem is based this in after the farmer that equals is we should have a variable now if we put in the I and then we delete they equals you can see that it says expected equals so a lot of the time is quite obvious what the problem is with the syntax if you know the syntax well now if you mean use in VBA kind of a lot you get tired of these synthetic you get tired of the syntax errors being reported like this because you have to click OK every time the box comes up so what you can do is you can do tools options and you can turn off auto syntax check now the beauty of this is that even if there's still an error so for example it's put the equals back it's okay now we delete the equals if there's still an error it turns red so that you know that doesn't her so these are basically syntax appears now the second type of error I like to call compilers so these are errors that happen they're not the syntax is fine but they happen over more than one line so for example if we have a forest statement the forest statement here is fine but we've actually forgot the next so every far statement must end with a next so that we we have code in between and the next statement is basically telling us where that block of code ends so if we leave out the next and we do a debug compile so this finds our compilers you can see that it says far without the next now it's highlighted the end sub because it's reached they're looking for the next now the same thing goes with all kinds of VBA lines like an if statement if we say if one then and we leave out the end if you can see that it says block if without an end if so any of these kind of statements that start and end a section if they're missing VBA will let us know now another thing is if we try and call a sub so say we call a sub like do something and if that sub doesn't exist we do a debug compile VBA will say sub or function not defined now often what happens is we can have this over to function like this and then we come along when we change it and then we do debug compile and VBA SS server function come not defined so this is a compile error I've shown in another video that if we actually run the code to here so we'll actually go and run the code it still finds the compile error so this is this can be a bit confusing because you might think well this is a runtime error but the code isn't actually really running at this point so what it's actually doing is basically just checking each module or each sub that it comes in to for compile errors and then it tries to run so the best thing to always do is use debug compile and the reason that we do that is because running only finds compilers that it actually comes across if there's a compile error in a different sub then running won't find it so for example imagine we put a an error in here we'll say let's actually say where we forgot to declare the variable so far I equals we'll say a actually just to make it more clear for a equals 1 to 10 and we say next hey now if we do a debug compile it tells us the variable has not been defined so a has not been defined but if we don't want that so if we only run the show errors so and let's pretend we don't call this one it will actually run like so in step to that as she runs and so on and then we run it at the end and it doesn't come across that error so that's why we use depo compile because debug compile will find all the errors in our code so let's do it again debug compile and you can see that it found variable not defined okay so now we're looking at the third type of error and third type of error that we have is a runtime error now the difference between the runtime errors and the other two is that the runtime errors only happen when our application is running so an example of this is the type mismatch and this is the easiest way to understand it is to see it happening so normally in this sub what we're doing is we're just basically reading the value from a 1 you can see that the value in a 1 is 99 so let's step through the code we step down to the code to read in the value into our long integer and everything is fine we look we can just place the cursor over the variable and you can see as the value 99 so everything is good on or soft ends there's nothing wrong with this code but the problem is is that if someone comes along and changes the value to a string for example and then we go back and we try to run our code so I'm just going to run straight to the code you can see that we got a type mismatch error so you can see the difference between the runtime errors and the other errors are that the runtime errors are actually caused by something external to our code now a second example is we're reading a file and what happens in real life when you read files is that the names can change all the time someone can change the folder and so on now in this case we run the code this file doesn't exist by the way and you can see couldn't find an name of the file is it possible it was not removed so again this is another runtime error and the reason that it's wrong time is cause it's external and it only happens when the code is running now when a runtime error happens if you want to fix it you just press debug so just click on the debug button and it will stop on the line with the error and then you can investigate and see what the problem is so when we when we talk about runtime errors we've got two types of errors we've got expected errors and unexpected errors so you could say that the read file is that when we want going to be the files and expected error so an expected error is an error that we've written code to handle and an unexpected error is where we haven't and code to handle that specific error and this is where the error handling code comes in so when you see an error go to that code is for errors that were not expecting so in the case here what we're actually going to do is we'd say something like if they're so their checks actually if the file name is valid so if the file name and we'll just say equals then it's an error and we'd have like a message box and we'd say a pair please check the file and then we might exit the sub and then we'd end if so we did exit the soap or something like this so let's run this code and it says error please check the file now the beauty of this code is that because we can we were doing were handling the error we can make the message specific to our application and we and the reason that it's really good as well is that if you make the the if we really specify what the problem is it means if we if someone comes along and changes the folder and we come back and run our code and is a problem it'll tell us exactly what we have to do to fix it basically just ensure that we have the correct file name now in the case of the type mismatch we might not want to basically check for each cell now we can put something in to do but there's no really easy way to be checking each cell as it was thought to go down we just expect that it will work and so in the case where it doesn't work what we what we use is the error handling code which is an error go to and so if an error happens this will truly air so just give you an example of this and we'll be going to it more in later videos so on error go to eh and then eh is our error handler and we have message box and we'll just put out the description all right actually error I just put out the description and then if everything is okay we just basically go to exit soap so if everything is okay we can call this one done we're just going to exit the soap but if there's an error it will actually come to here so let's step to the cord and this should be let me just put in the colon here so let's step to the cord and it's going to do this one this gives us a runtime error and you can see it jump down to the error handler and we put out the error and it's type mismatch so you can see that the two things here in the read file or expecting that error could happen at some stage I'm a writing code specifically for it in the case of the type mismatch we don't expect that an error is gonna happen it should run all the time so if on the off chance there is an error we put in some error handling code to deal with it and in the further videos in this series I'm going to be looking at the error handling code and showing you what exactly we can do thanks for watching this video I hope you found it useful if you would let me get notified when my new videos are published then please click on the subscribe button and then click on the bell icon beside it now if you'd like some more free excel vba resources then check out my website Excel macro mastery comm there are major articles on all the major areas of Excel VBA each article has an easy-to-navigate table of contents as well as a quick guide that allows you to easily find the syntax you need and there's tons of coding examples that you can copy and use in your own macros you'll also find techniques that are not available anywhere else I also have a VBA tutorial and in this tutorial there's lots of activities and solutions so that you can try them all for yourself and it's all absolutely free so that's all for me and I hope to see you on my next video

Channel: Excel Macro Mastery

Views: 15,287

Keywords: vba, excel, excel vba, microsoft excel (software), error handling, error in excel, excel vba error handling, vba error handling, visual basic for applications

Id: gSHjyN6JPcY

Length: 12min 33sec (753 seconds)

Published: Thu Nov 28 2019