Multiway

So far, our Ifs have been working with two cases, either this, or that. Often, there are more than two. What do we do then?

Goat fashions

Cthulhu sponsors an event each year during Little Rock Fashion Week. LRFW is a key event in the Arkansas fashion scene.

Fancy goat Another fancy goat Yet another fancy goat

There are four goatwalks (catwalks, but not) running simultaneously, with different breeds on different goatwalks. Here are the assignments:

Breed Goatwalk
Australian Cashmere A
Sarda B
Toggenburg B
Fainting C
Alpine C
Kiko A
Hongtong C
All others D

Here's one way to write the code:

  • Dim tBreed As String
  • Dim tGoatWalk As String
  • Dim tMessage As String
  •  
  • tBreed = InputBox("Breed?")
  • tBreed = LCase(Trim(breed))
  • If tBreed = "australian cashmere" Then
  •     tGoatWalk = "A"
  • ElseIf tBreed = "sarda" Then
  •     tGoatWalk = "B"
  • ElseIf tBreed = "toggenburg" Then
  •     tGoatWalk = "B"
  • ElseIf tBreed = "fainting" Then
  •     tGoatWalk = "C"
  • ElseIf tBreed = "alpine" Then
  •     tGoatWalk = "C"
  • ElseIf tBreed = "kiko" Then
  •     tGoatWalk = "A"
  • ElseIf tBreed = "hongtong" Then
  •     tGoatWalk = "C"
  • Else
  •     tGoatWalk = "D"
  • End If
  •  
  • tMessage = "You are on goatwalk " & tGoatWalk & "."
  • MsgBox tMessage

The ElseIf is the key. It lets you test for a bunch of different options.

There's an Else at the end. It's the default. If none of the others match, then the goat will end up on goatwalk D.

Adela
Adela

We could reduce the amount of code, with Ors, right?

Yes, you're right. Here's another way to write the If.

  • If tBreed = "australian cashmere" Or tBreed = "kiko" Then
  •     tGoatWalk = "A"
  • ElseIf tBreed = "sarda" Or tBreed = "toggenburg" Then
  •     tGoatWalk = "B"
  • ElseIf tBreed = "fainting" Or tBreed = "alpine" Or tBreed = "hongtong" Then
  •     tGoatWalk = "C"
  • Else
  •     tGoatWalk = "D"
  • End If

Either way would be fine.

Numeric multiways

The last example used string tests, like:

If tBreed = "australian cashmere" Then

You can also use numeric tests, like:

If sFollowers < 555 Then

Tests like this often are used to lookup values in a table. Let's see an example of that.

Goats attending one of Cthulhu's huge events stay in condominimums. They're large crates, grouped in circles. A condominimum is like a condominium, but smaller. Goats are happy with less.

Each condominimum has a straw bed, straw for snacking, a drinking bucket, and other amenities. The central area is used for socializing, and goaty games, like Butt My Butt.

Condominimums come in three styles:

  • Basic Billy, a minimum condominimum
  • Comfy Cylde, better straw, and a bigger bucket
  • Luxury Lucy, stylin'!
  • One percent Juan, all the trimmings, including edible trimmings

Goats are allocated a condominimum based on their number of BaaBaaa followers. Like Twitter, but, you know, goats. Here's a table, for looking up the condominimum based on the number of followers.

Followers Condominimum
Less than 555 BB
Between 555 and 5,555 CC
Between 5,555 and 55,555 LL
More than 55,555 OPJ
Adela
Adela

Have lots of coffee today? This goat story is... creative.

Why, yes, I did have quite a lot of coffee. Good stuff! Good! GOOD! GOOOOD! ARGHGHGHGH!

Let's figure out a goat's condominimum allocation, based on its followers. We'll read the number of followers from a CSV file. Just because.

Ooo! Let's get the name of the file from the user, in an InputBox.

Georgina
Georgina

OK, waaaay too much coffee.

