If statements

Here's another program. It controls a karaoke machine with one track. OK, it sucks.

This one uses string variables. They have text in them, like "Yo mama!" or "Don't leave your cell phone near Amy Schumer".

Notice the double quotes on lines 8, 9, and 10. They let VBA know where the strings start, and where they end.

Try the program.

Run the program with "dogs" in B1.
Run the program with "cats" in B1.

The program has an If statement. When track equals dogs, lines 9 and 10 are run. If track does not equal dogs, the program skips to line 12. Notice the EndIf at line 11. VBA needs this, so it knows where to skip to when the test is line 8 is false.

Now, run the program, but this time, type "Dogs" into B1. Uppercase first character. "Dogs" not "dogs".

We'll talk about handling case differences later.

`Else`

If you type anything other than "dogs" into B1, you get emptiness in line 1 and 2. Hmm. It'd be better if you got a message saying "Unknown song."

Try it with dog, and other things.

The Else tells the CPU what to do when the If test isn't true. So the program runs lines 9 and 10, or lines 12 and 13.

`ElseIf`

If – Else – EndIf gives you two choices. What if you want more? Use ElseIf:

Try it.

Run the program with "dog" in B1.
Run the program with "cat" in B1.
Run the program with "cupachabra" in B1.

You can add as many options as you want. Test twenty different animals, if you want. Add an ElseIf for each one.

Numeric tests

If statements work with numbers, too, not just strings. Remember the tip program? It lets you compute a tip for a negative meal price. Kind of weird. Let's change it.

Run the program with positive and negative meal prices.

The <= in line 5 means "less than or equal to." Here are some other comparison operators:

=	Equal to
<>	Not equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to

This program does input validation, that is, checking that user input is OK. We'll talk much more about that in this course.

The program shows the error message a bit differently (line 6). Rather than putting the message into a variable, and then putting the variable into a cell, this code puts the message directly into a cell. Either way is OK.

BTW, you could also write the code like this:

If amount > 0 Then
    tip = amount * 0.15
    total = amount + tip
    Cells(2, 2) = tip
    Cells(3, 2) = total
Else
    Cells(2, 2) = "Invalid amount"
EndIf

The test in the If statement changed from amount <= 0 to amount > 0. The statement blocks were flipped around to match.

Klaus

Which one is right?

Tara

They're both right. Neither is better than the other. That's common in programming.

Exercises

Exercise: Favorite animal in Excel

Implement the favorite animal 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.

Upload your Excel workbook.

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

Exercise: Dog advice

Write a program to give advice on how many dogs people should have. The program starts like this:

Start

The user puts a number in cell B1, and clicks the button. Advice appears:

Advice

Use this table:

Dogs	Advice
0	Get some dogs!
1	Get another dog.
2	That's the right number of dogs.
More than 2	You have too many dogs.

Here's some code. You don't have to use it.

Option Explicit

Private Sub cmdRun_Click()
    Dim numDogs As Integer
    Dim advice As String
    'Input.
    numDogs = Cells(1, 2)
    'Processing.
    [Your code here.]
    'Output.
    Cells(4, 1) = advice
End Sub

An Integer is a type of number; specifically, it's a whole number. More on that later.

You can assume that users make no errors when they type in their input.

Upload your solution. The usual coding standards apply.

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

Summary

Use If to control which statements a program runs. Add Else to run one group of statements, or another. Want more than two choices? ElseIf

Iffy variables