Computing the tip

You’re going to use Eva. It shows you how VBA programs run. Eva doesn’t match exactly how real computers work, but it’s close enough for our purposes.

Let’s start by looking at the pieces of a computer.

Memory

The first is memory, or RAM. Here’s an Excel VBA program, ready to run.

You can see memory in the lower right. Click the “Run next line” button once.

The program takes a piece of memory, and gives it a name: amount. amount is a variable.

That’s what the VBA statement Dim means: grab some memory for a variable. Well, the real word is “allocate,” not “grab,” but the same thing.

Click Run twice more. More variables in memory.

A variable in programming is not the same as a variable in math. In programming, a variable is a place to store data. That’s all.

Variables are key to making programs useful. They let one program work, no matter what the data is.

This is why businesses use computers. They pay to write a program once, then use it again and again and again. Each time, the cost of running the program is almost zero. But the program has to work with different amounts each time.

Here’s Eva again.

Run the program through entirely. You’ll see that it gets the meal amount from cell B1. That happens in line 4:

amount = Cells(1, 2)

Data is copied into the variable amount. Line 5…

tip = amount * 0.15

… uses whatever is in amount to compute the tip. * means multiply.

That’s the key: whatever is in amount. Put different values in the variable amount (by putting different values in B1), and program works with the new data.

Try it. Click the Reset button. Change B1 to something else, like 20. Run the program through again. BTW, if you want the skip an animation (where Eva is showing you data moving around), click Run again while the animation is running. Eva will jump you forward in time to the start of the next statement.

Reset, and try a different value.

The program could get the meal cost from anywhere. This program gets it from a worksheet. It could use a dialog box, like this:

Dialog box

Dialog box

The data could from a database, a file, the Web, a smartphone’s keyboard, doesn’t matter. Once the mean cost gets into the variable amount, the program works.

To sum up:

  • Put data into variables: amount = Cells(1,2)
  • Do computations on variables: tip = amount * 0.15
  • Get data out of variables: Cells(2, 2) = tip

Bugs

Here’s Eva again.

Enter a negative number for meal cost, like -10. Run the program.

Does the result make sense? Kind of. The program did what it was told. But a negative tip doesn’t make sense in the real world.

Reset. Try a word for meal cost, like “dog”. Run the program.

Crash. With a strange error message from the CPU.

The program tries to run, but it’s told to put a word into a variable that can only hold numbers. That’s what Single means in the line: Dim amount as Single: amount can only hold numbers. We’ll talk about data types later.

A crash is anytime a program halts unexpectedly. For most crashes you’ll get in this course, Excel will tell you what the problem is, or at least give you a hint. You fix the bug, and go on.

When we get to loops, you’ll crash Excel itself, so that you lose everything you haven’t saved. Woohoo! What fun!

Not really fun.

Save your code often.

More on bugs and debugging later.

The CPU

Same Eva:

The CPU (central processing unit) is the “brain” of the computer. Here’s a Lego dude looking at a CPU. It’s the chip the arrow is pointing at.

CPU

Your computer has a CPU, but probably not a Lego guy inside.

The CPU is the thing that runs programs. When you write a program, you’re telling the CPU what to do.

The CPU has several components.

CPU

“Run status” shows you whether a program is running. It’s either “running” or “halted.” A program halts because it’s finished, or it crashed.

Programs are a bunch of statements, or lines. “Next line” shows the next statement that the CPU will run. Usually, the CPU executes one line after another, in sequence. If statements, subroutines, and loops let you change that. More later.

BTW, you can change the number in “Next line” while Eva is running. Make it skip statements, and redo statements. Another way to crash programs. Try starting the program at line 4. (Reset, type 4 into Next line, Run.)

The “Evaluator” is where all calculations and memory access takes place. Every calculation has to go through the evaluator.

Lily
Lily
Why is that? I watched Eva doing calculations. It moves data from a variable into the evaluator, does a calculation, then moves the data back. Seems kind of clunky, to have three separate steps.
Tara
Tara
Good question! On a chip, circuits for doing calculations take a lot of space. That makes them expensive, since there isn’t much space available. It’s cheaper overall to have one set of calculation circuits connected to one evaluator, and move data between memory and the evaluator.

