The first program

At the spa
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?

Adela
Adela

Well, I'd take the meal cost, whatever it was. What is it?

Uh, let's say $25.

Adela
Adela

OK. The tip is 25 × 0.15. That's 3.75.

Then I'd take the tip, 3.75, and add it to the meal cost, of 25.

That would give a total, 28.75

Great!

  • Multiply meal cost by 15%.
  • Then add to meal cost to get the total to pay.

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 runs a program

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

  • Multiply meal cost by 15%.
  • Then add to meal cost to get the total to pay.

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

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.)

  • Ask a human for a number. Put that in [meal cost].
  • Multiply [meal cost] by 0.15. Put that in [tip amount].
  • Add [meal cost] and [tip amount]. Put that in [total].
  • Tell the human [tip amount] and [total].

It's much longer than Adela's program:

  • Multiply meal cost by 15%.
  • Then add to meal cost to get the total to pay.

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.

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.

  • Ask a human for a number. Put that in [meal cost].
  • Multiply [meal cost] by 0.15. Put that in [tip amount].

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.

  • Ask a human for a number. Put that in [meal cost].
  • Multiply [meal cost] by 0.15. Put that in [tip amount].
  • Add [meal cost] and [tip amount]. Put that in [total].
  • Tell the human [tip amount] and [total].
Ray
Ray

OK, so what if we took out part of the first line, the piece that put the human's data into a variable.

  • Ask a human for a number. (Something deleted.)
  • Multiply [meal cost] by 0.15. Put that in [tip amount].

Would the program work?

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

Ethan
Ethan

But, it would still ask for a number?

Yes, because the program says to:

  • Ask a human for a number.
  • Multiply [meal cost] by 0.15. Put that in [tip amount].
Ethan
Ethan

What would it do with the number it got from a human?

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

Ray
Ray

Wow, that's super-duper stupid.

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.

Adela
Adela

Would the computer know something was wrong with the program?

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:

  • Multiply [meal cost] by 0.15. Put that in [tip amount].

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:

Before entering data

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

Input

The computer works out the answers, and outputs them:

Output

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:

  • Sub DemonGoat()
  •  
  • 'Code goes here.
  •  
  • End Sub

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:

View code

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:

VBE

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.

Opening the Project Explorer

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

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

Where code can go

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

Where to put code

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

Sheet1

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

Code

Ray
Ray

What happens if I accidentally put code in one of the other places?

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.

Cutting code

VBE starts

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

Require Variable Declaration option

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:

  • Sub runClicked()
  •   Dim sMealPrice As Single
  •   Dim sTipAmount As Single
  •   Dim sTotal As Single
  •   ' Input meal price.
  •   sMealPrice = Cells(1, 2)
  •   ' Compute tip and total.
  •   sTipAmount = sMealPrice * 0.15
  •   sTotal = sMealPrice + sTipAmount
  •   ' Output.
  •   Cells(5, 2) = sTipAmount
  •   Cells(6, 2) = sTotal
  • End Sub

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.

Add button

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:

Assign macro

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

Choose your sub

Adela
Adela

I notice it says Sheet1.runClicked.

Right! Good noticing.

Sheet1.runClicked

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:

Button

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:

Design mode

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

Edit button text

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

Changing button text

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.

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:

Save as 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:

Warning

Warning options

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

  • Dim sMealPrice As Single
  • Dim sTipAmount As Single
  • Dim sTotal As Single
  • ' Input meal price.
  • sMealPrice = Cells(1, 2)
  • ' Compute tip and total.
  • sTipAmount = sMealPrice * 0.15
  • sTotal = sMealPrice + sTipAmount
  • ' Output.
  • Cells(5, 2) = sTipAmount
  • Cells(6, 2) = sTotal

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.

Georgina
Georgina

I'm guessing that the s in the variable names, like sTotal, has to do with them being Single?

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

After these lines run, computer memory looks like this:

