Debugging

Error messages

Sometimes, you know when there are bugs, because the program gives you an error message.

Here's an example. We want a workbook that will compute the average of two numbers, so we start with this:

Here's the code, in VBE:

Not much to it. Input, processing, output.

​Small goat: What could possibly go wrong?

Let's try it. You can download the program, and try it yourself.

Argh! How is that possible?!

When there's a bug, the first thing I do is read through the code. Carefully! I keep my brain focused on checking what I typed. Most of the time, just reading carefully will find the mistake.

Here's the code again:

Can you find the error? It's subtle.

Suppose I read through the code, and can't see anything wrong. What now? This is where debugging helps.

BTW, there's a page showing common error messages. It's in the lesson tree, under Extras.

Using the debugger

The debugger is built-in to VBE. Go to VBE, and show the Locals window. That's View | Locals.

Recall that a variable is a piece of memory with a name. The locals window shows the variables' memory. You can see the variables change as the program runs.

The debugger lets you add breakpoints to programs. Breakpoints stop the program while it's running, and lets you look at memory. Then you can compare what values variables have, with what values you want them to have.

To add a breakpoint, click in the grey area to the left of a line of code.

Now run the program, by clicking the button in the worksheet. The program runs down to the breakpoint, and stops.

It stops before executing the line with the breakpoint.

The highlighted line hasn't run yet, so all that's happened in the Diming of the variables. You can see in memory that score1 is 0, score2 is 0, etc. As we expected.

Now, let's tell VBA to run the next line:

All that should happen is that the program copies the value in Cells(1, 2) into the variable sScore1. The cell has 1 in it:

OK, let's see what happens. To run one line, press F8. That's a shortcut for Debug | Step into. You'll see:

In memory (the local window), you can see that sScore1 is 1, as expected.

Here's the worksheet again.

Here's the next line of code, and the locals window:

The next line of code to be run is sScore2 = Cells(2, 2).

Before we run that line (by pressing F8), make a prediction.

Ethan, what do you think?

Let's see. Press F8.

Yes! You got it!

So far so good. The program has done what we expected it to. No bugs found, so far.

The next line of code:

Make a prediction.

Georgina?

Let's see. Press F8.

Woohoo! That's right! That line looks like it's bugless.

OK, the next line.

Make a prediction.

Ray?

Let's try it. Press F8.

OK, we now know the line of code that has the problem. The program did something we didn't expect when it ran that line. Memory didn't look the way we thought it would.

That's what the debugger has done for us. It helps us find where the mistake is. It won't tell us what the problem is, but now we know where to look.

Anyone see it? Here's the code again:

You got it!

We just did. That sort of typo is hard to find by reading the code. But we used the debugger, found it, and now we can fix the problem. Replace the n with an m, and the program works.

What the debugger does, again

The debugger let's you work through your code line by line. It shows you what's in each variable.

You can compare what you expect to be in the variables, with what is actually there. When you find a difference, that's where the bug is. Then it's up to you to fix it.

To use the debugger, make sure the locals window is showing. That's View | Locals in the VBE menu. Then add a breakpoint to your program, by clicking in the grey area next to the line where you want the program to pause.

Run the program, and it will stop on the line with the breakpoint. Press F8 to step through the program line by line. Compare what the program does (by looking at the values in the local window), with what you think it should be doing. When you see something unexpected, that's where the bug is.

Avoiding this particular bug

The mistyping-the-variable-name bug is so common, that Microsoft added something special to VBA to help us find those bugs. Here's the code again.

We're missing the line Option Explicit at the top of the program.

Option Explicit forces you to declare your variables. Here's the code again, with Option Explicit.

The bug is still there, I just added Option Explicit.

Here's what happens when the program runs.

That means that VBA found a variable in the code, that doesn't have a Dim.

Click OK, and VBE will show you where the problem is.

Aha! We can see the misspelling.

Takeaways

You've learned two things in this lesson:

• How to use the debugger.
• Why you should always use option explicit.

Come back to this lesson when you want a reminder about how to use the debugger.

You should use Option Explicit in every program.

Exercise