How to Solve a VBA Error in Less Than a Minute

in this video i'm going to show you how to solve a vba error in less than a minute and i'm going to do it using some known and some little-known vba techniques if you spend a lot of time fixing errors then the methods in this video will save you a ton of time now let me know in the comments what you think of these medics and if you've used them or plan to use the medics in your code if you'd like a copy of the workbook that i use in this video then you'll find a link in the description below if you liked the video then please click on the like button and if you want to get notified of my upcoming videos then click on the subscribe button below and the bell icon beside it so let's go ahead and get started there are two things we must do when it comes to locating an error in the code the first is to find the line with the error and the second is to find the part of the line that is causing the error let's look at finding the line first when we run the code in this sub you see that we get the error message displayed on a dialog now if we click on the debug button it will stop us on that line with the error if your code has error handling then it won't stop on the line with the error so let's add error handling to this soap we add on air go to eh which will send us to the eh section if an error occurs we then add the eh section and in this we're simply going to display an error when the error occurs before this line we add an exit subline so that we will only enter the error handling section if there is an actual error now if you're not sure why we're using error handling then check out my video vba error handling explained in plain english now if we run the code you will see that it displays the error but it doesn't show us the line where the error is if we want the code to stop and align with the error we have to make a small change in the editor settings we go to tools options and then what we do is we select general and then we click on break on all errors now we only change this section when we are debugging the code and trying to locate errors once we are finished we should select the setting break on unhandled errors now let's run the code again after updating this setting and you can see it ignores the error handling and stops on the line with the error so now that we know how to find the line with the error let's look at how we find the error on the actual line when the line of code with the error is simple the error will often be obvious in many cases though the line may not be so simple and it may not be obvious which part of the line is causing the error let's look now at how to find the error on the line when it is not so obvious in this example we are reading through the data and performing a calculation when the code runs we get an error now when the error appears we click on debug to stop the code on the line as we have seen already so it's not obvious which part of this line is causing the error so the first thing we do is to open the watch window and we do this by selecting view and then watch window the shortcut keys are all v plus h the watch window can be docked or undocked by double clicking on the title bar so let's examine this line we start by looking at the right hand side of the line we highlight it and then drop it into the watch window now you can see it says type mismatch in the value section what we want to do is divide and conquer we take half the line and check for the error when we put it in the watch window you can see that the value isn't a tight mismatch error so now we drop the second part of the line in the watch window this doesn't give a tight mismatch error either but we can see that the value is not a number so this is obviously a problem as you're trying to multiply this value now we can go back to the full part in the watch window and replace the second part with 54a and it will still give a tight mismatch error as we would have expected we can now change 54 a to 54 and you can see that type mismatch error is gone so now we know the part of the line that's causing the errors and sometimes at this point we have enough information to fix the errors but often we need more information especially if the lines are more complex so let's have a look at how we can easily locate the cells and worksheet where the error is occurring in excel vba we often get errors that are related to the range when we find the error we also need to know the seller arranged where the problem is so it's very easy to find this we take the range with the invalid value and simply add address in the watch window the address property will display the address of any range and we can see the address here is c5 we can use address in anything that evaluates to arrange in our code for example we can take the range with current region and place this in the watch window we can then use address to see what cells are in the range address is one of the most useful properties in excel vba when it comes to finding and fixing errors when checking an error it is often a good idea to check which worksheet the range belongs to this ensures that we are referencing the correct worksheet it's very easy to find a worksheet from the range we simply use the parent property the parent property of any range is the worksheet that it belongs to and we can then use the name property of the worksheet to give us the name so let's do this with our range we add the parent property and then we add name you can see now the name is displayed in the watch window so in some cases we may want to know the workbook that the range belongs to and the workbook is essentially the parent of the worksheet and we simply add an extra parent to the line in the watch window and this gives us the name of the workbook we can do this with any range to get the workbook name let's look at an example where we put everything we learned to use in this example we are using this data on the country worksheet the code reads this data and performs a calculation but the user is getting an error our job is to find the error and to see what is causing it when we run the application we can see it is not stopping on the line with the error our first step is to change the error handling settings we switch to visual basic by pressing alt f11 and then we go to tools options and the general tab and then we set error handling to break on all errors let's run the application again when the vba error message dialog appears we can click on the bug to go to the line where the error is occurring now we're at the line with the error the next step is to examine the line with the help of the watch window so let me start breaking down the line with the error we drag the right hand side of the equals to the watch window we can see that division by zero shows in the watch window next we drop the boxes per container in the watch window and that's fine it isn't zero so we go to the part in the parenthesis when we drop this into the watch window we can see the error is here next we drop volume into the watch window and we see that this is the problem because volume is zero as you get better at vba you will often realize what the error is before you have completed all these steps in this case we could have easily checked volume first this is absolutely fine the key is to start with the steps and if you get a sudden insight along the way then you can skip the next steps and check that out back to this error we know that volume is zero but where is volume coming from if we go back to the code we can see that volume is a range so we take this range and put it into the watch window we then add the address property to find the range we can see that the range is cell c7 now we want to find out the worksheet of the range so that we are sure we are reading from the correct worksheet we use parent and then we use name of the range and we can see the worksheet is country so if we go to the country worksheet we can see that the problem is at cell c7 where the value is zero in this video we looked at how to locate and solve a vba error in under a minute let's have a look at these steps again so number one if your code uses error handling then turn it off into visual basic editor so number two run the code and when the error dialog appears click on the debug button this will stop you on the line with the code so now number three let's examine the line using the watch window until you find the part that has the error if this error is related to a range then use address and parent properties to find the range worksheet and workbook please let me know in the comments if you've used or plan to use these methods in your own code if you would like a copy of the workbook that i used in this video you will find it in the link in the description below the video if you liked the video then please click on the like button and if you want to get notified of my upcoming videos then click on the subscribe button below i hope to see you on the next video

Channel: Excel Macro Mastery

Views: 19,797

Rating: 4.9712644 out of 5

Keywords: VBA Error, Excel VBA Error, VBA Find Error, VBA Locate Error, VBA Runtime Error, VBA Error Watch Window, VBA Error Solve

Id: 79i0zeBE1ec

Length: 9min 20sec (560 seconds)

Published: Thu Dec 17 2020