Hack 75. Shrink Your Code with Subroutines

Say goodbye to long and difficult-to-maintain code by placing repetitive processing into subroutines.

All applications live and grow. Functionality begets functionality. As users start banging away at your first delivered application, they scream for more features. As you add these features, the amount of code can grow. Often, routines get copied, and then a couple of literals, variable names, or criteria get changed. You end up with code that has a number of similar routines.

Example 8-3 shows three identical routines, with the exception that each addresses a different state.

Example 8-3. Multiple nearly identical routines

Sub get_NY_records( )
  '
  'get New York customers
  '
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As ADODB.Recordset
  Set recset = New ADODB.Recordset
  recset.Open "Select * From Customers Where State='NY'", conn
  Do Until recset.EOF
	''Process records here
  recset.MoveNext
  Loop
  recset.Close
  Set recset = Nothing
 End Sub

 Sub get_CT_records( )
   '
   'get Connecticut customers
   '
   Dim conn As ADODB.Connection
   Set conn = CurrentProject.Connection
   Dim recset As ADODB.Recordset
   Set recset = New ADODB.Recordset
   recset.Open "Select * From Customers Where State='CT'", conn
   Do Until recset.EOF
    ''Process records here
   recset.MoveNext
   Loop
   recset.Close
   Set recset = Nothing
 End Sub

 Sub get_MA_records( )
   '
   'get Massachusetts customers
   '
   Dim conn As ADODB.Connection
   Set conn = CurrentProject.Connection
   Dim recset As ADODB.Recordset
   Set recset = New ADODB.Recordset
   recset.Open "Select * From Customers Where State='MA'", conn
   Do Until recset.EOF
    ''Process records here
   recset.MoveNext
   Loop
   recset.Close
   Set recset = Nothing
 End Sub

The code in Example 8-3 is frightfully redundant. You can optimize code such as this by creating a subroutine that takes an argument. You place the code that is identical in all the routines into the subroutine and then use the argument to pass the particular individual value to the subroutine to run the process.

In Example 8-3 the only differentiating item in the code is the state, such as in this statement, which selects Massachusetts (MA) records:

  recset.Open "Select * From Customers Where State='MA'", conn

Example 8-4 shows how to change the code by using a subroutine. Now the repetitive code is placed in a separate subroutine named get_state_records. The subroutine takes a string argument, named state.

Example 8-4. The repetitive code placed into a subroutine

Sub get_NY_records( )
  'get New York customers
   get_state_records "NY"
End Sub

Sub get_CT_records( )
  'get Connecticut customers
  get_state_records "CT"
End Sub

Sub get_MA_records( )
  'get Massachusetts customers
   get_state_records "MA"
End Sub

Sub get_state_records(state As String)
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As ADODB.Recordset
  Set recset = New ADODB.Recordset
  recset.Open "Select * From Customers Where State='" & state & "'", conn
  Do Until recset.EOF
   ''Process records here
  recset.MoveNext
  Loop
  recset.Close
  Set recset = Nothing
End Sub

Now, each individual routine, such as get_MA_records, simply calls the generic subroutine and passes the state initials as the argument. This is done in a single line of code:

	get_state_records "MA"

The generic get_state_records subroutine takes the passed argument and uses it in the SQL statement that opens the recordset:

	recset.Open "Select * From Customers Where State='" & state & "'", conn

You can easily see that the code in Example 8-4 is shorter than the code in Example 8-3.

Years ago, programmers would boast about how many thousands of lines of code they had written. I suppose now the fashion is for programmers to talk about how many lines of code they avoided writing!