Reduce VBA Errors by 90% (with this little-known Method)

in this video i'm going to show you a little known technique that will vastly reduce the number of errors in your vba application now this technique has many benefits it is simple to use it is a very effective way of finding errors and if that wasn't enough it won't impact the speed of your application yet almost nobody uses this technique in vba even though you can try it for yourself and see almost instant results so let's get started by looking at the three types of errors in excel vba if you liked this video then please click on the like button below and if you want to get notified of my upcoming videos then please click on the subscribe button and the bell icon beside it now if you want to try out this method for yourself then you can download the freeze demonstration workbook from the description below the video and this contains some demonstrations of exactly how this method works so you can see it for yourself in real time the most important thing when dealing with errors in vba is that we should understand what exactly they are so we can classify errors into three types we've got syntax errors compiler errors and runtime errors so let's have a quick look at each so that we can understand what they are and how we should deal with them so the syntax error is concerned with one line it's simply an error on one line where the syntax is not proper vba syntax now if we write something like dim x as long it is a standard vba line if we leave out the type long which is expected and then return we will get a syntax error now confusingly it says compile error but you can ignore that this is a syntax error now another way this would happen is if we had an if statement we could say if x is greater than zero but in this case we leave out the then keyword and this gives us the error expected then or go to so let's have a look at one more this time we're looking at the for loop if we use the for loop for i equals 1 to 10 but we leave out the two keyword it will tell us expected 2. so these are syntax errors and syntax errors are just concerned with one line so vba detects syntax errors by default so we can turn this off by going to tools options and auto syntax check if we turn it off then the line will turn red if there is a syntax error but we won't get an error message explaining what it is the second type of error is the compiler error compiler errors are concerned with more than one line of code so here is one example the if statement doesn't have a corresponding endif so this would give a compiler error and how we find compiler errors is by using debug compile in the menu let's select debug compile and this gives us the message block if without end if so if we put our end if here and we do a debug compile again you'll see that there's no error so when there's no error nothing happens now another example of this is if we have option explicit up at the top of our module and we don't declare the variable so we do a debug compile and you can see that it says the variable not defined and it actually highlighted x in this case so other examples of compiler errors are a sober function being called but it doesn't exist incorrect parameters being passed to a sovereign function select statement without an end select far without next and so on you can see that the compiler is very useful for finding errors that we may not notice in our code doing a debug compile before we run our code helps ensure that there are no syntax or compile errors in the code now that we understand what a syntax and compile error is let's have a look at what runtime errors are runtime errors are errors that only happen when the code is running if we do a debug compile here we'll find that there's no errors in this code however if we run the code we'll get a type mismatch error the reason for this is that we're trying to put text into a variable which can only store numbers so the variable amount can only store a type of long integer so let's have a look at a second example in this example we're creating a collection and adding an apple to the collection and further down into code we try to access the item at position 2. now the problem is we've only got one item in the collection so when we run this code you'll see we get a subscript out of range error and this means that we're trying to access an item in an array a collection etc but it doesn't exist and so let's have a look at one final example in this case what we've done is we've declared a variable as a workbook but we haven't actually assigned it to any workbook anywhere and now we're trying to print out the name and this doesn't make sense because the workbook hasn't been set to anything when we run the code you can see the error we get is object variable or block variable not set so what that means in simple english is that we should set the workbook variable to something so these are the runtime errors that produce error messages there's a second type of error that happens at runtime and we call this a logic error this is when your application doesn't do what it's supposed to do so for example here we have a function that doubles a value that it is given as input when we run the code you will see the result we get is 100 from the input of 50. now if we accidentally change the 2 to a 4 for example we're now getting an incorrect result but vba cannot help us detect the error because it has no way of knowing what we are trying to do so this leads to two major problems we may not discover the error for a long time and when we discover there is an error we may waste hours trying to find out where the problem is so let's take a look at another runtime example in this case we have the outline of a typical vba application reading the data updating the data and finally writing it out now let's run the code and see what happens it writes out all the numbers less than 500. now imagine someone makes a change to this code unknown to us we don't know that they've done it and now we run the code there is no results but where is the problem is it in read data update data or write data we have no idea and we have to debug through the code to figure out the problem now in this case the code is quite simple but in real time applications it could be much more complex now wouldn't it be nice if there was some way to detect these type of errors as soon as they occur well there is and this is where the magic of the debug assert function comes into play so now let's take a look at the technique that we're going to use to find these hidden errors in our code and what i'm referring to is using debug assert now you might have come across debug assert before but you probably have no idea how powerful it is what debugger start essentially does is it takes a condition and if it evaluates to false then debugger cert pauses the code on the line as you can see here so let's take a look at some simple examples in this example we're using debug assert to assert that x is less than 10. we are simply saying x less than 10 should be true if it is true the code will continue on as normal but if it is false the code will stop on this line so if we run this code you will see that they printed out 5 and everything was ok but if we change x to 12 when we run the code debug assert will stop the code on this line and so this is the real power of debug assert the idea is that like having a series of checkpoints alter out your code and it makes it very hard for variables to move to your code if they have incorrect values let's have a look at another example so in this case what we're doing is setting the amount to be 12 and we have a debug assert that says the month must be between 1 and 12 which makes perfect sense at any point our mod reaches here and it is not in the valid range then what will happen is the code will stop so let's run our code and you can see that it says mont is 12 so everything is fine but imagine that an error happens in our code and we get the wrong value for month we could run the code without a debug assert but we don't notice the problem and the code gives us a value we don't know is wrong now we might know that we have problems in the code unless reported by the user and this may not happen for a couple of weeks time and then we've got to dig right down into the code to find it but using debug assert the code will tell us straight away we have a problem and it will also stop the code close to the problem so you can see that this makes it very difficult for our code to operate if there is invalid data in our variables so let's take a look at the application example that we had earlier on and i'm going to use debug assert to smoke out the errors remember this application that we looked at a bit earlier it was working fine and then for some reason it suddenly just stops displaying any results when there appears what we normally have to do is use the debugging tools and dig through the code to try and find out where the error is so let's look at the main sub in the code we've got read data update data and write data procedures and the error could be in any of them so let's update this code by adding debug asserts to it so now we run the code and see what happens straight away it stops here and says hold on the collection count is zero and we can see that the line right above is where the collection was emptied so to show how effective the assertions are let's put this line somewhere else in the code let's put it in before we return the collection from read data when we run the code now you see it stops us right where we've put the line of code so this means anytime the collection gets accidentally emptied will be notified straight away you can see from this example that assertions save us a considerable amount of wasted time when you're writing new code is when the errors are introduced and often they can go undetected for a long time so using assertions allow us to detect them very quickly now we see how useful assertions are let's look at some guidelines for using them a quick pause to tell you about the excel vba handbook course are you struggling to build vba applications do you find it difficult to get good information on how to create real-world vba code is it a struggle every time you try to create a vba application no matter how simple it is well the excel vba handbook course teaches how to build real-world excel vba applications from scratch unlike most courses you won't be overwhelmed with information and left to figure out how to put it all together instead you'll be taken step by step to 10 excel vba applications with every concept explained once you start work into vba applications you'll be amazed how quickly your vba skills increase so check out the vba handbook course at the excel vba handbook.com and the link can also be found in the description below the video when and how do we use debug assert so in this section i'm going to give you some very simple guidelines the when is important we only use debug assert when we are writing the code or when we are debugging it a lot of people get confused with this and think that debugging is some part of error handling when it's actually not when we give the code to other users we turn off debug assert or we remove the statements the last thing we want the user to see is the application pausing in the code now the big advantage of this is that we can add as many debug asserts as we like and it won't impact the application when we give it to the user so now where do we use debugger search in our code well these are the simple guidelines where you get a value from a sub or a function we test this using debug assert at the start of a sober function when we receive parameters we test them using debug assert and before you return values from a sub or a function we also test them now we don't use debugger server to handle errors that should be handled by error handling code so for example if a cell is empty we should have code to inform the user and prevent the application from crashing and we call this code error handling code however in the early stages of creating an application where you don't have error handling code it's perfectly fine to use debug asserts we can always change this later so apart from the guidelines you can pretty much use assertions anywhere you think might be useful if in doubt put them in because you can always remove them later one word of caution for assertions is don't mix them with your code so in this example we have a call to a function that is in a debugger search statement so we shouldn't do this our code should be completely separate from the debugger search statements we should be able to remove all the statements and it shouldn't have any impact on how our code runs one important point i made earlier is that we don't want debugger serve to be available when the user is running our application so what we do instead is we can either turn off debug assert or we can remove them from our code it makes much more sense to just turn them off because we can turn them back on when we want to update the code so how we do it is as follows we first of all create our own sub and we do it like this we type public sub and then we're going to give it the name debug assert all one word and then we take condition as the parameter and this condition is a boolean type so that means it's either true or false and now we're going to add the line debug assert and that's all we're simply doing here for the moment is passing on the condition to debug assert so let's see this working first of all we change the code where it has debug.assert to the name of our soap which is debug assert and as we run the code you can see it stops here one thing to keep in mind is that it's always going to stop within the soap but this is simple to get around we do control l to view the stack and the soap we want is always the second one from the top so we simply double click on this and this will go to the line where we're using debug assert now the beauty of having our own debugger search function is that we can turn it on and off very easily so we can do something like hash if debugging equals one then we're going to use debug assert when we use if with hash what it really means is that if we put code in here and debugging is not one then the code will not be used in other words it won't be loaded into memory and vba will just completely ignore it so let's explain this with an example say we do x equals 6. this gives us an error because x hasn't been defined but if we move this line with the error into the hash if statement the compiler will ignore it and we don't get an error and similarly if we debug through the code vba will simply skip over the lines here so how do we set debugging to one we simply go to tools the project properties and then we just put it in the bottom text box we just say debugging equals one so we can easily turn on and off the assertion by setting debugging to one if we want to turn it off we simply set the bug into zero or we remove it completely so when we go back to the code and run it you'll now see that the line stops because debugging is turned on so now let's go and turn debugging off when we run the code you can see that it ignored the debug assert lines so that's how easy it is to turn it on and off with our assertions so let's have a recap of debug assert so we can see it in very simple terms so what is debug assert debug assert is a simple statement very easy to add to our code and it simply evaluates to true or false so we can use this to easily identify errors in our code very close to where they happen this saves us considerable time as we're not looking through all our code and debugging trying to figure out where the error is and we also know that when we give the code to a user that is very unlikely that there's many errors in it and the beauty of debug assert is that there's no impact on the speed of our application because we're removing debug asserts when we give the code to the user so when should we use it we use debug assert when we're writing and testing code and then we remove or turn off assertions when you give the code to the user so what to avoid so don't use debug assert on the line with normal code so it should only be used on its own line where we're evaluating variables the idea is that at any point we can just delete a debug assert and it shouldn't affect your application in any way so where to use debug assert so the general guidelines are when you're getting a return value from a procedure to check parameters that are going to a procedure and to check a value before you return it from a procedure apart from this you can put them anywhere you think or anywhere you like because if they don't work you can just simply get rid of them at any point and as i said they don't have any impact on the code that you give to the user so i hope you enjoyed this video and if you could let me know in the comments if you've used debugger serve before or if you plan to use it in the future i recommend trying it in any new code that you're writing and you see how useful that it is now if you like this video please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button below hope to see you on the next video now if you want to try out this method for yourself then you can download the freeze demonstration workbook from the description below the video and this contains some demonstrations of exactly how this method works so you can see it for yourself in real time

Channel: Excel Macro Mastery

Views: 31,034

Rating: 4.9804468 out of 5

Keywords: Debug.assert, Vba assertion, Vba debug assert, excel vba, vba excel programming tutorial, microsoft excel tutorial, visual basic for applications, vba

Id: CT7XkPXKVFw

Length: 16min 59sec (1019 seconds)

Published: Wed Nov 04 2020