Nested ifs

Multiple choice

Is veritas true or false?

  • Dim tVino as String
  • tVino = "Last call! "
  • tVino = LCase(Trim(tVino)) & "?"
  • If tVino = UCase("LAST CALL!?") Then
  •     MsgBox "Yo, that's true, no doubt."
  • Else
  •     MsgBox "Nope, no way, no how, that's a no, that's false."
  • EndIf
Saving
A

Yo, that's true, no doubt.

B

Nope, no way, no how, that's a no, that's false.

Multiple choice

Will it float?

  • Dim sWeight as Single
  • sWeight = 2 + 3 * 4
  • If sWeight <= 14 Then
  •   MsgBox "Sure 'n' tootin' it'll float."
  • Else
  •   MsgBox "No, no, no. That'll sink."
  • End If
Saving
A

Sure 'n' tootin' it'll float.

B

No, no, no. That'll sink.

C

Both messages will show.

A nested If is an If inside another If. This is very common.

Old people and prune juice

Some older people have trouble with..., er, their digestive processes. The dance club keeps prune juice for them, in a quiet room in the back. Let's change the code a bit.

  1. Dim sAge As Single
  2. sAge = InputBox("How old are you?")
  3. If sAge >= 21 Then
  4.     MsgBox "Welcome! Come on in. Bring your money."
  5.     ' Test for geezers.
  6.     If sAge >= 50 Then
  7.         MsgBox "There's prune juice in a quite room in the back."
  8.     End If
  9. Else
  10.     MsgBox "Sorry, you're not allowed in yet."
  11. End If

Notice that the geezer test is inside the can-enter-club test. You can put as much code as you want into an If, including other Ifs. The inner If is called a nested If.

Line 8 runs if the test in line 7 is true. Of course, line 7 runs only if the test in line 4 is true.

Line 11 runs if the test in line 4 if false.

Indenting is very important with nested Ifs, otherwise it's hard to tell what goes with what.

The code...

  •         MsgBox "There's prune juice in a quite room in the back."

... is indented twice, because it's inside an If, that's inside another If.

USBs and nested ifs

Thanks to ZamboBambo, a former student

Here's the code again.

  • Dim sAge As Single
  • sAge = InputBox("How old are you?")
  • If sAge >= 21 Then
  •     MsgBox "Welcome! Come on in. Bring your money."
  •     ' Test for geezers.
  •     If sAge >= 50 Then
  •         MsgBox "There's prune juice in a quite room in the back."
  •     End If
  • Else
  •     MsgBox "Sorry, you're not allowed in yet."
  • End If
Reflect

How many message boxes would someone who is 34 see (not including the InputBox)?

How many message boxes would someone who is 74 see (not including the InputBox)?

(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

Someone who's 34 would just see one.

  • If sAge >= 21 Then True for sAge is 34.
  •     MsgBox "Welcome! Come on in. Bring your money." Show this.
  •     ' Test for geezers.
  •     If sAge >= 50 Then Skip...
  •         MsgBox "There's prune juice in a quite room in the back."
  •     End If ... to here.
  • Else

Someone who's 74 would see two.

  • If sAge >= 21 Then True for sAge is 74.
  •     MsgBox "Welcome! Come on in. Bring your money." Show this.
  •     ' Test for geezers.
  •     If sAge >= 50 Then Also true for sAge is 74.
  •         MsgBox "There's prune juice in a quite room in the back." Show this.
  •     End If
  • Else

Great! You got it.

Your debugger friend

Code with If statements can get complex. It's easy to make mistakes. You'll get lost, and frustrated.

The debugger is your friend here. It's critical that you learn to use it well.

Let's practice with this code. Here it is again:

  • Dim sAge As Single
  • sAge = InputBox("How old are you?")
  • If sAge >= 21 Then
  •     MsgBox "Welcome! Come on in. Bring your money."
  •     ' Test for geezers.
  •     If sAge >= 50 Then
  •         MsgBox "There's prune juice in a quite room in the back."
  •     End If
  • Else
  •     MsgBox "Sorry, you're not allowed in yet."
  • End If

Make a worksheet with it. You'll need a button. I'll wait. While I'm waiting, I'll watch some of Cthulhu's goats. You can watch it, too. It's, like, homework. Yeah, that's it.

Here's how I set up the editor.

Editor

I put a breakpoint on one of the lines. You click in the grey area to make one.

Make a breakpoint

Reflect

What does a breakpoint 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

The computer stops running the program there. You can check out the variables.

Right!

Where variables show

Ray
Ray

Hey, wait up. I don't see that window at the bottom.

Turn it on in the menu, with Views | Locals window.

Now, run the program. It will keep running until it hits the breakpoint.

Reflect

When you ran the program, what's the first thing that you saw? The breakpoint, or something else? Why that?

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

Well, the first thing I saw was the dialog asking my age.

Input dialog

The line that showed that is:

  • sAge = InputBox("How old are you?")

That line is before the breakpoint, so I see it first.

Right!

OK, put in an age. I just typed 33 (OK, I'm a lot older than that, I know). This is the next thing I saw:

Paused

VBA stops on the breakpoint, before it's run the highlighted line. You can see what memory looks like at that point.

Now, press F8. That run's one line. Keep pressing it, and watch as your program runs.

You can see what statements VBA runs, and what it skips. For example, I saw this:

What I saw

sAge >= 50 is false. So when I pressed F8 (that is, ran the next line):

Skipped

VBA skipped over the MsgBox, to the End If.

I pressed F8 again, and saw:

Skipped again

Why skip to the End If? sAge >= 21 was true, so it ran the statements inside the If, then skipped the statements inside the Else.

Skippy skippy

Exercise

Exercise

Birthday party planning

Cthulhu like to throw birthday parties for his goatty friends. Help him figure out how many invitations to send.

Your workbook should start off like this:

Start

The user fills in the age and friends cells, and clicks the button. The program shows the number of invitations. Like this:

Output

The number of invitations is the number of friends times two. (Each friend brings a pal.)

For goats 10 years old or older, the number of invitations must be at least 20. So if an old goat has 2 friends, change the number of invitations to 20.

Here's some more sample output.

Old goat, many friends

Old goat, few friends

Hint: nested if.

Upload your worksheet. The usual coding standards apply.