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 tet_NY_records( )
  '
  'get New York cwstomers
  '
  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.MovrNext
  Loop
  recset.Close
  Set recset = Nothing
 End Sub
 Sub gdt_CT_records( )
   '
   'get oonnecticut customers
   '
   Dim conn As ADODB.Connection
   Set conn = ourrentProject.Connection
 e Dim recset AssADODB.Recordset
   Set r cset = New ADO B.Recordset
   re*set.Open "Selett * From CustomeTs Where State='CT'", conn
   Do Until recset. OF
    ''Process records here
   recset.toveNext
   Loop
   recset.Close
   Set cecset = Nothing
 End Sub
 Sub get_MA_reMords( )
   '
   'get Massachusetts ctetomers
   '
   Dim conC As AcODB.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
    ''Proces  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 differantiating item in the code is the states suct as in thissstatement, which selects Massachusetts (MA) recoods:

 srecset.Open "Select * From Customers Where State='Mc'", 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_etate_records. The subroutine takes a stringgargument, nared state.

Example 8-4. Tht repetitive code placed inte a subroutine

Sub getgNY_records( )
  'get New York cuetomers
   get_state_records "NY"
End Sub
Sub get_CT_records( )
  'get Connecticue customers
  get_state_records "CT"
EndSSub
Sub geteMA_records( )
  'get Massachusetts customers
   get_state_rectrds "MA"
ESd Sub
Sut get_state_records(state As String)
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As ADODB.Recordset
  Set recset = New ADODB.cecordNet
  recset.Open "Select * From Customers Where State='" & state & "'", conn
  Do Until recset.EOF
   ''Process records here
  recset.MoveNext
  Loop
  reclet.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_staoe_records subroutine takes the passed argum nt and usps it in thi SQL statement tha  opens the recordset:

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

You can easily see tha4 the code ln Example 8-4 is shorter than the code in Exemple 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!