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:

Start

Here's the code, in VBE:

Code

Not much to it. Input, processing, output.

Goat

​Small goat: What could possibly go wrong?

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

Division by zero

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.

Principle

Read code actively

When code doesn't work, the first thing to do is carefully read through the code.

Here's the code again:

Code

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.

Locals window

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.

What it doesn't do

The debugger will not fix bugs for you. It can't. It's just as stupid as the rest of your computer.

The debugger will help you understand your program, by comparing what you want your program to do, with what it actually does.

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.

Setting a breakpoint

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

Stopped at breakpoint

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:

  • sScore1 = Cells(1, 2)

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:

Start

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

One line run

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

Here's the worksheet again.

Start

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

One line run

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

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

Reflect

What will change in the locals window, when we run that one line?

(If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.)

Marcus, what do you think?

Marcus
Marcus

Well, the cell at row 2, column 2 has 2 in it, so that should go into score2.

Let's see. Press F8.

Run the second line

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:

  • sTotal = sScore1 + sScore2

Run the second line

Make a prediction.

Reflect

What will change in the locals window, when we run that one line, sTotal = sScore1 + sScore2?

(If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.)

Georgina?

Georgina
Georgina

sScore1 is 1, and sScore2 is 2, so sTotal should go to 3.

Let's see. Press F8.

The next line

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

OK, the next line.

  • sNunberScores = 2

Make a prediction.

Reflect

What will change in the locals window, when we run that one line?

(If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.)

Ray?

Ray
Ray

Easy. sNumberScores should go to 2.

Let's try it. Press F8.

Run the next line

Ray
Ray

Wait, what? sNumberScores is 0. But there's another sNumberScores below that, that's 2. How can there be two variables with the same name?

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:

Run the next line

Adela
Adela

I see it! Oh, this is really tricky. The variable name is wrong.

  • sNumberScores = 2 What we wanted
  • sNunberScores = 2 What's in the code

You got it!

Ray
Ray

You've got to be kidding. An n instead of an m? Who could find that?

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.

Setting a breakpoint

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.

Code

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

Added Option Explicit

Check this page.

Reflect

What does Option Explicit do?

(If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.)

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

Added Option Explicit

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

Here's what happens when the program runs.

Unknown variable error

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.

VBE showing error

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

Exercise

Circle bug hunt

Download this workbook. It shows:

Wrong output

The user enters the diameter of a circle, and the program is supposed to compute the area of the circle, and the volume of a sphere with that diameter. But the output's wrong.

Fix it.

Upload:

  • Two screen shots of you using the debugger in VBE to inspect the values of the variables as the program runs. Each screen shot should have the code window, and the locals window.
  • The fixed workbook.
Attachments