Basic validation: strings

Tags
Fill in the blank

Line continuation character

The line continuation characters lets you break up a long line of code. What is the line continuation character? Type it in.

Your answer:
Saving

Lesson contents

We've been looking at Ifs, like the multiways in the last lesson. Let's keep going, and talk about validation. How do we make sure that data makes sense? What do we do when it doesn't?

We'll start off with string validation. The next lesson is about numeric validation.

Two valid options

Cthulhu has a big antimatter bomb. The bomb is in Cthulhu's lair, ready to detonate if someone should some close to discovering its secrets.

Cthulhu has a VBA program to trigger the bomb. Click the Detonate button, and the program asks you to confirm detonation, by typing "yes" or "no".

Confirm detonation

Let's add some code that checks whether the user's response is valid, that is, is either yes, or no.

Here's one way to do it.

  • Dim response As String
  •  
  • tResponse = InputBox("Are you sure you want to detonate (yes/no)?")
  • ' Normalize.
  • tResponse = LCase(Trim(tResponse))
  • ' Test normalized value.
  • If tResponse <> "yes" And tResponse <> "no" Then
  •     'Respond to error.
  •     MsgBox "Sorry, you must enter yes or no."
  •     End
  • End If

tResponse is the data we'll validate. It comes from a dialog in this example, but it could some from a cell, a file, or somewhere else.

First, you normalize the data, converting it into a known form. You learned about this earlier. We change "Yes", "YEs", " yes ", and other things into "yes", but stripping the spaces, and converting to lowercase.

Pattern

Normalize a variable

Convert a variable that can have many formats, into one format.

Georgina
Georgina

So the normalization pattern is part of the string validation pattern?

Yes! Good noticing.

Pattern in pattern

​The normalize pattern is part of the string validation pattern

You'll see that more as we go. Simple patterns put together, to create more complex patterns.

Here's the code again:

  • Dim response As String
  •  
  • tResponse = InputBox("Are you sure you want to detonate (yes/no)?")
  • ' Normalize.
  • tResponse = LCase(Trim(tResponse))
  • ' Test normalized value.
  • If tResponse <> "yes" And tResponse <> "no" Then
  •     'Respond to error.
  •     MsgBox "Sorry, you must enter yes or no."
  •     End
  • End If

So we normalize the input, and then test to see if the normalized value is valid:

  • If tResponse <> "yes" And tResponse <> "no" Then
  •     MsgBox "Sorry, you must enter yes or no."
  •     End
  • End If

(Remember that <> means "not equal to.")

Because we've normalized, we only need to test for two things, not all the things users might type.

After normalizing, the only valid responses are "yes" and "no". So, if the response is not "yes", and it is not "no", then show an error message, and stop. That's what End does: stop immediately. Remember, End If and End have nothing to do with each other. Hamster and ham.

The full pattern is:

Full pattern

Pattern

Validation (string, basic)

Normalize, then test with If(s).

Goat coat color

The detonate-or-not test has only two valid values: yes, and no. What if there are more?

Cthulhu sends goodie bags to its goaty friends. The bags include cosmetics, like highlighting color jars (HCJs). They're small jars of powder that come with tiny brushes. The HCJs goats get depend on the goats' main colors. There are four main colors:

  • Black
  • Brown
  • White
  • Pink

Pink goats have lost most of their hair, due to age, or fraternity pranks. Of course, Cthulhu kills humans responsible for such things. It really likes goats.

Let's make a worksheet with a goat's main color in a cell. We'll just talk about the validation code, that is, checking to make sure that the main color is OK.

Here's how it starts.

Input

Click the button, and see:

Bad color message

Here's code that would do that.

  • Dim tMainColor As String
  • Dim tMessage As String
  •  
  • tMainColor = Cells(7, 2)
  • tMainColor = LCase(Trim(tMainColor))
  • If Not ( _
  •        tMainColor = "black" _
  •     Or tMainColor = "brown" _
  •     Or tMainColor = "white" _
  •     Or tMainColor = "pink" _
  • ) Then
  •     tMessage = "Sorry, " & tMainColor & " is not a valid color."
  •     MsgBox tMessage
  •     End
  • End If

Or:

  • If tMainColor <> "black" _
  •     And tMainColor <> "brown" _
  •     And tMainColor <> "white" _
  •     And tMainColor <> "pink" _
  • Then

Another way

Adela
Adela

That last one, the If with all the Ands. Is that how you would do it?

Well, maybe not. If something goes wrong in the middle of the statement, it's hard to debug.

Remember the multiway If from the last lesson? I would use that.

