Input validation so far

Earlier, Klaus and Jeremy wrote a program to convert pounds and ounces into kilos.

Much of their code was validating user input:

1. 'Validate pounds
2. userInput = Cells(3, 2)
3. If Not IsNumeric(userInput) Then
4.     MsgBox "Sorry, pounds must be a number."
5.     End
6. End If
7. If userInput < 0 Then
8.     MsgBox "Sorry, pounds cannot be negative."
9.     End
10. End If
11. pounds = userInput
12. 'Validate ounces
13. userInput = Cells(4, 2)
14. If Not IsNumeric(userInput) Then
15.     MsgBox "Sorry, ounces must be a number."
16.     End
17. End If
18. If userInput < 0 Then
19.     MsgBox "Sorry, ounces cannot be negative."
20.     End
21. End If
22. ounces = userInput

The code shows error messages like this:

There are two problems with this method.

First, once users click OK, the error message is gone. When they start fixing errors, there's no reminder of what the errors were. No big deal, but at work people get interrupted all the time. Even a one minute interruption will make someone lose track.

The second problem: users might have made more than one mistake. Say a user got both pounds and ounces wrong. S/he gets an error message about the first problem, then the program stops. The user fixes that problem, only to get another error message. It might be more helpful to show both errors at one time.

Better error reporting

Here's what we want to happen when both pounds and ounces have bad input:

Differences from before:

• Both errors are reported.
• The error messages stay on the screen.

We should make other changes, too. A human might have two conversions to make, not just one. Here's a human, to remind you what they are.

Two eyes, one to three noses, you get the idea.

Here's what might happen. The human does the first conversion, without any typing mistakes.

She starts on the second task. She makes a typing mistake in ounces. She meant to type 5, but missed a little, and also hit the R key, just below 5:

The program shows an error, and also erased the output from the previous task – 1.588. Leave it there, and there's a small chance that a human who gets interrupted might think that 1.588 was the right answer. Erase the output cell when the Convert button is pressed, and that won't be an issue.

So that's one change: erase the output each time the program runs.

OK, now the human corrects the mistake and hits Convert:

The program erased the error message. Leave it there, and the human might think she hadn't fixed the mistake.

So, each time the program runs:

• Erase the output.
• Erase error messages.

Initializing

The two erasures are done every time the program runs. Geeks call that initializing, or initing, the program.

1. Private Sub cmdConvert_Click()
2.     'Declare varibles.
3.     ....
4.     'Init
5.     initializeConvert
6.     ...
7. End Sub
9. 'Initialize.
10. Sub initializeConvert()
11.     'Clear output cell.
12.     Cells(8, 2) = ""
13.     'Clear errors.
14.     Cells(3, 3) = ""
15.     Cells(4, 3) = ""
16. End Sub

Pattern

Initialize

Situation:

Some things need to be done every time a program starts.

Actions:

Create a Sub for the code called initializeProgram, or some such.

More…

The flag pattern

Here's what we want to do. Suppose that Check input 1 is a sub that will show an error.

Check input 1
Check input 2
Check input 3
if nothing went wrong then
    process
    output
end if

Another way:

Check input 1
Check input 2
Check input 3
if anything went wrong then
    end
end if
process
output

The flag pattern helps. A flag is a variable that shows whether something happened. It's usually a Boolean, a data type. Booleans are either True, or False, and nothing else. Instead of using booleans for flags, you could use Strings ("yes" or "no"), Integers (1 or 0), whatevs. Booleans happen to be convenient for flags, but use what you want.

Pattern

Flag

Situation:

You want to keep track of whether something happened. For example, whether one of several input errors happened.

Actions:

Use a variable as a flag. Write code that initializes it, sets it if some condition occurs, then checks it. See this pattern's Explanation for an example.

More…

Validation Sub

Here's the original validation code for pounds and ounces. The differences between validating pounds and validating ounces are marked.

1. 'Validate pounds
2. userInput = Cells(3, 2)
3. If Not IsNumeric(userInput) Then
4.     MsgBox "Sorry, pounds must be a number."
5.     End
6. End If
7. If userInput < 0 Then
8.     MsgBox "Sorry, pounds cannot be negative."
9.     End
10. End If
11. pounds = userInput
12. 'Validate ounces
13. userInput = Cells(4, 2)
14. If Not IsNumeric(userInput) Then
15.     MsgBox "Sorry, ounces must be a number."
16.     End
17. End If
18. If userInput < 0 Then
19.     MsgBox "Sorry, ounces cannot be negative."
20.     End
21. End If
22. ounces = userInput

The code is almost identical, so you should think, "Aha! Time for a Sub." The differences are:

• The cell to check.
• The label to use for the variable in the error messages ("pounds" or "ounces").

Those differences become params for the Sub. Here's one way to do it.

1. 'Check that the value in a cell is a number that is not negative.
2. 'Params:
3. '  row: the row the cell to check is in, e.g., 3.
4. '  dataLabel: text describing the data in the cell, e.g., "pounds".
5. '  dataOK: returns true if the data is OK, false if it is not.
6. Sub checkCellValue(row As Integer, dataLabel As String, dataOK As Boolean)
7.     Dim userInput As String
8.     Dim errorMessage As String
9.     dataOK = True
10.     'Check if numeric.
11.     userInput = Cells(row, 2)
12.     If Not IsNumeric(userInput) Then
13.         errorMessage = "Sorry, " & dataLabel & " must be a number."
14.         dataOK = False
15.     'Less than zero?
16.     ElseIf userInput < 0 Then
17.         errorMessage = "Sorry, " & dataLabel & " cannot be negative."
18.         dataOK = False
19.     End If
20.     'Bad thing happened?
21.     If Not dataOK Then
22.         'Show the error.
23.         Cells(row, 3) = errorMessage
24.         Cells(row, 3).Font.Color = vbRed
25.     End If
26. End Sub
27. ​

