More debugging

Multiple choice

What's a breakpoint?


A gap in a number sequence. If you had 2, 4, 7, 11, 34, 36, 42, then there's a breakpoint of 23 after the 11.


Point that breaks a circle for a given angle, using Euler's formula.


Stops a program, so you can peek inside memory.


Point at which Cthulhu thinks it has too many goats.

Not graded. So why do it?

Multiple choice

What does VBE's locals window show?


Computers on a network with a latency below 100ms, from the point of view of a given computer.


The values of variables in memory.


A shifting time window, within which VBA normalizes variable values.


Where goats who live within ten miles of Cthulhu can buy tickets to one of its gigs.

Not graded. So why do it?

In the last lesson, you learned about debugging. But it's tricky, so let's run through another example.

Lesson contents

What's a bug, anyway?


So, the debugger doesn't actually fix your code for you. It steps through code.

What use is that?

Good question! To answer that, we have to know what a bug is.

Bug: you intend your program to do one thing, and it does something different.

Brokeh average

For example, say you want to write a program to find the average of three numbers. That's your intent.

Here's a worksheet the user fills in.


They click the button, and see the average. Here's what you want to happen:


That's your intent. The average is right. 2 + 6 + 7 is 15. 15/3 is 5.

You write the program and test it. This is what you see:


Ack! The average is different. What you want the program to do, and what it actually does, is not the same.

There's a bug. Buggy McBug Face.

A bug in the code

The bug is somewhere in your code. But you don't know where it is, or what it is. If you did, the bug wouldn't exist in the first place.

Here's the code you wrote.

  • Option Explicit
  • Sub runClicked()
  •     Dim sValue1 As Single
  •     Dim sValue2 As Single
  •     Dim sValue3 As Single
  •     Dim sTotal As Single
  •     Dim sAverage As Single
  •     'Input
  •     sValue1 = Cells(3, 2)
  •     sValue2 = Cells(4, 2)
  •     sValue3 = Cells(5, 2)
  •     'Processing
  •     sTotal = sValue1 + sValue2 + sValue2
  •     sAverage = sTotal / 3
  •     'Output
  •     Cells(9, 2) = sAverage
  • End Sub

The bug is somewhere in there, but where?

Say you look and look and look at the code. You don't see the bug. How are you going to find it?

Debugging isn't a plug-and-play set of steps. Do this, do that, and everything's fine.

It's more like a detective mystery. There's a crime: the program's not working. Use the debugger to find clues. Then, you have to work out what clues mean.

Adela can fix it

Hey, Adela! Can you show us how you'd debug the program?


OK, no problem.

I'm going to run through the code one line at a time, compare what I intend the line to do, with what it actually does. When the two are different, that's a clue as to where the bug is.

It's all about the variables. I'll compare what values the variables should have, with what they are.

I'll start by setting up VBE. Make sure I can see the Locals window, and add a breakpoint.

Set up VBE


Adela put the breakpoint on the first executable line. You can't put breakpoints on Dims, since they aren't executable statements. They just reserve places in memory.


Now I'll type some numbers into the worksheet, and run the program, by clicking on the button.

Clicky clicky


The program runs, and stops on the line with the breakpoint.


The debugger stopped right before running the line highlighted in yellow.


I can see what the values of the variables are in the Locals window. Since the program has just started, I expect the values all to be 0. They are. So far, what should happen, and what I want to happen, are the same. No bugs.


Remember that the program has stopped before executing the line with the breakpoint on it. The statement sValue1 = Cells(2,3) hasn't run yet.


Now, I'll press F8 a few times, and watch Locals, to see if what I expect to happen, does happen.


F8 runs one line at a time. It's a shortcut to the menu item Debug | Step into. When the debugger stops on a line, you can look at the values of variables. Compare what values they have, and what you expected them to have.

Here's what Adela sees:

Running a few lines


So I ran the input code. It should have copied data from cells in the worksheet into variables. Here are the cells in the worksheet:

Input values

The values in the Locals window for the variables sValue1, sValue2, and sValue3 match the ones in the worksheet.

So far, what I want the code to do, matches what it is doing (see screen shot above). No clues about what's wrong.

OK, now for the next line. It's sTotal = sValue1 + sValue2 + sValue2. I'll press F8. I get:

Total computed

Now, let's see. The line is sTotal = sValue1 + sValue2 + sValue2. What I expect is 2 + 6 + 7, that's 15. So sTotal should be 15...


What, what!? The total should be 15, but it's 14. Huh? What I expected, isn't the same as what happened.

That tells me the bug is on the line that was just executed: sTotal = sValue1 + sValue2 + sValue2


What's the problem? What should the code be?

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.

Oh, sh... shoot. This is what's in the program.

sTotal = sValue1 + sValue2 + sValue2

sValue2 is there twice. The last variable should be value3, not value2 again.

sTotal = sValue1 + sValue2 + sValue3

I'm such an idiot! I should have seen that!

You found the bug. Good! Fix it, and the program runs just fine.

But you are not an idiot, Adela!

I've been programming since the '70's. For more than 40 f'ing years!


​He's really old.

I have a bachelors degree, two masters, and a Ph.D. Yes, I'm a doctor of geekery!

I make mistakes like this all the time!

Programming is not natural for humans. Not like speaking, or running, or a hundred other things we do. Programming is like nothing else you've ever done. It's a task that our brains are not evolved to handle. Humans evolved to run away from leopards, not to write code.

The reason people will pay you Benjamins to program, is that it isn't easy.

Ah, money!

OK, it's not that hard, honestly, but let's keep that a secret between ourselves.

You will make mistakes, because you have a human brain. Get used to it. And don't beat yourself up over it.

There's not much students can do to make me mad. But there are three things:

  • Picking on other students. That really pisses me off.
  • Cheating, because it wastes a opportunity to learn something worthwhile.
  • Thinking you're stupid.

So, when you find a bug, you're not stupid, just human.


OK, thanks.


Hey, thanks for showing us that, Adela. That was really helpful.


Yeah, seriously. I feel much better.


Me, too.



Goat party planner

Cthulhu throws lavish parties for his goat friends. It made a program to help plan the expenses, but the program doesn't seem to work correctly. It starts like this:


It's supposed to produce this output.


But it doesn't. Download the buggy worksheet, and fix it.

The expenses are supposed to be:

Expense type Per goat Per kid
Food 28 14
Drinks 19 32
Games 18 9
Gifts 12 29
Clean up 17 58

There might be more than one bug.

Upload a corrected workbook. The usual coding standards apply.

Next up: how to avoid bugs in the first place. Better to not have them, than the have them and fix them.