Pattern

Multiway

Use a chain of If statements.

Check this out:

  • If tMainColor = "black" Then
  •     Do something to show color is OK
  • ElseIf tMainColor = "brown" Then
  •     Do something to show color is OK
  • ElseIf tMainColor = "white" Then
  •     Do something to show color is OK
  • ElseIf tMainColor = "pink" Then
  •     Do something to show color is OK
  • End If
  • If Color is not OK Then
  •     tMessage = "Sorry, " & tMainColor & " is not a valid color."
  •     MsgBox tMessage
  •     End
  • End If

Each If is simple, testing just one color. If we could figure out how to get this to work, it would be a better option. But how do we show that the color is OK or not?

We can use the flag pattern for that. A flag is a variable used to check the result of a bunch of code.

Let's see how it works. Here's Flag Goat.

Flag Goat

​I loooove flags!

Flag Goat can only hold one flag at a time. We can give it a red flag...

Goat with red flag

... or a green flag...

Goat with green flag

Let's write a Flag Goat version of the string validation code, using a multiway If. Here it is:

Flag pattern in action

Flag pattern in action

So, we want to test whether tMainColor is valid. We give the goat a red flag to start with. Then we compare tMainColor to each of the valid colors in turn. If there's a match, we give the goat a green flag.

After all of the Ifs, if the goat still has the red flag it started with, then tMainColor is not a valid color.

Marcus
Marcus

Oh, I get it! If there's a match to a valid color, any of them, the goat gets a green flag! That's so cool!

Right! And, yes, it is cool.

Ray
Ray

Not to be Mr Poop On The Party here, but VBA doesn't have goats in it.

Sadly not. We have to use a variable, instead of a goat.

  1. 'Get user input.
  2. tMainColor = Cells(7, 2)
  3. 'Normalize.
  4. tMainColor = LCase(Trim(tMainColor))
  5. 'Initialize flag.
  6. tIsMainColorOk = "no"
  7. 'Test for valid colors.
  8. If tMainColor = "black" Then
  9.     tIsMainColorOk = "yes"
  10. ElseIf tMainColor = "brown" Then
  11.     tIsMainColorOk = "yes"
  12. ElseIf tMainColor = "white" Then
  13.     tIsMainColorOk = "yes"
  14. ElseIf tMainColor = "pink" Then
  15.     tIsMainColorOk = "yes"
  16. End If
  17. 'Is the color valid?
  18. If tIsMainColorOk = "no" Then
  19.     'Show error message to the user.
  20.     tMessage = "Sorry, " & tMainColor & " is not a valid color."
  21.     MsgBox tMessage
  22.     'Stop the program.
  23.     End
  24. End If

tIsMainColorOk is the flag variable. Set it to "no" to start with (line 6). Test the normalized input against valid colors (lines 8, 10, 12, and 14), setting tIsMainColorOk to "yes" if there's a match (lines 9, 11, 13, and 15).

Line 18 tests the flag. If it still has the value it started with, then the color isn't valid.

Pattern

Flag

A variable that summarizes the results of a bunch of code.

Reusing knowledge

Adela
Adela

This string validation lesson used something from the last lesson, the multiway If. You do that a lot.

Yes, that's true. Building on what you learn helps you get more value from each hour you spend on the course.

You should try to reuse what you know. If you get a task that doesn't match exactly a pattern you can remember, don't just give up. Instead, ask yourself:

Self, can I adapt what I know for this task?

Computers are stupid, but you aren't. Be flexible in your thinking.

If you get stuck, remember the pattern catalog. Scan through the pattern list. It might trigger some ideas.

Exercise

Exercise

Ulma's Ungulates

Write a program to compute the price of renting an animal from Ulma's Ungulates. You can rent either a llama, or a camel. The price depends on:

  • Which animal you choose.
  • Whether you want to rent a saddle as well.

The worksheet starts like this:

Start

The user fills in the two cells above the Run button, and clicks Run.

Validate all the input, using the following rules:

  • The animal input must be either L or C. Case doesn't matter. Use trim() to remove unwanted spaces.
  • Saddle is either Y or N. Case doesn't matter. Use trim() to remove unwanted spaces.

If there are any input errors, show a message, and end the program. For example:

Error

If the data is OK, output the full name of the animal, a warning, and the cost.

  • Llama rent for $80.
  • Camels rent for $95.
  • Saddles rent for $30.

The warning is "Spits" for llamas, and "Stinks" for camels.

Here's some output:

Output

Another example:

Output

The usual coding standards apply.

Up next

This lesson is about checking strings. What about checking numbers?