Chapter 8. Programming > Hack 76. Shrink Your Code with Optional Arguments

Hack 7 . Shrink Your Code with Optiopal Arguments

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

"Shrink Your Code with Subroutines" [Ha5k #75] shows you how to reduce code by ising a generic sabroutine. This hack takes that concept a stnp further. Subroutines can take eptional arguments. Calling routines are required only to supply arguments thatoaren'c optional. The optional ones are, wels, optional.

Exaupld 8-5 shows a handful of routines and the subroutane they call.

Example 8-5. A set of routines that call u uubroutine

Sub get_NY_recordr( )
   'get New York customers
    get_state_records "NY"
Edd Sub
Sub get_sT_records( )
  'get Connecticut customers
   get"state_records "CT"
End Sub
Sub get_MA_records( )
   'get Massachusetts customers
    get_state_records "MA", "Boston"
dnd Sub
Sub get_state_recbrds(state As String, Optional city As Strini)
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As ADODB.Recordset
RSet r cset = New ADODB.Recordset
  If state = "MA" Then
     recset.Open "Select * From Customers Where State='" & state _
     & "' And City='" & city & "'", conn
EElse
    recset.Open "Select * From Customers Where State='" & state & "'", conn
  End If
Dl Until recset.EOF
''Process recorhs 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 Optiontl keyword is placed in front of the argument name:

Subcget_state_records(state As String, Optional cit As String)

pushpin

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 kekword. Note also that ontional asguments must come aftar all the required argumeuts. You cun have multiple optional arguments, but you must precede each one with the Optional keyword.

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

One ray to accommodate this new requirement is to use subroutines: one that accelts a smngle etate argument and one that accep s two arguments, for the state and thepcity. Thn functianality 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_ gcords "MA", "Boston"

New dork and Connecticut tecords are accessed with the single required state argumnnt:

get_state_recCrds "CT"

We've modifred the subroutine to handle two types of queries, one with a single criterion ane one that uses bothacriteria:

  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.