There is no such thing as too much coffee! No! NO! NOOOOOO!

  • Dim tFileName As String
  • Dim sFollowers As Single
  • Dim tCondo As String
  • Dim tMessage As String
  •  
  • 'Input
  • tFileName = InputBox("Data file name (e.g., data.csv)?")
  • Open ThisWorkbook.Path & "\" & tFileName For Input As #1
  • Input #1, sFollowers
  • Close #1
  •  
  • 'Glue variable: followers
  •  
  • 'Processing
  • If sFollowers < 555 Then
  •     tCondo = "Basic Billy"
  • ElseIf sFollowers < 5555 Then
  •     tCondo = "Comfy Cylde"
  • ElseIf sFollowers < 55555 Then
  •     tCondo = "Luxury Lucy"
  • Else
  •     tCondo = "One percent Juan"
  • End If
  •  
  • 'Glue variable: tCondo
  •  
  • 'Output.
  • tMessage = "Condominimum: " & tCondo
  • MsgBox message

Let's check out the structure of the program. It's IPO, that is, uses the input-processing-output pattern. We've identified the glue variables.

Reflect

What is a glue variable?

(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

A glue variable links two parts of a program together.

Right! The Input part of the IPO program sends data to the Processing part.

  • 'Input
  • tFileName = InputBox("Data file name (e.g., data.csv)?")
  • Open ThisWorkbook.Path & "\" & tFileName For Input As #1
  • Input #1, sFollowers Put data into the glue variable
  • Close #1
  •  
  • 'Glue variable: followers
  •  
  • 'Processing
  • If sFollowers < 555 Then Use data that's in the glue variable

The Processing part sends data to the output part.

  • 'Processing
  • If sFollowers < 555 Then
  •     tCondo = "Basic Billy" Put data into the glue variable
  •     ...
  • End If
  •  
  • 'Glue variable: condo
  •  
  • 'Output.
  • message = "Condominimum: " & tCondo Use data from the glue variable
Ray
Ray

I forget, why are we talking about glue variables?

Anyone want to try answering that?

Adela
Adela

It's about making programming easier on our brains. Our programs are getting more complex, so making it easier means fewer bugs, quicker code writing, and less frustration.

When I write a program, I plan it first, using comments. So I might start like this:

  • 'Input
  •  
  • 'Glue variable: sFollowers
  •  
  • 'Processing
  •  
  • 'Glue variable: tCondo
  •  
  • 'Output.

So, I've broken the program into three pieces. The comments tell me what the glue variables are, that will stick the pieces together.

Marcus
Marcus

Oh, that's so cool! I can see how you're planning it out. Like Georgina did earlier. She used Needs and Provides, from patterns. This is similar, but your own way.

Note

Georgina and Adela plan in slightly different ways, but:

  • They both plan before coding.
  • They both decompose a program into chunks, and think about how the chunks fit together.
Principle

Decomposition

Break a task into smaller tasks, do each smaller task, and put them back together.

Adela, what's next?

Adela
Adela

OK, so check out the first part:

  • 'Input
  •  
  • 'Glue variable: followers

Now I can plan the input section, without having to think about processing. My goal is to put data into sFollowers. That's all I have to think about.

It's easier on my brain when I can just focus on one thing.

I'd break Input down like this:

  • 'Input
  •  
  • 'Get the file name
  • 'Get the followers from the file
  •  
  • 'Glue variable: sFollowers
Ray
Ray

Sorry, but can I make a guess about the next step?

Adela
Adela

Go ahead.

Ray
Ray
  • 'Get the file name
  •  
  • 'Glue variable: tFileName
  •  
  • 'Get the followers from the file

D00d! That is so righteous! You can keep breaking things down, writing out the glue variables that link things together.

Hey, Marcus! What's next?

Marcus
Marcus

OK, just thinking out loud here. We have:

  • 'Get the file name
  •  
  • 'Glue variable: tFileName
  •  
  • 'Get the followers from the file

We're going to get the file name from the user, right? You said that before.

Flashback

Doodly doo, doodly doo... (that's flashback music)

Ooo! Let's get the name of the file from the user, in an InputBox.

Doodly doo, doodly doo...

Marcus
Marcus

OooooK, so, take...

  • 'Get the file name
  •  
  • 'Glue variable: tFileName

... and do it like this...

  • 'Get the file name
  • tFileName = InputBox("Data file name (e.g., data.csv)?")
  • 'Glue variable: tFileName

Great! Decomposition again. Break the program into IPO pieces, then break the input piece down into smaller pieces, than write the first bit o' code.

What's next? Adela?

Adela
Adela

Well, we could finish off the input section.

  • 'Input
  • 'Get the file name
  • tFileName = InputBox("Data file name (e.g., data.csv)?")
  • 'Glue variable: tFileName
  • 'Get the followers from the file
  • Open ThisWorkbook.Path & "\" & tFileName For Input As #1
  • Input #1, sFollowers
  • Close #1
  •  
  • 'Glue variable: sFollowers

Good! Thanks for that, Adela.

Adela
Adela

You're welcome. It helps me understand things better when I explain them to other people.

Yes, that's true. Teach someone else, and you understand it better yourself.

One more thing before we go on. Adela didn't start with the Dims.

Reflect

Why didn't Adela start with Dims when writing the code?

(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

Until you know what variables you want, you can't Dim them. As you work out the code, you can Dim variables as you need them.

Correct! That's what I do, too, when writing bigger programs.

Let's look at the processing code, now that we have a value for the glue variable sFollowers.

Here's the condominimums goats get:

Followers Condominimum
Less than 555 BB
Between 555 and 5,555 CC
Between 5,555 and 55,555 LL
More than 55,555 OPJ

Here's the code to use that info.

  • 'Glue variable: followers
  •  
  • 'Processing
  • If sFollowers < 555 Then
  •     tCondo = "Basic Billy"
  • ElseIf sFollowers < 5555 Then
  •     tCondo = "Comfy Cylde"
  • ElseIf sFollowers < 55555 Then
  •     tCondo = "Luxury Lucy"
  • Else
  •     tCondo = "One percent Juan"
  • End If
  •  
  • 'Glue variable: condo

Remember that VBA will only do one of the tCondo = "something" statements. So if sFollowers was 333, VBA would run tCondo = "Basic Billy", and then skip to the End If.

The last bit o' code is easy.

  • 'Glue variable: condo
  •  
  • 'Output.
  • tMessage = "Condominimum: " & tCondo
  • MsgBox tMessage

Thanks for helping run through this, everyone. It's better when you study with other people.

One other thing. Georgina worked backwards when writing the sales report code. She started with the output, and worked back from there.

Adela worked forward. She wrote the input first, and worked down from there.

I use both when I code. If I know a task well, and have the patterns already in mind, I usually work forward. But if I'm unsure of how I'll write the code, I might work backwards. That helps me focus on converting the goal into running code, when I don't know ahead of time how the code will be structured.

One more example

Suppose we wanted to work out a goat's age category, using this table (numbers are in months):

From To Category
0 < 4 Infant
4 < 24 Kid
24 < 48 Young adult
48 < 72 Middle-aged adult
72 < 96 Oldster
96 Infinity Crypt Keeper

Suppose that the variable sGoatAgeMonths has a goat's age in months. This code would work out the age category:

  • If sGoatAgeMonths < 4 Then
  •     tGoatAgeCategory = "Infant"
  • ElseIf sGoatAgeMonths < 24 Then
  •     tGoatAgeCategory = "Kid"
  • ElseIf sGoatAgeMonths < 48 Then
  •     tGoatAgeCategory = "Young adult"
  • ElseIf sGoatAgeMonths < 72 Then
  •     tGoatAgeCategory = "Middle-aged adult"
  • ElseIf sGoatAgeMonths < 96 Then
  •     tGoatAgeCategory = "Oldster"
  • Else
  •     tGoatAgeCategory = "Crypt Keeper"
  • End If
Pattern

Multiway

Use a chain of If statements.

Exercise

Exercise

Zilo's zen rater

Zilo is a self-proclaimed Zen master. Goat Zen is nothing like human Zen. Goat Zen is about purity-of-hide. Why? Zilo says it came to him in a divine dream.

Anyway, write a program to compute a goat's Zen rating, based on the number of tattoos they have. Here's how it works.

Tattoos Rating
0 5
More than 0, less than 4 4
4 or more, less than 8 3
8 or more, less than 14 2
14 or more 1

Here's what it starts like:

Start

The user puts in a number of tattoos, and clicks the button. Sample output:

Output

More:

Output

Yes, there is a goat with 522 tattoos. Blotto, Nanny of the Ink.

Upload your workbook. The usual coding standards apply.

Up next

One of the most important uses of If statements is for input validation, making sure that the data users' type is reasonable. Let's get started on that.