1 00:00:00,730 --> 00:00:05,680 In this video we're going to talk a little bit about the debugging methods and debugging tools you might 2 00:00:05,680 --> 00:00:09,510 use to help identify errors in your code and address them. 3 00:00:09,520 --> 00:00:17,230 And so I have here an Excel file that we built in our form controls and form code videos early on when 4 00:00:17,230 --> 00:00:22,830 we discussed how to build user form and use a user form in excel. 5 00:00:22,850 --> 00:00:28,990 And so what we have in this particular form is I can add employees to this table and I can select whatever 6 00:00:28,990 --> 00:00:30,010 role they're in. 7 00:00:30,010 --> 00:00:31,750 Enter a first name. 8 00:00:31,750 --> 00:00:39,530 So we'll just do an example we'll just say Hannah Smith and we won't check full time so we'll just click 9 00:00:39,530 --> 00:00:45,740 add and we can see the employee has been added to the table and on my actual table we can see that her 10 00:00:45,740 --> 00:00:49,800 name does appear here with the information that we selected. 11 00:00:49,880 --> 00:00:55,100 So we're gonna go ahead and go to the developer tab and we're going to open up our visual basic window 12 00:00:56,670 --> 00:00:59,250 and I'll go ahead and I'll make this full screen. 13 00:00:59,340 --> 00:01:05,040 Now I'm going to go ahead and I'm going to open up the code that appears in my actual form by right 14 00:01:05,040 --> 00:01:11,370 clicking and selecting view code and we can see here this is all the code that's tied to my form itself. 15 00:01:11,370 --> 00:01:17,730 And so we're going to talk a little bit about different methods we might use for debugging and identifying 16 00:01:17,730 --> 00:01:18,920 errors in our code. 17 00:01:19,500 --> 00:01:25,650 So up here you can see VBA actually has a debug menu that you can click on and we can see step into 18 00:01:25,890 --> 00:01:32,160 step over step out run to cursor and watch quick watches that it watches breakpoints et cetera. 19 00:01:32,170 --> 00:01:36,660 And so this is what we're gonna talk through in this video are these particular tools and how we can 20 00:01:36,660 --> 00:01:38,970 apply some of these tools to our code. 21 00:01:38,970 --> 00:01:44,070 So we've actually used some breakpoints before and this is actually what appears when you click to the 22 00:01:44,070 --> 00:01:47,850 left of a line and it highlights this line in red. 23 00:01:47,850 --> 00:01:51,690 And this means this line is a break point for our code. 24 00:01:51,690 --> 00:01:57,720 So as our code runs through this particular subroutine it's actually going to break on this line and 25 00:01:57,720 --> 00:02:03,510 give us access to our code so we can make some adjustments or see where we are at in the code. 26 00:02:03,510 --> 00:02:05,000 So I'm going to break a little bit later. 27 00:02:05,010 --> 00:02:09,870 I'm going to break on this roll line and I'm going to go back to my excel file. 28 00:02:09,960 --> 00:02:16,500 Go ahead and add an employee and I'll just fill some random values in here that we can just get an idea 29 00:02:16,500 --> 00:02:23,440 of what's going to show up and I'm going to click Add we can see it has successfully broke the code 30 00:02:23,800 --> 00:02:28,950 on this specific line that we asked it to break on and this is a good way to pause your code. 31 00:02:28,960 --> 00:02:33,250 Maybe after you've assigned all your variables or something so you can come back through and you can 32 00:02:33,250 --> 00:02:34,900 actually check what your variables are. 33 00:02:34,920 --> 00:02:40,720 So I can hover over my first variable my last variable my start variable and I can see what values are 34 00:02:40,720 --> 00:02:43,340 currently stored into all of those. 35 00:02:43,390 --> 00:02:49,540 I can also see right now my role doesn't have an assigned variable and that's because I haven't actually 36 00:02:49,540 --> 00:02:51,550 executed this line of code yet. 37 00:02:51,910 --> 00:02:56,890 So wherever you add a break it will pause on that line before running that line. 38 00:02:56,890 --> 00:03:03,820 Now if we want to step through this line by line we can either use debug step into or we can just press 39 00:03:03,820 --> 00:03:05,790 f 8 on our keyboard. 40 00:03:05,920 --> 00:03:10,450 So I will hit f 8 and we can see now we've moved past this roll line. 41 00:03:10,450 --> 00:03:16,960 If I hover over roll I can see we've assigned it the value of sales run to cursor is another helpful 42 00:03:16,960 --> 00:03:17,320 tool. 43 00:03:17,350 --> 00:03:23,950 So if I move my cursor down say this is the next line I want to stop on rather than adding a break if 44 00:03:23,950 --> 00:03:30,370 we don't want to have to add another break into our code we can use debug run to cursor or control and 45 00:03:30,370 --> 00:03:35,960 evade and we can see this time it ran directly to where I had my cursor which was on this line. 46 00:03:36,040 --> 00:03:41,950 It has actually executed these in-between lines I can see that when I hover over those variables and 47 00:03:41,950 --> 00:03:43,830 I can see what those values are. 48 00:03:43,840 --> 00:03:50,170 So this debug menu up here has some really helpful things in step into run to cursor. 49 00:03:50,170 --> 00:03:54,290 We've talked a little bit about our breakpoints and things like that. 50 00:03:54,310 --> 00:03:59,860 So the next thing we'll talk about is adding a watch and what a watch does it is in this window down 51 00:03:59,860 --> 00:04:00,270 here. 52 00:04:00,280 --> 00:04:03,190 And this will show up when you add a watch or edit a watch. 53 00:04:03,280 --> 00:04:08,230 You can assign a watch on a particular variable or something that you're interested in tracking throughout 54 00:04:08,230 --> 00:04:14,620 your code and then as you run your code you can see how that variable changes values or contacts or 55 00:04:14,620 --> 00:04:15,910 type or anything like that. 56 00:04:15,940 --> 00:04:20,800 And so it's super helpful for if you have a really long piece of code where you're using the same variable 57 00:04:20,800 --> 00:04:25,720 for multiple values or maybe you're doing some math on a variable or something and you want to see how 58 00:04:25,720 --> 00:04:31,780 that variable changes so we can select a variable we'll just do this for our first variable here as 59 00:04:31,780 --> 00:04:38,070 an example and we will add a watch and we can see it's automatically selected this first expression 60 00:04:38,070 --> 00:04:43,770 because that's what I had my mouse on when I when I actually clicked on this and it shows us the context 61 00:04:43,770 --> 00:04:47,090 that we're running this in and the module that we're running this in. 62 00:04:47,130 --> 00:04:52,710 And so all this means is we're in this particular Add button subroutine here and we're in our ad employee 63 00:04:53,310 --> 00:04:57,530 module which is just the code that's tied to our ad employee form. 64 00:04:57,960 --> 00:05:02,930 So I can click OK and we can see this has showed up down at the bottom of our screen here. 65 00:05:03,150 --> 00:05:08,070 And the variable is first this is what we're tracking we can see right now we've stored the value F 66 00:05:08,460 --> 00:05:12,880 into this variable which we could also figure out by hovering over the variable. 67 00:05:12,990 --> 00:05:17,160 But if you wanted to do this with a bunch of watches instead of having to outbreak and then manually 68 00:05:17,160 --> 00:05:21,930 come through and hover over each variable to see its value you could do that with watches instead so 69 00:05:21,930 --> 00:05:28,840 watches are another very helpful tool for debugging and seeing if your variables are reading correctly. 70 00:05:28,940 --> 00:05:34,830 You can also build message boxes in at strategic points in your code for testing purposes. 71 00:05:34,850 --> 00:05:39,290 So if I don't want to have to use any breaks and I don't want to have to use any watches or anything 72 00:05:39,290 --> 00:05:44,900 like that I just want to run my code cleanly and I want to know when I get to certain points in my code 73 00:05:45,290 --> 00:05:46,820 I could build a message box. 74 00:05:46,850 --> 00:05:58,060 So for example I could build a message box here that says the variable R is and then show me the variable 75 00:05:58,130 --> 00:06:04,520 R and when I run this line a message box will pop up on my screen that shows me what my variable R is. 76 00:06:04,520 --> 00:06:08,810 So I know at this point OK I've successfully hit this line in my code. 77 00:06:08,810 --> 00:06:15,620 I know that my R variable is whatever it is being displayed on my screen so it'll give me an idea of 78 00:06:15,700 --> 00:06:16,010 OK. 79 00:06:16,010 --> 00:06:21,530 I can successfully run to this point in my code and I know my variable R either is or isn't pulling 80 00:06:21,530 --> 00:06:22,060 correctly. 81 00:06:22,070 --> 00:06:27,200 And so you can use message boxes to build in little checks in your code just for developer purposes 82 00:06:27,200 --> 00:06:32,460 and then later on you can come back through and you can comment out these lines with an apostrophe and 83 00:06:32,470 --> 00:06:36,640 that way it makes a little easier for you if you ever run into an error in the future you can come through 84 00:06:36,640 --> 00:06:44,260 and turn those message box lines back on and get an idea of where your error is actually existing. 85 00:06:44,280 --> 00:06:50,880 Now one of the most common types of errors that you'll get in your code are variable errors and this 86 00:06:50,880 --> 00:06:56,540 generally references when you have a variable that you've declared incorrectly. 87 00:06:56,580 --> 00:07:03,300 And so what that means is if we declare this variable the first name variable for example as an integer 88 00:07:03,390 --> 00:07:09,090 instead of as a string then later on in our code we're gonna get a little bit of an issue when we try 89 00:07:09,090 --> 00:07:15,830 to assign a text value of F to a variable that we have predetermined should be an integer and that'll 90 00:07:15,870 --> 00:07:16,940 throw an error. 91 00:07:16,950 --> 00:07:22,890 You can also get an error if you assign text to a variable that you later on try to perform math on 92 00:07:23,160 --> 00:07:27,900 that would also give you an error because you can't perform math on just text. 93 00:07:27,900 --> 00:07:33,660 So the most common error is variable mismatch error which is what we just discussed. 94 00:07:33,660 --> 00:07:37,820 So if you ever have some variables that don't seem to be pulling correctly or that are throwing errors 95 00:07:38,070 --> 00:07:42,270 that's the first thing that I would go in and check is make sure that you're type that you've declared 96 00:07:42,270 --> 00:07:48,840 your variable as matches up to what that variable is actually representative of another common error 97 00:07:48,870 --> 00:07:54,660 is performing work on the wrong sheet or reading data from the wrong sheet or you know wrong workbook 98 00:07:54,690 --> 00:07:55,540 in excel. 99 00:07:55,680 --> 00:08:00,240 And this goes back to when we were talking about the object hierarchy and how important the object hierarchy 100 00:08:00,240 --> 00:08:07,110 is we can see here I've gone ahead and specified I'm going to activate my sheet one worksheet and all 101 00:08:07,110 --> 00:08:11,820 of the code that follows will be applied to this active worksheet which is Sheet 1. 102 00:08:11,850 --> 00:08:16,590 It's important that we keep that in mind as we move through our code and we make sure we're always looking 103 00:08:16,590 --> 00:08:19,830 at the right workbook in the right worksheet in the right range. 104 00:08:19,830 --> 00:08:25,590 And so this is something that also causes a lot of error drives a lot of error later on is if we're 105 00:08:25,590 --> 00:08:31,200 not paying attention to what is our active worksheet or our active workbook and are we referencing the 106 00:08:31,200 --> 00:08:34,260 right worksheet in the right workbook in the right data. 107 00:08:34,260 --> 00:08:38,430 And so this is a second thing that I would check if you ever are getting an error or your code isn't 108 00:08:38,430 --> 00:08:45,120 running as you expect it to now VBA is also very good about actually giving you an error code. 109 00:08:45,120 --> 00:08:47,650 Anytime it encounters an error. 110 00:08:47,790 --> 00:08:53,010 And so generally speaking if we have an error that our code experiences while it's running VBA is going 111 00:08:53,010 --> 00:08:58,440 to go ahead pause it's going to pull up this window it's going to highlight the line just like this 112 00:08:58,440 --> 00:08:59,390 is highlighted. 113 00:08:59,520 --> 00:09:03,870 It's going to highlight the line that is giving an error and it's going to give you a little pop up 114 00:09:03,870 --> 00:09:09,090 window that has an error code in it and that error code is something that you can actually Google or 115 00:09:09,090 --> 00:09:14,130 you can get on Microsoft's help site which is a great resource and you can figure out exactly what that 116 00:09:14,130 --> 00:09:15,570 error code means. 117 00:09:15,690 --> 00:09:19,090 And that gives you a more specific idea of what you need to address. 118 00:09:19,230 --> 00:09:25,230 And you know the issue that is existing is on whatever line is highlighted so you know what you need 119 00:09:25,230 --> 00:09:27,390 to address and where you need to address it. 120 00:09:27,690 --> 00:09:33,720 So VBA actually has its own built in error addressing functionality that makes it a lot easier for you 121 00:09:33,720 --> 00:09:36,340 to address any errors that you are getting. 122 00:09:36,480 --> 00:09:44,340 But with that being said you don't necessarily want your code to break and open up for any user who 123 00:09:44,340 --> 00:09:49,080 happens to be in the code so you don't necessarily want a file that you're pushing out to a big group 124 00:09:49,080 --> 00:09:54,930 of people to have an error that lets your users get into the background code and mess up the background 125 00:09:54,930 --> 00:09:55,630 code. 126 00:09:55,680 --> 00:10:01,350 And so there are some error handlers that we can build into VBA that will take an error and process 127 00:10:01,350 --> 00:10:07,020 it internally in the code without opening up the code or any sort of window for the user to interact 128 00:10:07,020 --> 00:10:09,190 with and potentially mess up your code. 129 00:10:09,210 --> 00:10:15,810 And so that's what we'll be covering in a later video but this video to be discussed the various methods 130 00:10:15,810 --> 00:10:17,580 you can use to debug your code. 131 00:10:17,580 --> 00:10:21,860 Some of the tools that VBA has built into the debug menu which are very helpful. 132 00:10:22,050 --> 00:10:27,600 And we talked a little bit about some of the more common errors that you'll experience in VBA and what 133 00:10:27,600 --> 00:10:28,500 to watch out for. 134 00:10:28,560 --> 00:10:29,400 As your coding.