Adela's turn | Programming with Excel VBA

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

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

Should that be Cells?

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

That's neat!

BTW, Adela, there are 2.54 centimeters per inch.

Aye, it is neat.

Georgina

BTW, Adela, there are 2.54 centimeters per inch.

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

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

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

What if you made sInches to sInchesTotal?

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

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

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

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.

Indenting

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.

Adela

Oh, no! I just noticed. The program converts feet and inches to centimeters, not meters! I need to divide the output number by 100 to get meters!

Hey, you're right! I didn't notice that, either. Don't feel bad. It's normal not to notice errors in programs. I've been programming for decades, and didn't see it.

Programming is like that. Mistakes are normal. Finding and fixing them is part of the job.

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

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.