The first program

At the spa
Photo: Holly Vickery

Working out the tip in a restaurant

Say we want a program that will figure out the tip for a meal, in an American restaurant. It's usually 15%.

Adela, how do you compute a tip?

Uh, let's say $25.

Great!

That's a program, a set of instructions Adela used to do the task.

To do the task, Adela had to get some data: the meal cost. Then she could do her calculations. The last step of Adela's program was to tell us what the result was.

Adela's smart, so the program could be a bit sloppy, and she could work out what to do. Here's the program again:

The program doesn't tell Adela to ask us for the meal cost. She figured out that she needed that, though, and asked. The program didn't say she should tell us the results, but she worked out that she should.

Writing the program for Adela to run is pretty easy.

Computers are stupid

We can't use Adela's program with a computer, because computers are really, really, really stupid. They need every step spelled out. Miss one, or get it wrong, and the program won't work.

Truth Goat: You won't believe how stupid computers are.

Here's a program that a computer might run. (It's pseudocode, not in a real programming language.)

It's much longer than Adela's program:

Why is it longer? Because you need to tell the computer all the deets, or the program won't work.

That's what programmers do. Tell a stupid stupid stupid machine all the deets to work something out.

You can run the program with this button. Try it, with a number, like 50.

Click me

Now try it again, but this time type the word "fifty" instead of the number 50. Will the computer be smart enough to know what you wanted?

Nope. Stupid computer.

Variables

Computers are so stupid, they forget what they did, from one moment to the next. Here are the first two lines of the program again.

By the time the computer starts running the second line, it's forgotten about the first.

[meal cost] and [tip amount] are variables. A variable a tiny chunk of computer memory, with a name.

[meal cost] links the two lines together. The first line puts something into [meal cost]. The second gets something out of [meal cost].

Ray wonders how stupid computers are

Here's the program again.

Well, it would run. It wouldn't get the right answer, since [meal cost] would stay at zero.

Yes, because the program says to:

Throw it away, because the code doesn't tell it to do anything with the number.

Yes.

Many bugs are from programmers forgetting how stupid computers are. They miss steps that are so obvious to humans that we don't even think about them. But they must be spelled out to computers.

Ooo, good question! No, it wouldn't. It would run happily, giving wrong answers every time.

So far...

• A program is a set of steps that a computer runs.
• Programmers use variables to link the steps together.
• Computer are stupid.

One more thing. The 0.15 in the line:

That's called a constant, since it's a value that never changes.

To Excel

Let's Excelize that. We want a worksheet that starts like this:

The user types a number into cell B1, then clicks the "Compute tip" button.

The computer works out the answers, and outputs them:

Let's make that.

Start Excel, and open a blank workbook. Put the labels in cells A1, A5, and A6.

You turned on the developer tools, right? You saw how to do that earlier.

Now we'll:

• Make a place for some code to live.
• Make a button that runs the code.

Make a sub

A sub, or subroutine, is a chunk of code with a name. Like this:

You can link a sub to a button, so what when the button is clicked, the sub runs.

There's a special tool you'll use to type in code. It's called the Visual Basic Editor (VBE). One way to get to VBE is with View Code in the Developer tab:

I prefer to use the keyboard shortcut. On Windows it's Alt+F11. There's a similar shortcut on the Mac.

VBE opens, and looks something like this:

The window on the left is called the Project Explorer. Best to keep that open. If it's not there, or you accidentally close it, you can open it with the View menu.

Notice the shortcut, Ctrl+R. Shortcuts are good.

The Project Explorer shows where you can put your subs. There are three common places:

Let's put all of our subs inside the sheet.

Double-click on Sheet1, and you'll see an editor for subs for Sheet1.

Here's what the editor will look like when there's some code in it.

No problem. Cut-and-paste the code into the Sheet1 place. Make sure you get the whole thing, from the Sub to the End Sub.

The line Option Explicit should be there, because you set the Require Variable Declaration option earlier:

If you forgot to set the option, do it now with Tools | Options (select Options from the Tools menu). If you still don't see Option Explicit, type it in.

Important! Option Explicit should be the first line in every program. It helps avoid bugs. We'll talk about why later.

Put this code after Option Explicit:

We'll go through it in a bit.

Notice the code lives inside a Sub, named runClicked. All of our code will live in Subs.

OK, now your code has a home. Onward!

pause,

Making a button to run the code

Go to the Developer tab. Click Insert, and choose Button from the form controls.

Then click in the worksheet where you want the button to appear. You can move the button later, if you want.

You'll get a dialog window like this:

You want runClicked to run when the user clicks the button. Click on runClicked, and then click OK:

Right! Good noticing.

That's because an Excel workbook can have more than one worksheet. We'll usually have just one, called Sheet1.

After you click OK, you'll see the button on your worksheet. Something like this:

Now you've linked the button to your code. So when someone clicks on the button, runClicked will run.

Give it a try. Put in a meal amount, and click the button.

The button caption

We want the word Run on the button, so let's fix that.

Go into design mode:

Right-click on the button, and select Edit text:

This will let you set the button's caption. Change it to Run.

The small circles show that you are editing the button. When you're done, click off the button, somewhere in the worksheet.

To move a button, enter design mode, right-click on the buttin, and then press Escape to hide the menu. Move the mouse around until you see a move arrow.

Now drag the button where you want it.

Excel workbooks with VBA in them need to be saved in a special way. When you save, choose Excel Macro-Enabled Workbook:

Notice the extension: xlsm.

If you save a workbook as a regular Excel file (extension: xlsx), you could lose your code. Argh! Don't do that.

When you open an XLSM file, you'll get warnings:

You get to decide whether you trust the file. If it's code you wrote, it should be safe. Unless you're trying to hack yourself.

How the code works

The first three lines tell the computer that we need three variables, what the names of the variables are, and that we want to put numbers in them (that's what Single means; more later). This is called declaring the variables.

Right. It's called Hungarian naming. More later.

After these lines run, computer memory looks like this:

Variables in memory

When you declare a variable, VBA automatically sets the variable's value to 0. Not all languages do this. In those languages, you have to set the value to 0 yourself.

Input

Let's check out the next two lines.

The first one is a comment. It explains to a human what the code below it does. Comments begin with a single quote ('). The computer ignores everything after the quote.

The next line says "Take what is in the cell at row 1, column 2, and put it into the variable sMealPrice. If the cell at row 1, column 2 had 25 in it:

Copy a value from a cell in a worksheet, into a variable in memory

Here's that line again:

Important! The = does not mean what it means in math. It's something else entirely in programming. It says: "Take the value of the thing on the right, and copy it into the thing on the left."

Yes, I agree. It would better if it looked like an arrow:

That's clearer: take the value of the thing on the right, and copy it into the thing on the left.

That's not the way VBA is, though. You have to use =.

This is called an assignment statement, since it assigns a value to a variable. So an assignment statement always has the form:

Computation

Here's the code again:

We talked about declaring variables, and getting the user's input into a variable.

Let's look at the next three lines:

A comment, then more assignment statements. Take the thing on the right, and copy it to the thing on the left.

The thing on the right is an expression. It's a computation of some kind.

Yes, it does.

Yes! Great thinking, Adela! We could do that.

We wouldn't have sTipAmount, though, which we need for the output.

Output

Let's look at the last bit of the code:

What's this code do?

Right! Those lines output the variables' values.

Once more with feeling

OK, let's go through the code again. Explain what the pieces of code do, in your own words. Then compare it with what the virtual students think.

Here's some code:

Good. Next:

Nice. Next:

Excellent. Last bit:

Yes! Great job, everyone!