In the last lesson of this video course, we'll be focusing on debugging code. We've already experienced some coding errors, also referred to as bugs during this course. As a matter of fact, if we haven't, we could consider ourselves lucky as errors and coding go hand in hand, typos will happen. Syntax may be overlooked. Math errors may occur, and there are many more things that can cause code errors. During program execution. This lesson will introduce you to the three types of errors.
That can occur during execution and the reasons why they occur. The process of tracing and correcting code errors is known as debugging. The visual basic editor has a Debu tool. That can be used for this purpose. We'll also cover some tips for minimizing errors and what you can do if you cannot resolve them. And you'll see in this lesson, why the structure of your code is important during the process of debugging code is easier to review when it's organized in a logical structure. So specifically we will be covering the following topics, the types.
Of coding errors and their cause. Using the debug toolbar to investigate errors and we'll be setting break points, stepping through code, using break mode during run mode and determining the value of expressions. Then we'll move on to handling errors. Where you'll gain an understanding of error handling and learning about VBAs error, trapping options. We're gonna get into trapping errors with the on era statement, which.
Includes understanding the error object, writing an error handling routine, and working with inline error handling as mentioned, you'll get tips for minimizing errors and tips on what to do. If you cannot resolve. Finally, you'll be introduced to the object browser window. And we'll review more detail about that. When we get there, we're gonna be using our sales fiscal year file in this lesson. There are only three types of errors that you may have in your visual basic for applications code..
A runtime error happens when a line of code cannot be execut. The procedure is halted and a message box will display that defines the error. There is a help button in the message box that can be used of you, the help topic associated with that specific type of runtime error. There are many types of runtime errors there're caused by. If you divide by zero. That can't be done. If you reference a non-existent workbook or worksheet or other object in referencing an Excel cell that contains an error. Are a few examples of things that can cause a runtime error and last but.
Not least, you have syntax errors. Now these are detected by the line editor and the compiler. So as you're typing code, when you get to the end of a line and you press enter, if there is a syntax era in that line, the line will turn red and examples that can cause this are incomplete express. And missing arguments and then syntax errors are also detected by the compiler. So all along we've been compiling and then saving. And the compiler checks all lines of code in each procedure and all.
Declarations within the project. If variable declaration is required, if we have that option explicit statement at the top of a module, the compiler will check that all variables are declared. And that all objects have correct references to properties, methods, and events. It also checks constructs to ensure the correct required statements are present. So when you have a width block, you need to have the end width statement. When you have an if block, you need to have end..
If the compiler will display a message box that describes the era, if found. So I mentioned using the debug toolbar in the visual basic editor to investigate errors. The tools that are on that toolbar can be categorized as follows tools that help you manually execute the program tools that suspend the execution of the code. And tools that assist in determining the values of expressions. So there's an illustration of the debug toolbar and its tools..
So the first set of tools would be the ones that could help you suspend the code. And then you have a set of tools. That can help you manually execute the program. And that would start with like toggle breakpoint all the way through step out. And then the last group of tools, the window tools I call them are tools that assist in determining the values of expressions. So this slide is for your future reference is just a further description of the debugging tools and they're shortcut keys. If any. And a description, a comprehensive description of each tool..
Posts Related:
Now most debugging is done when the application is suspended and that's known as break mode.
An application is in break mode when a runtime era occurs. A break point is manually inserted into the code or a stop statement is entered within the code. Some of the tools for debugging can be utilized in design mold and or at runtime. In addition to when the application is in break mold. So for example, the step into tool can be run in break mode and design mode..The immediate window can be run in break mode, design mode, or at run time. And the watch window can be run in break mode and run time. Now, before we do this next exercise, I just wanna review and I increase the size of the font for three of the tools on a debugging toolbar. And those are the step tools. So we used step into earlier. In the course. And we saw that, that executed code one statement at a time, one line at a time.
There's also the ability to step over code and that would execute code one procedure or one statement at a time. And then you have your step out option, which can overlook a called procedure. And execute the remaining lines of the calling procedure. So you'll get more experience with step into and you'll get new experience with step out in this next lesson. So we're gonna start by creating a syntax era. And remember, these would be detected by the line editor when you get to.
The end of the line and you press enter, or by compiling your project, which we've been doing all along. And when you compile it, compiles everything in the module, we're gonna force this to happen. I'm in the visual basic editor for sales fiscal year. I'm in module one in the ad totals procedure. And what I'm gonna do is I'm gonna delete the end if statement, and then I'm gonna go up and compile the project. Now it gives me a compile error and it specifically tells me block.
If without end, if so I can click. Okay. And I'm gonna put my end, if statement back in. And then I'm gonna compile again and I don't get the error. Let's do another one in the if line we're gonna change the, not equal to symbol to an exclamation point, put an exclamation point there. And now click out of that line. And when you click out of that line, the compile error comes up. It says, expect it then, or go to, and the entire line turns red..
We're gonna click. Okay. And now go up and compile the project. And this time you get a compiled error that says syntax error. So this one's a little bit more specific. It's letting you know, there's some kind of syntax error in that line. And this is typical when you're clicking out of the line, it's the same as pressing enter at the end of it. And the line editor kicks in. When you compile, then the compiler kicks in. This message is more specific. I'm gonna click. Okay. And I'm gonna change the exclamation point back to our not equal.
Symbol, and then I'm gonna compile. So now I'm in modern reports and I used my procedure list to get to the consolidate data procedure. That's one that we brought in from a text file. And we're gonna go to the view menu, hover over tool bars. And click on debug. So the debug toolbar comes up on the screen..
Now we're looking for a specific line of code. And before we find it, I'm going to decrease the width of the project, Explorer and properties pains on the left, because we're gonna end up viewing this procedure side by side with the Excel workbook. After we set our manual break point. So we're gonna scroll down in the procedure. It says select case FRM, generate reports dot period. When you find that line, you can be anywhere within it. And on a debug toolbar, if you hover over the hand icon, you'll see that it says toggle breakpoint, and it gives you the shortcut key, which is F nine..
I'm gonna just click the hand and it turns that line maroon, and it puts a maroon oval. In the gray bar to the left of the line that is indicative of a manual break point. When you set a manual break point and you execute the code, it will execute it up until the break point. And then it goes into, what's known as break mode as you'll see in the moment. So I'm gonna arrange this so that my editor window is on.
The right side of my screen. And I have my Excel file on the left side of my screen and in the Excel
File, I'm gonna go ahead and start this procedure by running the show form procedure from the quick access toolbar. So it's gonna bring up the form and in the period frame, I'm gonna select month and then choose August from the combo list. And I'm gonna select salesperson in the sales frame. And click display. So if you notice on your Excel screen, the reports sheet has been.Created, but it's not populated. And if you look at your editor screen, now that line that where we set the manual break point. has yellow shading and a yellow arrow in the gray border to the left of it. And so it ran all the code up until it gets to that line. And now it's in what's called break mode. Now that we're in break mode, we can use our step tools. I'm gonna just display my debug toolbar again. And your step tools are to the right of the hand. So there's your step into that does line by line..
You have step over. Which goes procedure by procedure or statement by statement. And then you have step out that would skip any called procedures or just execute the rest of the code as one block. So the first thing we're gonna do is step into, and it goes down to the next line and we'll do step over at this point. That's still within that four each. Block, we're gonna step over again. It goes down to compare, which is another statement we're gonna step over again..
And it goes to the next statement. And now go ahead and click on step out and it runs the rest of the code, and you can see that it's going through your Excel workbook and ultimately you'll get the popup. Asking if you wanna permanently delete the report sheet in the sales fiscal year, I'm gonna go ahead and click delete and it opened the reports file and gave me my pivot table. We're gonna go ahead and close the reports file and don't save the changes. And I'm gonna just maximize the visual basic editor window again..
I'm gonna expand my project Explorer. Window so that I can get back to modern reports and notice it didn't remove our break point. So I'm gonna click in that line and consolidate data procedure and go click the hand again. To get rid of the break point. And typically after using these processes, I go ahead and click the reset button on the debug toolbar, just to make sure there's nothing lingering in memory. You can go ahead and save. So now I'm gonna challenge you and give you an on your own exercise in.
The same consolidate data procedure. I'm gonna have you set the break point again at the same line, select case FRM generate reports, period. And then I want you to execute the show form procedure until the program enters break mode. So go ahead and pause the video and do those things. At this point and I have my windows side by side. Again, the program has halted execution at the point where it created the report sheet. And now we can look at a couple of the window debug tools..
So with my debug toolbar to the right of your step tools are your window tools. So the first one we're going to select is the local's window. And it opens into pain on the lower half of the editor's window. And what it's showing you is the current value of any variables. If you look at mod reports under expression, it has a plus sign. When you expand it, you'll see your public variables and. Values at this point, it also on the right tells you the type of variable..
So the local's window will show you that when you're in break mode and I just collapsed mod reports again. And then what I'm gonna do is I'm going to go ahead and press F eight. So it's at my break line. I'm gonna press F. So it steps into the code and I'm gonna F eight until I get down to the on era resume. Next statement. So now there's a, in the expression window, the value of compare, the.
Compare expression is reports. If I hover over compare. In the code window, you can see that it's letting me know the value of that variable right in the code window. When it's in break mode in that compare line in the code window, I'm gonna have you select the function C date, which is a conversion function. Right converts the date on the sheet tab, the date, the text string of the.
Date on the sheet tab to an actual date. So we're starting with the letter C and C date, and we're gonna highlight it until we get to the first closing parenthesis after compare. So just the function and its argument in parenthesis is selected on your debug window. The next to the last icon is quick. Watch another window. So when we have that expression selected, it gives us the context..
So it lets us know the VBA project model reports, consolidate data procedure. the expression itself. And then its value is a type mismatch because there's text on the sheet tab and then we're converting it to a date. So quick, watch gives you different information than can show in your locals window. And we're gonna close the quick watch window and you can use the X. To close the local's window. And now what you can do is you can go ahead, step out of your code..
And I deleted the report sheet and I'm gonna close the report's workbook without saving the changes. And I'll just maximize my editor window again. We're gonna start forcing some runtime errors to happen, and it is best practice to write error, trapping code, to try to avoid runtime errors. You might not be able to avoid all of them, but it will be helpful to avoid as many as possible error trapping options are set in the options dialogue.
Box in the editor where we set the required variable declaration earlier. And changed our font size to review those options before we get into. Error handling they're listed on this slide. So there's three options you can break on all errors. So that means even if you've written error handling code a break in execution will occur. If a runtime error is encountered. So it really disables any error handling code that you've.
Written break in class module. The execution will break and an error message will display when an unhandled error occurs within a class module. This option is only useful for debugging. And then you have break on unhandled errors. The execution will break, and an error message will display when any unhandled era occurs. And again, we'll review those options as we go over our next set of exercise. There are some methods to error handling and visual basic for applications..
An on era statement is used to enable what's called an error trap. If an error is generated after this statement is executed. The error handler becomes active and passes control to the code on the on era statement that it's is specified and there are two types of on era statements on era go to and on era resume. Next, I have the syntax. So for on era, go to then there's a corresponding line label. And then on era resume next, once an on era statement, traps and error..
The error can be handled in one of three ways you could write an error handler. This is a routine that is pointed to in the on era go-to statement, line label, the line label statements address one or more types of errors for the procedure. Another method is ignoring the error and that's what happens when you use the on era resume. Next statement to trap the era and handle. By moving to the next line of code and then you have inline error handling.
And that's also on error resume. Next. You can use it to trap the error. You enter code in the procedure to check for errors after any statements that are expected to generate them. You'll see in the upcoming exercise that the air object can be used to examine information. About an era that has just occurred. The error object has a global scope and has properties and methods that are useful for finding out information about the current era clearing error.
Information and generating errors. The properties contain information about the error that just occurred in the current procedure. So you have a number property, and it's the identification number of the most recent era and numbers represent different types of error. You have a description property, which describes the error and corresponds to the error number. And then you have a source property, which is a name that identifies the component module and or procedure that generated the error..
And all three of those properties have data types as listed on this slide. The air object only has two methods clear and raise. Clear resets all the error objects, properties to zero or zero length strings. This method is used automatically when any on error statement is encountered and then you have the raise method. Which generates a runtime error and it can specify the number of an error defined by VBA Excel or another application such as word..
And you're like, what is she talking about? Well, it'll start making sense when we start doing it, which is going to be right now. We're gonna start by causing a situation that will lead to a runtime era. So I'm in my working directory. And what we're gonna do is we're gonna rename the Excel file called reports. We're gonna just name it reports with the number two, and now I'm going to bring up my sales fiscal year file. In Excel, I'm gonna start the show form procedure from the quick access toolbar..
I'm gonna select all for period and model in the sales frame and click display. Eventually you'll get the runtime era has an error. Number 1 0 0 4. Sorry, we couldn't find my path reports dot XL SX. Is it possible? It was moved renamed or deleted and we are gonna click the debug button on that message. So it takes us over to model reports to the sub procedure finished report and.
The errors being caused when it tries to. That workbook.open file name line, because it's looking for a file name, the reports. Now I'm gonna go up to the standard toolbar and I'm gonna click the reset button. I'm gonna go then go back to modern reports and notice that it cleared that break in the code for us. So now before we write error handling code, I wanna show you the options that are available for you..
We saw them on a slide, so I'm gonna go up to the tools, menu, and choose options. And when I get in there at top, I'm gonna go to the general tab. And on the right side, you have your error trapping options. So remember break on all errors really means ignore any error handling code that you would write. Your other choices are break in class module or break on unhandled errors. I usually use break on unhandled. Can't really say usually it just depends on what kind of project I'm.
Working on and I'm gonna click okay. To get out of. So now we're going to modify this finished report sub procedure by using the on era, go to statement. So we're gonna click at the end of our variable declaration line, dim sheet as worksheet, press enter couple of times, and then you're gonna type on error. Go to and go to is one word here. And then era handler in this example, era handler is what.
We're calling our line statement. So on era, go to era handler, we're gonna go down and click at the end of the set fields called procedure and press enter. And on this line, we're gonna type exit sub and I'll explain it. After we get the rest of this in, we're gonna press enter a couple of times and then we're gonna type error handler. What we named our line label followed by a colon enter, and we're gonna do a select case statement here, select select case, and we're gonna do air dot and the list.
Pops up and we're gonna select number. So that's a property of the air object. So select case air numb number, enter tab. And then we're gonna say case 1004, that's the number of the runtime error we got. So case 1004, enter tab message box. And in parenthesis and quotes, the reports workbook is not available..
Period. Close your quotes, close to Peren enter. We're gonna out dent and we're gonna do case else enter tab message box again. And this one is going to say era number. We're gonna do a concatenated statement. Colon space, double quote, our ampersand for conation air dot number again, ampersand. We're gonna do VB L F, which is line feed..
We're gonna do an ampersand and we're gonna do our line continuation character of space underscore enter tab in double quotes, era description. Colon space, double quote space, Amper sand and air dot description. So another property of the air object there, and we're gonna close the parenthesis. We're gonna enter an out dent until we get to the same margin as select case. And we need our end select statement there. And now we'll break down what we just did..
So using on era, go to what we've done is we said, if there's an error, when this procedure runs go to our line. So era handler is the line. What we, what we named it. So if there is an error, it's gonna go down. and before it gets to that line, it's gonna exit the sub procedure. Now this is only if there's an error, we don't want it to try to keep running the sub procedure..