Actually, real CPUs have more than one evaluator, maybe 16 or so. But the same principle applies.

BTW, geeks usually call evaluators “registers” or “accumulators.” Evaluators is better for us, since that’s what they do.

Here’s the CPU again:

CPU

“What just happened” explains the program statement that ran, to help you learn. Real CPUs don’t have them.

The program

Eva

The program is under the CPU. That’s what you write.

Let’s run through the program, one chunk at a time. Nothing you haven’t seen already, but going through it again helps it stick in your head. We’ll also point out a couple of extra things.

Declaring variables

The Dim statements declares a variable. Here’s what you see after running a Dim:

Dim

Jeremy
Jeremy
Eva just did the first statement, Dim amount is Single, right? Why is the next line highlighted?

Tara
Tara
VBE – the VBA editor you use to write code – has a debugger. That’s the way the debugger works; it highlights the next line to be executed, not the line that was just executed. Eva mimics the VBE debugger. You’ll see that later.

Now that I think about it, every debugger I’ve used works the same way. It highlights the line about the be executed, not the line that was just executed.

Remember, a variable is a place in memory for storing data. Oh, and it’s temporary. When the program stops running, memory is erased. If you want to keep the data in memory, you need to save it to a disk drive, or somewhere else.

Programming is all about variables. Putting the user’s data into variables. Calculating with them. Moving data from variables to a place the user can see.

Use meaningful variable names, and camel case. That’s part of this course’s coding standards.

Here’s what things look like after the first three statements:

Variables

Get the user’s data

The next line is an assignment statement:

amount = Cells(1,2)

Assignment statements tell the CPU to take the data in the thing on the right of the =, and put it into the variable on the left. The CPU does this in two parts. First, copy the data from the thing on the right into the evaluator, doing calculations along the way (if the program gives any). Second, copy the data to the destination, given on the left of the =.

Look at the statement again:

amount = Cells(1,2)

It tells the CPU to take what the user typed into a cell (row 1, column 2) of the spreadsheet, and copy it into amount.

Input into memory

The user’s data is in memory, ready for the CPU to do calculations on it.

Calculations

The next statement is an assignment statement as well:

tip = amount * 0.15

The CPU takes what’s on the right of the =, and puts it into the variable on the left. The thing on the right is a calculation this time. Take whatever is in the variable amount and multiply it by 0.15. amount has the user’s data, because the previous statement put it there.

* means multiply. Geeks sometimes call * “splat,” because it’s easier to say than “asterisk.”

The next statement:

total = amount + tip

Same thing. The CPU computes the stuff on the right of the =, and puts it into the variable on the left. This makes sense because previous statements filled amount and tip with the right values.

Output

The last two statements show the user the program’s output, by copying data from two variables into the worksheet.

Again, programming is all about the variables. Put data into variables. Calculate with variables. Output variables.

Baby steps

Klaus
Klaus
Each statement is… well, it doesn’t do very much.

Tara
Tara
That’s right. Real programs have thousands of statements. Programmers break down a task into smaller and smaller pieces, until each one can be done with a single statement.

That’s what programming is. Take a task, and break it down into steps that are so small that even a machine with no smarts can do the task.

Of course, you need to understand the task first, before you can tell a computer how to do it.

Exercises

Fill in the blank

How many legs?

Here’s a worksheet:

Workbook

Clicking the Run button runs this code:

  1. Dim emus As Integer
  2. Dim cows As Integer
  3. Dim dogs As Integer
  4. Dim spiders As Integer
  5. Dim totalLegs As Integer
  6. emus = Cells(3, 2)
  7. cows = Cells(4, 2)
  8. dogs = Cells(5, 2)
  9. spiders = Cells(6, 2)
  10. totalLegs = 0
  11. totalLegs = totalLegs + emus * 2
  12. totalLegs = totalLegs + cows * 4
  13. totalLegs = totalLegs + dogs * 4
  14. totalLegs = totalLegs + spiders * 8
  15. Cells(8, 2) = totalLegs
Once the program has run, what value is in cell (8,2)?
Your answer:

Summary

A computer has a CPU. The CPU runs statements (aka instructions), doing what each statement tells it to. Many instructions mess with variables, that is, data in memory.