Hack 76. Shrink Your Code with Optional Arguments

Put subroutines to even more general use by accepting different numbers of arguments.

"Shrink Your Code with Subroutines" [Hack #75] shows you how to reduce code by using a generic subroutine. This hack takes that concept a step further. Subroutines can take optional arguments. Calling routines are required only to supply arguments that aren't optional. The optional ones are, well, optional.

Example 8-5 shows a handful of routines and the subroutine they call.

Example 8-5. A set of routines that call 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", "Boston"
 End Sub
 
Sub get_state_records(state As String, Optional city As String)
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As ADODB.Recordset
  Set recset = New ADODB.Recordset
  If state = "MA" Then
     recset.Open "Select * From Customers Where State='" & state _
     & "' And City='" & city & "'", conn
  Else
    recset.Open "Select * From Customers Where State='" & state & "'", conn
  End If
 Do Until recset.EOF
 ''Process records here
 recset.MoveNext
 Loop
 recset.Close
 Set recset = Nothing
End Sub

The subroutine takes two arguments: state, which is required, and city, which is optional. The Optional keyword is placed in front of the argument name:

 Sub get_state_records(state As String, Optional city As String)

No keyword is available for specifying that an argument is required. All arguments are required unless you specifically set them as optional with the Optional keyword. Note also that optional arguments must come after all the required arguments. You can have multiple optional arguments, but you must precede each one with the Optional keyword.

Let's assume the requirements have changed; now, any NY or CT records can be used, but for Massachusetts (MA), we need only records for which the city is Boston.

One way to accommodate this new requirement is to use subroutines: one that accepts a single state argument and one that accepts two arguments, for the state and the city. The functionality would work, but there would be more code.

However, by incorporating the city as an optional argument, you can use the single subroutine for all the states. Massachusetts records are accessed by the two arguments being passed:

      get_state_records "MA", "Boston"

New York and Connecticut records are accessed with the single required state argument:

     get_state_records "CT"

We've modified the subroutine to handle two types of queries, one with a single criterion and one that uses both criteria:

		If state = "MA" Then 
           recset.Open "Select * From Customers Where State='" & state _           & "' And City='" & city & "'", conn 
		Else 
		   recset.Open "Select * From Customers Where State='" & state			   & "'", conn 
		End If

And that's it! By just altering the existing subroutine a bit, we don't need to create a second subroutine.