The first few lines document the Sub, explaining each param. That's common. Human programmers often work in groups. One human might write checkCellValue, and the other humans use it. Documenting the params helps those other humans.

Notice the first two params. They're the differences betwixt uses of the sub. To use it to check pounds:

checkCellValue 3, "pounds", poundsOK

To check ounces:

checkCellValue 4, "ounces", ouncesOK

The third param is the result of the check.

Notice the message variable. Why does it exist? Another way to write the code:

1. 'Check if numeric.
2. userInput = Cells(row, 2)
3. If Not IsNumeric(userInput) Then
4.     Cells(row, 3) = "Sorry, " & dataLabel & " must be a number."
5.     Cells(row, 3).Font.Color = vbRed
6.     dataOK = False
7. ElseIf userInput < 0 Then
8.     Cells(row, 3) = "Sorry, " & dataLabel & " cannot be negative."
9.     Cells(row, 3).Font.Color = vbRed
10.     dataOK = False
11. End If
12. ​

Say the Bad Boss Human wanted errors to look like this:

With this code…

1. 'Check if numeric.
2. userInput = Cells(row, 2)
3. If Not IsNumeric(userInput) Then
4.     Cells(row, 3) = "Sorry, " & dataLabel & " must be a number."
5.     Cells(row, 3).Font.Color = vbMagenta
6.     dataOK = False
7. ElseIf userInput < 0 Then
8.     Cells(row, 3) = "Sorry, " & dataLabel & " cannot be negative."
9.     Cells(row, 3).Font.Color = vbMagenta
10.     dataOK = False
11. End If
12. ​

… we need to make the change for every error message. Two error messages, two changes. Eight error messages, eight changes.

With this code…

1. 'Bad thing happened?
2. If Not dataOK Then
3.     'Show the error.
4.     Cells(row, 3) = errorMessage
5.     Cells(row, 3).Font.Color = vbMagenta
6. End If
7. ​

… we make the change only once, no matter how many error checks we have. Fewer chances of mistakes.

Big Idea

Make programs easy to change

Programmers spend a lot of time changing programs that have already been written. Make it easy,

More…

Calling the input validation routine

The weight conversion program is IPO: input, processing, output.

Pattern

Input-processing-output

Situation:

Data needs to be transformed into other data, without user interaction after input.

Actions:

Use abstraction to define each of the three parts, and the way they communicate. Abstraction means that you:

• Name each subroutine.
• Specify parameters and, for functions, return values.

More…

The main program should reflect that:

1. Private Sub cmdConvert_Click()
2.     'Declare variables.
3.     Dim pounds As Single
4.     Dim ounces As Single
5.     Dim kilos As Single
6.     Dim dataOK As Boolean
8.     'Init
9.     initializeConvert
11.     'Input
12.     getInput dataOK, pounds, ounces
13.     If Not dataOK Then
14.         End
15.     End If
17.     'Processing
18.     computeKilos pounds, ounces, kilos
20.     'Output
21.     outputKilos kilos
22. End Sub

The main program doesn't call checkCellValue directly. Instead, the main program calls the sub getInput, which does all the input validation, and returns a flag showing whether the input was all OK, or if there were any errors. The main program checks that flag to decide whether to continue.

Here's getInput:

1. 'Input
2. Sub getInput(dataOK As Boolean, pounds As Single, ounces As Single)
3.     Dim poundsOK As Boolean
4.     Dim ouncesOK As Boolean
5.     'Validate pounds.
6.     checkCellValue 3, "pounds", poundsOK
7.     'Validate ounces.
8.     checkCellValue 4, "ounces", ouncesOK
9.     If poundsOK And ouncesOK Then
10.         'Input valid, check return the input data.
11.         pounds = Cells(3, 2)
12.         ounces = Cells(4, 2)
13.         dataOK = True
14.     Else
15.         dataOK = False
16.     End If
17. End Sub

It calls getInput twice, once for each input cell. If both are OK, then it grabs data from the cells into the variables pounds and ounces, and sets the flag dataOK to True. ​ Subs calling Subs is common.

Some questions for the class.

Argh! The names!

It's different values at different times.

Params can make a sub behave differently each time it's called. The first time checkCellValue is called, it messes with row 3. The next time, row 4.

That's one reason to add params. To make a sub do different things at different times.

Also, remember that it's the positions of the parameters that matters, not their names.

Suppose we had this code:

1. Sub d()
2.     Dim i As Integer
3.     Dim j As Integer
4.     i = 2
5.     j = 3
6.     s j, i
7. End Sub
9. Sub s(i As Integer, j As Integer)
10.     Dim message As String
11.     message = "i: " & i & " j: " & j
12.     MsgBox message
13. End Sub

Compare lines 6 and 9. i and j are reversed. So here's how the variables in the main program map to the params in the sub:

So you'd see:

Params map to data in the caller.

Exercises

Summary