Writing the first program | Programming with Excel VBA

In the last lesson, you saw code for the tip program. But how would you write that code from scratch?

Ethan tips

Let's watch Ethan write the program.

Ethan

OK, no problem.

Let's see... well, I'll set up the labels. That's easy.

Labels

Ethan

Now I'll make a place for the code. A Sub.

Place for the code

Note

Notice the Option Explicit. If it isn't there, type it in.

Ethan

Now for the code. Let's see...

Well, I know I'll have to put output in cell B6 at the end, so I'll put that in.

Option Explicit
Sub runClicked()
Cells(6, 2) =
End Sub

Note

Ethan is writing the code backwards. That's a great idea! Start with what you want the program to output, and work backwards towards the input.

Ethan

Let's see, I need a variable name. It's a number, so it starts with s. sTotal sounds good.

Option Explicit
Sub runClicked()
Dim sTotal As Single
Cells(6, 2) = sTotal
End Sub

Note

Total is a good name. It tells you what the variable means. Add an s, because it's a Single.

Ethan added a Dim once he knew he needed the variable. Made sure he didn't forget it.

Using the first letter of a variable's name to show its data type is called Hungarian naming, after a Hungarian dude at Microsoft who started doing it. We only use three data types in this course, and so three first letters:

s for single, like Dim sGoatTotal As Single
t for string (a text variable), like Dim tGoatName As String
r for range, like Dim rGoatDataSet As Range

There are many more data types, but we won't use them.

Ethan

Oh, wait, I need to output the tip as well, in cell B5.

Option Explicit
Sub runClicked()
Dim sTip As Single
Dim sTotal As Single
Cells(5, 2) = sTip
Cells(6, 2) = sTotal
End Sub

Ethan

OK, to work out the tip I need to know the cost of the meal. So...

Option Explicit
Sub runClicked()
Dim sMealCost As Single
Dim sTip As Single
Dim sTotal As Single
sTip = sMealCost * 0.15
Cells(5, 2) = sTip
Cells(6, 2) = sTotal
End Sub

Note

Working backwards again. That's a Good Thing.

But... something's missing.

Ethan

OK, so the meal cost comes from... oh, cell B1.

Option Explicit
Sub runClicked()
Dim sMealCost As Single
Dim sTip As Single
Dim sTotal As Single
sMealCost = Cells(B, 1)
sTip = sMealCost * 0.15
Cells(5, 2) = sTip
Cells(6, 2) = sTotal
End Sub

Note

Ethan made a mistake here. Try to find it.

Ethan

Now I'll make a button, and tell Excel to run runClicked when the button is clicked.

Done

OK, now to test it. I'll start with a meal cost that's easy to work out the tip for.

Test

Note

Use easy-to-compute values to test your code. Easy is Good.

Ethan

Click the button, and... farg! It didn't work. I got an error.

Variable not defined

Note

For some errors, you'll get a message from VBA. Sometimes not, depending on the error. Errors with messages are easier to fix.

Ethan

The B is highlighted. There's a cell B1... Oh, wait! When you use Cells, you use a number for the row, and a number for the column. It should be...

sMealCost = Cells(1, 2)

Put that in... OK, try again.

Hey! It's not running!

Break mode

Note

When a program fails, VBA might go into break mode. If you see yellow text like that, the program has stopped, waiting for you to tell it what to do.

To exit break mode, use the Reset button in the toolbar. The one that looks like a stop button.

Ethan

Try it again...

Argh! It doesn't work!

No total

Note

Programming is frustrating, since computers are so stupid. What Ethan's going through is normal. Even for me.

The .0000006 is rounding error. Forget it for now.

The total is zero. That's a problem.

Here's the code again:

Option Explicit
Sub runClicked()
Dim sMealCost As Single
Dim sTip As Single
Dim sTotal As Single
sMealCost = Cells(1, 2)
sTip = sMealCost * 0.15
Cells(5, 2) = sTip
Cells(6, 2) = sTotal
End Sub

Ethan

OK, let's see. Total isn't right. I'll look for the code that works out total. It would be like sTotal = something...

Wait! It isn't there!

Note

Since total wasn't right, Ethan focused on the code that computed total. Then he found the problem.

Backwards thinking again:

The output was wrong.
Work backwards: find the code making the output.

Ethan

I'll add in a line...

Option Explicit
Sub runClicked()
Dim sMealCost As Single
Dim sTip As Single
Dim sTotal As Single
sMealCost = Cells(1, 2)
sTip = sMealCost * 0.15
sTotal = sMealCost + sTip
Cells(5, 2) = sTip
Cells(6, 2) = sTotal
End Sub

Ethan

Testing time... yes!

Now I'll change the meal cost to 10. Does it work? ... Yes!

Nice job Ethan!

Ethan

Even though I made all those mistakes?

You made two mistakes. Not a lot.

Thing is, you kept at it, found the errors, and fixed them. So, yes, you did well.

I've been programming for a long time. Decades. I'm really old. I still make mistakes like yours. All the time.

Good programming isn't about not making mistakes. You will make them. I make them. Your friends make them. Goats make them.

Yep, we do

Yep, we do.

Winston Churchill, British prime minister during WWII, said:

Success is not final, failure is not fatal: it is the courage to continue that counts.

The British had their backs to the wall. Defeat by the Nazis was almost certain. They didn't give up, though. And they won in the end.

Keep on going. Remember, if you need help, ask.

Exercise

Do this exercise. We talked about how to submit exercises, if you need a reminder.

Exercise

Custom tip amount

Change the tip program, so that the user can enter a custom tip amount. Instead of 0.15, the user can enter whatever s/he wants.

The worksheet could start like this:

Start

The user clicks the button and sees:

15% tip

If the user changes 0.15 to 0.2 and clicks the button:

20% tip

Hints:

Start by adding a row for tip rate on the worksheet.
Add a new variable to the code.

Upload your worksheet.

If you were logged in as a student, you could submit a solution to this exercise.