Keywords:

# DRY

DRY stands for Don't Repeat Yourself. One of the Big Ideas of programming.

Big Idea

Don't repeat yourself

Write similar code once. Put it in a Sub. Use params to tell the Sub what to do differently each time the Sub is called.

So far, each of the subs has been called just once per program. Let's do something different.

# Retaxing

Remember that strange tax return program? The worksheet:

Let's add input validation. We'll just concentrate on the income part:

Here's the input code without validation.

1. incomeWages = Cells(5, 2)
2. incomeInvestments = Cells(6, 2)
3. incomeImaginary = Cells(7, 2)
4. incomeBeauty = Cells(8, 2)
All of the input is supposed to be numeric, zero or greater, with no empty cells. If there's an error, let's display an error message to the right of the cell, like this:

Here's code that would check wages income and investments income:

1. 'Check wages income
2. userInput = Cells(5, 2)
3. If Not IsNumeric(userInput) Then
4.     Cells(5, 3) = "Please enter a number."
5.     Cells(5, 3).Font.Color = vbRed
6.     End
7. End If
8. If userInput < 0 Then
9.     Cells(5, 3) = "Please enter a number greater than zero."
10.     Cells(5, 3).Font.Color = vbRed
11.     End
12. End If
13. incomeWages = userInput
14. 'Check investment income
15. userInput = Cells(6, 2)
16. If Not IsNumeric(userInput) Then
17.     Cells(6, 3) = "Please enter a number."
18.     Cells(6, 3).Font.Color = vbRed
19.     End
20. End If
21. If userInput < 0 Then
22.     Cells(6, 3) = "Please enter a number greater than zero."
23.     Cells(6, 3).Font.Color = vbRed
24.     End
25. End If
26. incomeInvestments = userInput
The code is almost identical. There are only a few differences:

1. userInput = Cells(6, 2)
2. If Not IsNumeric(userInput) Then
3.     Cells(6, 3) = "Please enter a number."
4.     Cells(6, 3).Font.Color = vbRed
5.     End
6. End If
7. If userInput < 0 Then
8.     Cells(6, 3) = "Please enter a number greater than zero."
9.     Cells(6, 3).Font.Color = vbRed
10.     End
11. End If
12. incomeInvestments = userInput
We could just cut-and-paste the code for each input cell. Why would that be a Bad Thing?

Klaus
We'd have to make a few changes to each thing we pasted. It's be easy to make a mistake.
Simon
And hard to find mistakes we did make.
Tara
Good! Anything else?
Lily
What if we wanted to change the validation code? Maybe change the text of the error messages, or change the color. We'd have a lot of code to change.
Tara
Yes, that's right.
Jeremy
Simon talked about finding mistakes. If there are, like, twenty copies of the validation code, but each one changed a little, that's a lot of testing!
Tara
Indeed it is! Good thinking, Jeremy.

# One sub to rule them all

Let's look at the validation code again. It's almost identical for every input cell. There are only a few differences:

1. userInput = Cells(6, 2)
2. If Not IsNumeric(userInput) Then
3.     Cells(6, 3) = "Please enter a number."
4.     Cells(6, 3).Font.Color = vbRed
5.     End
6. End If
7. If userInput < 0 Then
8.     Cells(6, 3) = "Please enter a number greater than zero."
9.     Cells(6, 3).Font.Color = vbRed
10.     End
11. End If
12. incomeInvestments = userInput
If we put this in a sub, it'd be something like:

1. Sub validateInput()
2.     userInput = Cells(ROW, 2)
3.     If Not IsNumeric(userInput) Then
4.         Cells(ROW, 3) = "Please enter a number."
5.         Cells(ROW, 3).Font.Color = vbRed
6.         End
7.     End If
8.     If userInput < 0 Then
9.         Cells(ROW, 3) = "Please enter a number greater than zero."
10.         Cells(ROW, 3).Font.Color = vbRed
11.         End
12.     End If
13.     VARIABLE = userInput
14. End Sub
Here's part of the worksheet again:

We want the sub to work for row 5, and put the data into, say, incomeWages.

We want the same sub to work for row 6, and put the data into, say, incomeInvestments.

Lily
Oo! Parameters!
Jeremy
Yeah! I was thinking the same!

1. Sub validateInput(row As Integer, destination As Single)
2.     Dim userInput As String
3.     userInput = Cells(row, 2)
4.     If Not IsNumeric(userInput) Then
5.         Cells(row, 3) = "Please enter a number."
6.         Cells(row, 3).Font.Color = vbRed
7.         End
8.     End If
9.     If userInput < 0 Then
10.         Cells(row, 3) = "Please enter a number greater than zero."
11.         Cells(row, 3).Font.Color = vbRed
12.         End
13.     End If
14.     destination = userInput
15. End Sub
If we call validateInput with 5 for row, it will check cell (5, 2), and show error messages in cell (5, 3).

If we call validateInput with 6 for row, it will check cell (6, 2), and show error messages in cell (6, 3).

How does validateInput get 5 for row one time, and 6 another? Here's how we would call validateInput:

1. validateInput 5, incomeWages
2. validateInput 6, incomeInvestments
3. validateInput 7, incomeImaginary
4. validateInput 8, incomeBeauty
The first time validateInput is called, row is 5. The next time, it's 6. The next time, it's 7. The next time, it's 8.

How does validateInput send data back to its caller? Remember how parameters work. Here's the first call.

validateInput 5, incomeWages

Parameters map the sub's memory into the caller's memory. So, when validateInput changes what's in destination, it changes incomeWages in the main program.

Here's the second call.

validateInput 6, incomeInvestments

When validateInput puts data into destination, it's really putting the data into a variable in the caller's memory space. Which variable? Whatever the caller uses as the second parameter.

Jeremy
Wait. When we used parameters earlier, the names were the same. Like, we had a sub signature like this:

Sub getInput(quality As String, size As String, region As String)

The call would be like this:

getInput quality, size, region

The parameter names in the sig and the call were the same.

But they don't have to be?

Tara
That's right. Before, we were using subs to thunk. Each sub was one chunk. We'd only call each sub once.

But now, the whole point is to reuse the sub, call it several times. That's more convenient when the names don't have to match.

What matters is the order of the parameters. So we could have done this:

getInput quality, size, region
...
Sub getInput(goodness As String, bigness As String, where As String)

quality maps to goodness, size to bigness, and region to where.

# Exercises

Exercise: Compute hypotenuse with validation
Create a workbook to compute the length of the hypotenuse of a right angled triangle, with input validation.

Here's a screenshot of the worksheet with valid data.

Height and width should be numeric, and not zero. Negative numbers are OK. If there's an error, show an error message in the next column:

Error messages and the output cell should be erased every time Run is clicked.

Use this as your main program. Don't change anything.

1. Private Sub cmdRun_Click()
2.     Dim height As Single
3.     Dim width As Single
4.     Dim hyp As Single
5.     'Erase results from previous run
6.     clearLastRun
7.     'Get input
8.     getSide 3, height
9.     getSide 4, width
10.     'Compute hypotenuse
11.     hyp = Sqr(height * height + width * width)
12.     'Show output
13.     Cells(6, 2) = hyp
14. End Sub
Write the two missing subs.