Variables declared

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.

  • Dim sMealPrice As Single
  • Dim sTipAmount As Single
  • Dim sTotal As Single
  • ' Input meal price.
  • sMealPrice = Cells(1, 2)
  • ' Compute tip and total.
  • sTipAmount = sMealPrice * 0.15
  • sTotal = sMealPrice + sTipAmount
  • ' Output.
  • Cells(5, 2) = sTipAmount
  • Cells(6, 2) = sTotal

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:

Assignment

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

Here's that line again:

  •     sMealPrice = Cells(1, 2)

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."

Adela
Adela

So the = is a bit confusing, then.

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

  •     sMealPrice <- Cells(1, 2)

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:

  •     to_here = from_here

Computation

Here's the code again:

  • Dim sMealPrice As Single
  • Dim sTipAmount As Single
  • Dim sTotal As Single
  • ' Input meal price.
  • sMealPrice = Cells(1, 2)
  • Up to here
  • ' Compute tip and total.
  • sTipAmount = sMealPrice * 0.15
  • sTotal = sMealPrice + sTipAmount
  • ' Output.
  • Cells(5, 2) = sTipAmount
  • Cells(6, 2) = sTotal

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

Let's look at the next three lines:

  • Dim sMealPrice As Single
  • Dim sTipAmount As Single
  • Dim sTotal As Single
  • ' Input meal price.
  • sMealPrice = Cells(1, 2)
  • ' Compute tip and total.
  • sTipAmount = sMealPrice * 0.15
  • sTotal = sMealPrice + sTipAmount
  • ' Output.
  • Cells(5, 2) = sTipAmount
  • Cells(6, 2) = sTotal

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.

Adela
Adela

So the * means multiply?

Yes, it does.

Adela
Adela

A question. The code is:

  • sTipAmount = sMealPrice * 0.15
  • sTotal = sMealPrice + sTipAmount

Do we need the variable sTipAmount? Could we do this instead?

  • sTotal = sMealPrice + sMealPrice * 0.15

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:

  • Dim sMealPrice As Single
  • Dim sTipAmount As Single
  • Dim sTotal As Single
  • ' Input meal price.
  • sMealPrice = Cells(1, 2)
  • ' Compute tip and total.
  • sTipAmount = sMealPrice * 0.15
  • sTotal = sMealPrice + sTipAmount
  • ' Output.
  • Cells(5, 2) = sTipAmount
  • Cells(6, 2) = sTotal

What's this code do?

Ray
Ray

Two assignment statements. Take what is on the right, and put it into what is on the left. The things on the left are cells on the worksheet.

So this code copies the variables sTipAmount and sTotal into the worksheet, so the user can see them.

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:

  •     Dim sMealPrice As Single
  •     Dim sTipAmount As Single
  •     Dim sTotal As Single
Reflect

What does the code do?

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.
Ray
Ray

The computer makes three variables in its memory, with the names given. sMealPrice, sTipAmount, and sTotal.

Good. Next:

  •     sMealPrice = Cells(1, 2)
Reflect

What does this code do?

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.
Georgina
Georgina

Take the value in the cell at row 1, column 2. Put it in the variable sMealPrice.

That's an assignment statement.

It's how the program gets input, data from the user.

Nice. Next:

  •     sTipAmount = sMealPrice * 0.15
  •     sTotal = sMealPrice + sTipAmount
Reflect

What does this code do?

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.
Ethan
Ethan

Take whatever is in the variable sMealPrice, and multiply it by 0.15. Put the result in sTipAmount.

Then take what is in sMealPrice, add sTipAmount, and put that in sTotal.

Excellent. Last bit:

  •     Cells(5, 2) = sTipAmount
  •     Cells(6, 2) = sTotal
Reflect

What does this code do?

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.
Adela
Adela

Take sTipAmount, and put its value into the worksheet cell at row 5, column 2. Then put sTotal into row 6, column 2.

Now that the values are in the worksheet, the user can see them. That's the program's output.

Yes! Great job, everyone!