From Eva to Excel

Let’s take the code we’ve seen in Eva, and put it into Excel. Let’s start with the tip calculator.

Eva version

Here it is in Eva. Remind yourself of what the program does.

Excel version

Start up Excel. Configure it using these instructions, from the Tips and tricks section. Go back to the instructions whenever you need to.

Make the worksheet look like this:

We need some way to start our program. One way is to add a button, like this:

Then tell Excel what code to run when the button is clicked.

These instructions (from Tips and tricks) show you how to add a button to your worksheet. Take a look, and add a button to your worksheet.

In a browser, right-click on a link, and choose “Open in new tab” (or whatever it says on your browser). Then you don’t lose your place. (CTRL+click will do the same thing. Thanks to Will for this tip.)

BTW, when you save your workbook, save it as macro-enabled.

1. Dim amount as Single
2. Dim tip as Single
3. Dim total as Single
4. amount = Cells(1,2)
5. tip = amount * 0.15
6. total = amount + tip
7. Cells(2, 2) = tip
8. Cells(3, 2) = total
The button instructions tell you where to put the code. My button is called cmdRun, so I have:

Save your work, go back to the worksheet, and try it. Remember that you’ll need to turn design mode off before you can click the button. That’s on the Developer tab.

If you have trouble, download my worksheet, and compare it with yours.

Looking inside the program

One of the nice things about Eva is that you can watch programs run. You can see inside memory and the CPU.

You can do that in Excel, too. Follow along.

It’s very important that you learn how to look inside your programs. It will save you a lot of screaming. If you have to, download my worksheet, and use the debugger on that.

Start by going to your code (ALT+F11, remember).

Tell VBE (the Visual Basic editor) that you want to use the debugger. The debugger will let you step through the code line by line, just like Eva. Click in the gray margin to the left of a line of the code. That sets a breakpoint:

You can’t set a breakpoint on a Dim statement that declares a variable.

Run your program by clicking the “Run” button you put on your worksheet. You’ll see:

Like Eva, Excel stops before it executes the line with the breakpoint.

Put your mouse cursor on amount. You’ll see:

You can also see variables by opening the Locals window. In the VBE menu, select View | Locals. You’ll see:

Press F8 to run one line. That’s like clicking Eva’s “Run next line” button.

Keep pressing F8, and the program will run line by line. You can watch the variables changing.

Example

Lily
Why is this so important?
Tara
Someday soon, your program won’t work. It’s supposed to output 14, but instead outputs 86. You’ll have to figure out why it’s showing the wrong number, and fix it.

The easiest way to do that is watch the values in the variables. Compare what values you expect, to what the debugger says is there.

Let’s say you make a worksheet like this:

When you click the button, you want the program to add X1 and X2, double it, and put the result in cell B4. That would be:

4 + 3 is 7

7 * 2 is 14

You click Run, and see:

Argh! You need to fix your code. Here it is:

Maybe the program is getting data from the wrong cell for x1. Or maybe getting the wrong data for x2. Maybe the calculation is wrong. Maybe the output is wrong.

Let’s start by checking whether the program got x1. Put a breakpoint on the line after the one that sets x1. Recall that the debugger stops before running the line with the breakpoint, so the line that sets x1 will just have been run. Run the program:

We expect x1 to be 4. The locals window says that, yes, x1 is 4. So the problem isn’t in the line that gets x1.

Let’s check the next line, the one that gets x2. Press F8, so the debugger runs one line:

We expect x2 to be 3, and the locals window says that it is. Haven’t found the problem yet.

Check the next line. Press F8 again:

We expect doubleSum to be 14. Check the locals window, and it’s… 86! Aha! The problem is on the line that computes doubleSum.

But what’s wrong? Hmmm. Suppose we highlight the first part of the expression on the right of the =:

Huh? x1 and x2 is 43? Wait, it’s "43". What’s with the quotes? Numbers don’t have quotes!

Do you know what’s going on? Download my program, fix it, and see if you’re right.

Tara
The debugger won’t fix the problem for you. It helps you find where the problem is. Home in on the line where the value of a variable isn’t what you expect it to be.
More debugging later in the course.

Messing with variables

Eva lets you change variables while the program is running. VBE lets you do the same. Open up the Immediate window. CTRL+G, or View | Immediate Window in the menu.

Run the program again, and get to:

amount has been read from cell B2, which has 15 in it. Suppose you want to set amount to 30. Here’s what you could do:

You can type VBA into the immediate window. ? is short for Print, so ? amount (and press Enter) shows the value of the variable amount. Then you type:

amount = 30 (Enter)

That’s a normal VBA statement. It takes the stuff on the right of the =, and puts it into the variable on the left. Put the mouse cursor on the variable, and you can see its new value.

Keep running the program a line at a time (with F8). Your code will use the new value for amount. Of course, the worksheet will have the wrong answers now.

Change the code

Eva doesn’t let you change the code. VBE does. Change the tip rate to different values, instead of 15%. See if the program works as you expect.

Sometimes VBA has to stop running the program before you can change it. VBA will ask you if that’s OK. When you see this…

… that’s what the debugger is asking. Click OK, and your program will stop.

The VBE debugger does more than we’ve seen here. Try the menu items, check help, and look online.

Coding standards

Coding standards are rules for writing code. They help all the humans on the project understand your work. We have a few coding standards in this course.

Variable names

Let’s look at that code again:

We can use any variable names we want. Excel doesn’t care. For example:

Ouch! That code is hard to understand.

Always use variables names that match the purpose of the variables in the program. For the tip program, amount is a better variable name than e29.

Always use meaningful variable names.

Use camel case, as explained here.

Indenting

Here’s the code again:

The code inside cmdRun_Click() is indented. It helps you remember when the code is run: when the button is clicked. Later, as we add more Subs, nested Ifs, and loops, indenting is essential. It helps you understand, test, and debug code.

Option Explicit

This should be the first line in your program. If it isn’t there, you’ll waste time finding misspelled variables names. Can lead to screaming.

See the tip about declaring variables for more.

Always use Option Explicit.

Earlier, you learned about program structure. Programs are written in chunks, like Input, Processing, and Output. It’s a good idea to add a comment identifying the start of each chunk. For example:

Compare with:

A quick glance at the comments, and you know how the code is organized.

The comments and blank lines help identify the chunks that make up the program. The blank lines are optional, but you should add a comment for each chunk.

Add a comment for each program chunk.

The standards are summarized in this tip. Refer to it when you need a reminder.

Exercises

Exercise: Aussie rules in Excel
Implement the Aussie rules example program in Excel. You can copy the code from Eva.

Remember: Option Explicit, good variable names, indenting, and chunky comments.

Here’s a tip on how to add a Run button. Remember to save your Excel file as an XLSM.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Ohm’s law
Ohm’s law is:

V = IR

where

V: voltage, measured in volts.
I: current, measured in amps.
R: resistance, measured in ohms.

Write a program in VBA that computes V, given I and R. For example:

Remember: Option Explicit, good variable names, indenting, and chunky comments.

Here’s a tip on how to add a Run button. Remember to save your Excel file as an XLSM.