1. Course content
  2. Part III. Building blocks
  3. Subroutines
  4. Reusing code

Reusing code

Keywords: 
Reuse
Subroutine
Procedure

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.

More…

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

Summary

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