Multiway | Programming with Excel VBA

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

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

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

Dim tFileName As String
Dim sFollowers As Single
Dim tCondo As String
Dim tMessage As String
'Input
sFollowers = InputBox("How many followers?")
'Glue variable: sFollowers
'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.

Glue variables

Glue variables link two parts of a program together.

'Input
sFollowers = InputBox("How many followers?") Put data into the glue variable
'Glue variable: sFollowers
'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: tCondo
'Output.
message = "Condominimum: " & tCondo Use data from the glue variable

Glue variables aren't really a thing, from your computer's view point. To a computer, a variable is a variable is a variable. Gluey or not, they're all just variables.

Thinking about glue variables helps us design programs. It makes us think about the data that ties the chunks together.

Ray

I don't understand what you just wrote.

Maybe G can help.

Georgina writes a program

Hey, G! Can you write the condominimum program, using backwards design, and glue variables?

Georgina

OK.

It's going to be IPO, so I'll type in comments for that.

'Input
'Processing
'Output.

Georgina

Start at the bottom, with the output. I need to output the name of the condo. That's going to come from processing, in a glue variable.

Note

A glue variable is the output of one chunk of code, that's the input to another. In this case, Processing puts data into a variable, and Output grabs it.

Georgina

Put that in...

Dim tCondo As String
'Input
'Processing
'Glue variable: tCondo
'Output.

Georgina

OK, that's how Processing and Output are connected.

Let's keep going. The Input chunk sends data to the Processing chunk. I'll add a variable for that.

Let's see... Processing needs to know the number of followers, so it can work out the condo. The number of followers comes from the Input chunk.

Dim tCondo As String
Dim sFollowers As Single
'Input
'Glue variable: sFollowers
'Processing
'Glue variable: tCondo
'Output.

Georgina

OK, that's the overall design.

Now I'll start writing the actual code. Hmm... The output is a dialog, so...

Dim tCondo As String
Dim sFollowers As Single
'Input
'Glue variable: sFollowers
'Processing
'Glue variable: tCondo
'Output.
tMessage = "Condominimum: " & tCondo
MsgBox tMessage

Ethan

You could have put that in one line, right?

Georgina

Yep: MsgBox "Condominimum: " & tCondo

It's easier for me to think of it as two steps: make the output message, and then show it. Easy is good, Kieran says.

But, either way.

Note

If you want to break things up, do it. Code is often easier to think about and debug when it's broken up.

Georgina

Now to write Processing.

Dim tCondo As String
Dim sFollowers As Single
'Input
'Glue variable: sFollowers
'Processing
'Glue variable: tCondo
'Output.
tMessage = "Condominimum: " & tCondo
MsgBox tMessage

Georgina

It's going to take sFollowers, and work out tCondo

Less to think about

G doesn't have to think about output anymore. She can focus on processing. As long as processing puts data into tCondo, it's good.

Georgina

I can use the multiway pattern from the pattern catalog.

'Glue variable: sFollowers
'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

Ray

OK, I see it now. G is writing the processing stuff. She knows what the data coming in is, and the data going out. So she can think just about processing. Easier that way.

Right! Easy is good. Code that's easy to thing about will have fewer bugs.

Georgina

Now just add the input, from the dialog.

Dim sFollowers As Single
Dim tCondo As String
Dim tMessage As String
'Input
sFollowers = InputBox("How many followers?")
'Glue variable: sFollowers
'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 tMessage

Good job!

Ray

Yeah, thanks G! I get it now.

Georgina

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.

Here's what G did:

Broke the program into chunks.
Planned how the chunks link together, using glue variables.
Wrote the program, one chunk at a time.

Breaking-a-progam-into-chunks-to-make-it-easier-to-think-about is called decomposition.

Principle

Decomposition

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

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

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:

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

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

Upload your workbook. The usual coding standards apply.

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

Up next

Ifs can be inside each other. Let's see what that's about.