Writing the first program

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
Ethan

OK, no problem.

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

Labels

Ethan
Ethan

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

Place for the code

Note

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

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

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

Testing time... yes!

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

Nice job Ethan!

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.