Reusing code

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:

Tax return

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

Income

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:

Bad income data

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
Klaus
We’d have to make a few changes to each thing we pasted. It’s be easy to make a mistake.
Simon
Simon
And hard to find mistakes we did make.
Tara
Tara
Good! Anything else?
Lily
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
Tara
Yes, that’s right.
Jeremy
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
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:

Worksheet fragment

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
Lily
Oo! Parameters!
Jeremy
Jeremy
Yeah! I was thinking the same!

Parameters are your friend

  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

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

Parameters

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

Triangle

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

Worksheet

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:

Worksheet

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.

Upload your Excel workbook.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

You can call a sub many times. If you change the parameters in the call, you change the variables the sub affects.