Adela's turn

Let's ask Adela to write a program. One that converts feet and inches to a sensible unit, like meters.

Interface

Adela, off you go!

Adela
Adela

OK, I'll do what Ethan did. Start with the labels, make a place for the code, and add the button.

Start working backwards. I like that idea, Ethan.

Note

I like it, too!

  • Option Explicit
  •  
  • Sub runClicked()
  •     Dim sMeters As Single
  •  
  •  
  •     cell(10, 2) = sMeters
  •  
  • End Sub
Ethan
Ethan

Should that be Cells?

Adela
Adela

Oh, yeah. Thanks!

  • Option Explicit
  •  
  • Sub runClicked()
  •     Dim sMeters As Single
  •  
  •  
  •     Cells(10, 2) = sMeters
  •  
  • End Sub

Adela typed an "s", changing "cell" to "cells". Let's see what VBE (the editor) did:

Code autocorrect

When Adela typed "cell", VBE didn't recognize it. "cell" isn't a thing in VBA. When Adela changed it to "cells", VBE did recognize it. It auto-formatted it, making the c uppercase: "Cells".

You can use this as a quick check of your code. Type code in lowercase, and see if VBE auto-formatted to uppercase. If it doesn't, there's something it doesn't recognize.

An exception is when you declare variables with a Dim. Type the variable name you want in upper- and lowercase, like dim sTotal as single. VBE will change it to Dim sTotal As Single.

Georgina
Georgina

That's neat!

BTW, Adela, there are 2.54 centimeters per inch.

Aye, it is neat.

Georgina
Georgina

BTW, Adela, there are 2.54 centimeters per inch.

Adela
Adela

Thanks, G. I can add the next line, working backwards.

  • Option Explicit
  •  
  • Sub runClicked()
  •     Dim sMeters As Single
  •     Dim sInches As Single
  •  
  •     sMeters = sInches * 2.54
  •     Cells(10, 2) = sMeters
  •  
  • End Sub

Note

Adela added the Dim when she worked out she needed a new variable.

Adela
Adela

OK, so now I need inches. Let's see...

Hey, Kieran, could I do this?

  •     sFeet = from the worksheet
  •     sInches = from the worksheet
  •     sInches = sFeet * 12 + sInches

Aye, you could. You reuse the variable inches.

You could also use two variables for inches, like this:

  •     sFeetInput = from the worksheet
  •     sInchesInput = from the worksheet
  •     sInches = sFeetInput * 12 + sInchesInput

The name sInchesInput suggests the value comes from the user.

Ray
Ray

Which is better?

Doesn't really matter. The second is a bit clearer. In the first code chunk, the meaning of sInches changes slightly in the code, from "a value input by the user," to "total number of inches." In the second, it's clear that one inch value is from the user, and the second one is not. But it doesn't make much of a difference.

Ethan
Ethan

What if you made sInches to sInchesTotal?

Adela
Adela

Ooo, I like that. That's the one I'll do. Thanks, Ethan.

  • Option Explicit
  •  
  • Sub runClicked()
  •     Dim sInchesInput As Single
  •     Dim sFeetInput As Single
  •     Dim sInchesTotal As Single
  •     Dim sMeters As Single
  •  
  •     sInchesInput = Cells(5, 2)
  •     sFeetInput = Cells(6, 2)
  •     sInchesTotal = sFeetInput * 12 + sInchesInput
  •     sMeters = sInchesTotal * 2.54
  •     Cells(10, 2) = sMeters
  •  
  • End Sub

Note

Ethan's idea for naming the variable sInchesTotal makes the purpose of the variable more clear. Naming variables is a Big Deal in programming. It will make your programs easier to think about, and easier to debug.

Adela
Adela

Try it, with one foot...

What?!

Farg! Looks like it did one inch, not one foot!

Bad output

Debugging

Sometimes you can just read the code again, and see an error. But sometimes you can read the code again, and again, and not see the problem.

That's when you use VBE's debugger. It won't fix errors for you. It will let you poke around in the guts of a program, comparing what the program did, with want you want it to.

In VBE, add a breakpoint. That's a place where the program will pause, and let you look inside memory.

Click in the grey area to the left of a statement.

Setting a breakpoint

If you don't know where the error might be, put the breakpoint near the middle of your program.

Now run the program again (click the button you made). The program will stop, before executing the line with the breakpoint.

Memory

The locals window shows you the contents of memory. Each variable, and its value. (If you don't see the locals window, use the View menu to show it.)

Now, let's compare what we want memory to be, with what it is. The worksheet looks like this:

Worksheet

So, feet is one, inches is zero.

Our code grabs feet and inches from the worksheet:

  •     sInchesInput = Cells(5, 2)
  •     sFeetInput = Cells(6, 2)

So, sInchesInput should be 0, and sFeetInput should be 1. Here's what the debugger showed:

Memory

Wait, what?! sInchesInput is 1, and sFeetInput is 0, They're backwards! What the farg?

Here's the code again:

  • Option Explicit
  •  
  • Sub runClicked()
  •     Dim sInchesInput As Single
  •     Dim sFeetInput As Single
  •     Dim sInchesTotal As Single
  •     Dim sMeters As Single
  •  
  •     sInchesInput = Cells(5, 2)
  •     sFeetInput = Cells(6, 2)
  •     sInchesTotal = sFeetInput * 12 + sInchesInput
  •     sMeters = sInchesTotal * 2.54
  •     Cells(10, 2) = sMeters
  •  
  • End Sub
Ethan
Ethan

Oh, I see it! You put the data for feet into the inches variable, and inches into feet. Easy mistake to make.

Oh, yeah. Good work, Ethan! I didn't notice that.

  •     sInchesInput = Cells(5, 2)
  •     sFeetInput = Cells(6, 2)

... should be...

  •     sInchesInput = Cells(6, 2)
  •     sFeetInput = Cells(5, 2)
Adela
Adela

OK, let's try it.

It works!

Note

Adela put feet and then inches in the workbook...

Worksheet

... but inches and then feet in the code...

  • sInchesInput = Cells(5, 2)
  • sFeetInput = Cells(6, 2)

A subtle issue, that might have made the bug more likely. Matching interface and code order might have avoided the problem.

Ray
Ray

Hey, Kieran, I noticed the code is in from the left a bit, except for a few lines. Why?

Oh, right. That's indenting:

Indenting

It shows that the indented code belongs to the Sub.

Indenting is very important when you have decisions and loops. Always indent. The computer doesn't care, but it will make your code easier to understand.

Ethan wrote a program for computing tips. Adela wrote a program for converting from strange units to a sensible one. They found and fixed bugs.

Georgina
Georgina

Hey, those programs... They do different things, but they feel like they're the same kind of thing.

Hmm, you have a point, Georgina. Let's explore that.