Basic validation: strings

Business analytics is all about the data. Data comes from all over. Employees, customers, cash registers, goats... you name it.

Problem is, people make mistakes. Even goats, sometimes.

​Sad, but true

Some errors even crash our programs! Not good.

How do we make sure the data we get makes sense? What do we do when it doesn't? That's what validation is about.

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

Except... when you check data from this d00d...

...that's data vladidation.

Hey, that's an original Kieran Pun!

Thanks, G.

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

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.

tResponse is the data we'll validate. Or vladidiate. It comes from a dialog in this example, but it could come from a cell, a file, a database, whatevs.

First, you normalize the data, converting it to a known form. We change "Yes", "YEs", " yes ", and other things into "yes", by stripping leading and trailing spaces, and converting to lowercase.

Spaces at the beginning and end. When we Trim() " Tale of Two Cities ", we get "Tale of Two Cities".

Yes! It often is, though I give it a separate name, to bring attention to it.

​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:

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

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

After normalizing, the only valid responses are "yes" and "no". So, if the response is not "yes", and it is not "no"...

... it's invalid. Show an error message, and stop.

That's what End means: stop immediately.

End If and End have as much in common as hamster and ham. Nothing.

The full pattern is:

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. Rightly so.

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.

Click the button, and see:

Here's code that would do that.

Or:

Another way

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.

Check this out:

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.

​I loooove flags!

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

... or a 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

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 for any of the colors, 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.

Right! And, yes, it is cool.

Sadly not.

(I just noticed. The acronym of Poop On Our Party is POOP. The acronym contains itself. Huh.)

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

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.

In this case, we set the flag to "no", and any one test makes it "yes".

In a different situation, we might set the flag to "yes", and any one test makes is "no". That's more common. We'll see it later.

Reusing knowledge

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

Up next

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