Running a subroutine without stopping is one way to verify your work, but sometimes the only way to find an error or to discover how an unfamiliar program works, is to move through it step-by-step.
- [Instructor] Running a subroutine without stopping is one way to verify your work. But sometimes the only way to find an error, or discover how an unfamiliar program works, is to move through it step by step. As an example, let's take this workbook where I'm tracking the amount of money that I owe to various taxing authorities. This workbook is the stepping workbook and you can find it in the chapter four folder of the exercise files collection. It looks like a fairly simple workbook, but I have some VBA code associated with it.
To take a look at the code, I'll open the visual basic editor by pressing ALT f11. And you can see that instead of having the editor take up the entire screen, I have it down so that it covers about half the screen, and I can see the part of the workbook that contains data. I have done that so that I can see how the instructions in the subroutines affect the workbook. So let's take a quick look at the code over here in module one.
I have three subroutines: display info, display state, and display city. Let's take a look at the second two subroutines first. They're very similar. I have display state and all it does is display a message box with the state variable. And that will come out of cell C7. The next one is display city and it shows the city variables value. That will come from cell D7.
The values are read in, in the first display info subroutine and you see the variable assignment to state and city here. Below that we have the names of the subroutines display state and display city, and that is how you call a subroutine in Excel VBA. Because I am reading the state and city variables into the first subroutine, but using them in other subroutines, I needed to declare them as global variables.
In other words, available to every subroutine within the module, so you see them here in the declarations section above the first sub keyword. With all that as preamble, lets start running through the code. If you want to run code all at once you press f5, which we've done elsewhere. If you want to go through step by step, you press f8. There are a few other options, but we'll start with f8. So, in sub display info, I'll press f8, and you see the first line is highlighted.
Next I will press f8, f8 again, range B7 has been activated. Now we're going to assign the value one column to the right of the active cell, to the string state variable. So I press f8. And one thing to note is that while you are checking your code, you can hover your mouse pointer over a variable and see the value that has been assigned to it. So if I hover my mouse pointer over string state, I get New York.
Now string city is highlighted, but hasn't been executed yet. And I can do the same thing for string city. Now press f8 again and display state is highlighted, f8 again and we jump down into the display state subroutine. Press it again, message box is highlighted. F8 again. We get the message box, click OK. End sub, when I press f8, we go up to display city.
F8, we jump down to the display city subroutine. Message box string city, there it is, click OK, end sub, and then end of display. And end sub and we're done. So that took a while, but I thought it was worth it, so that you could see what happens when you step through a number of subroutines, especially ones that are called from within a master, or main subroutine.
There are two other ways that you can use stepping to move through your subroutines. And those are step over and step out. To start I will go back up to display info and press f8, and I'll keep going until display state is highlighted. Now normally you'd be stepping through your code to make sure it works properly, if you know that display state does work properly then you can step over it by clicking shift f8.
I get New York, OK, and we end up going back to display city. So we didn't stop anywhere within display state, we stepped over it. Now press f8 and let's say that now that I am in display city I want to step out. To do that I will press control shift f8. I get Albany, now I'm back to message box editor display, f8, end of display, f8 again, and we're done.
What happened with step out is that once we were within display city I pressed control shift f8 and because we were already inside of it we needed to run it and then step out. So step over allows you to step over when the call, such as display state is highlighted and step out does the same thing, but when you've already stepped into a subroutine. Moving through your code one step at a time can be a tedious process, but it is often the easiest way to find an error, or to determine how the original programmer constructed the code's logic.
You'll find that pressing the f8 key is usually time very well spent.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures