Let's talk about the types of bugs in programs, and how you fix each one.

Here's a worksheet with a buggy sales tax program. There are eleven bugs in the code. See if you can find them all.

I'll wait.

One challenge in debugging is figuring out what VBA's error messages mean. Actually, VBA's messages aren't too bad. Some languages are particularly stupid in their error reporting.

Syntax errors

These are like speling erors in Englsh. Even a computer can find them. Computers don't recognize most mistakes, but syntax errors they find.

Suppose you had this error, and you couldn't figure it out. Lily, what would you do?

Good! Debugging is easier if it's a group sport.

Other ideas. Try looking at code samples on this site. Use the search bar to find stuff. You can also use the keyword list. There's a link to it in the Tools menu.

Ask Google how to do the thing you were trying to do. Like "excel vba get the contents of a cell" (try it). Starting with "excel vba" to focus the search results.

If you get too many irrelevant results, add intext:, as in "intext:excel intext:vba get the contents of a intext:cell". Only search results that contain all of those words will show up.

Documentation errors

These are errors in comments, and other explanations of how a program works. Not helpful when code actually works one way, but the comments say it works another. Spelling errors in comments are common, too.

There isn't an easy way to find documentation errors. Try to avoid them to begin with. If you change a program, change the comments to match. If you find a documentation error, correct it yourself.

Any documentation errors in the sales tax program?

User experience errors

These are mistakes that mislead users. For example, a user does this with the sales tax program:

Oops! Typing mistake. The user typed NI instead of MI. The user corrects the mistake, and pressses the Run button:

There is no error, but the message still shows from last time.

To fix the problem, the program should erase error messages each time it starts:

'Init. Erase error messages.
Cells(3, 3) = ""
Cells(5, 3) = ""
Cells(6, 3) = ""

Logic errors

A logic error is when your program does something that you didn't mean it to do. For example:

If userInput <= 0 Then
    Cells(6, 3) = "Sorry, can't be negative."
    Cells(6, 3).Font.Color = vbRed
    End
End If

The error message suggests that zero should be allowed, since zero is not negative. But the logical expression in the If statement says that zero is not allowed. Either the error message is wrong, or the test.

There are three parts to fixing logic errors:

• Finding whether there are errors.
• Finding what caused errors that exist.
• Changing the code.

Are there logic errors?

The best way to find out is to run the program, give it some input, and compare the program's output with what it should have produced. Do this for every different kind of output the program can produce.

For example, one kind of output the sales tax program should produce is an error message when a numeric input is negative. Let's test that:

Yay! It worked! We can move on.

Right! Zero is a special case, also called an edge case. Edge cases sometimes appear in If statements, like the 0 in If userInput <= 0 Then. You should test edge cases.

In RL, it's easy for bugs like this to slip past the programmers. Not because they're bad programmers, but because there are so many possibilities to test that it's easy to miss one. Having a logic bug slip by is normal, and not something to beat yourself up about.

Software companies know this. They have automated test software, that run thousands of tests comparing what a program should output, with what it does output. They hire people whose only job is to test software. They have alpha and beta testers, humans who use the software before it is released, and report errors they find.

So, to find out if there are logic errors, run your program again and again (and again and again and again and again and again and again) with different input. Compare the output the program produces, with what it should produce. If there's a difference, you may have a logic error.

The cause

Once you've found a logic bug, you need to find out what caused it. There's usually more than one possible cause for each bug.

Take the zero-is-bad error again. You see this when you shouldn't:

There are at least two possible causes. One is that the program read the wrong cell. Rather than Cells(5, 2), it read another cell that has a negative number in it. Another possibility is that the validation test is wrong.

To find the problem, you need to poke around inside the code, and figure out what it does that creates the problem. That's what your good friend the debugger is for.

In this case, we know what code created the message, so we might start there.

As you know, programming is all about the variables. The debugger helps you compare the values you think they should have with the values they do have. You could check the value of userInput by putting the mouse cursor on the variable:

Or use the locals window to see all the variables you defined. View | Locals in VBE's menu. Give it a try. You'll be glad you did.

userInput is what we expected it to be. Now let's try…

… and we get…

The variable userInput contains the right value in both cases. The code is grabbing the data from the right cell, so that isn't the bug. The problem is the test itself. Zero should be allowed for either taxable or nontaxable.

Correcting logic errors

So, you've found a logic error, and its cause. Change the code to eliminate the problem.

Unless the problem isn't in your code.

Good question. That brings us to…

The Super Bug of DOOM!

The SBD is the toughest type of bug. SBDs exist before you even start writing code. They're in the task description, also called the specification.

The spec tells you what the program should do. But what if the spec is wrong?

It happens all the time in RL. The spec for the sales tax program might say:

When the user types a negative number (or zero) for the taxable amount, show an error message saying that the amount cannot be negative, and stop the program.

Implementing the spec exactly means that the program will have a bug.

Some geek humor:

Failure is not an option. It's built-in to the spec.

Bad specs are a problem in systems analysis, and is outside the scope of this course. We'll assume all specs are correct.

Unpredictable

It's hard to predict how long it's going to take to write a program. Debugging makes this worse. What you think is a five-minute fix can end up taking an hour.

Allow yourself enough time. Don't start working on an assignment the day before it's due.

Bugs in the sales tax program

See how many you found.

1. Option Explicit is missing. Asking for trouble.
2. LCase instead of UCase.
3. Doesn't erase error messages.
4. Numeric validation stops zero, claiming it is negative.
5. Cells(5, 3).Font.Co1or = vbRed – Co1or has a 1 (one), not an l (el).
6. Ohio tax rate should be 0.0575.
7. LCase(Trim(Cell(3, 2))) – Should be Cells, not Cell.
8. The first comment 'Input nontaxable is wrong.
9. "Sorry, can"t be negative." – Has a double quote (") in the middle.
10. Round(taxable * taxRate) – Round should have 2 as the second parameter.
11. total and tax are output in the wrong cells.

Exercises

Summary

Bugs are frustrating. They're also inevitable. Learn to deal with them, and they're less scary.

Syntax errors are like speling erors in Englsh. Even a computer can find them.

User experience errors are mistakes that mislead users. Like leaving error messages around.

Documentation errors are errors in comments. They're hard to find.

A logic error is when your program does something that you didn't mean it to do. There are three parts to fixing logic errors:

• Finding whether there are errors.
• Finding what caused errors that exist.
• Changing the code.

It's hard to predict how long it's going to take to write a program. Debugging makes this worse.