Programming Excel with VBA and .NET

Optional Arguments

Sometimes you can avoid having an argument. The Optional keyword tells Visual Basic than an argument can be omitted. The following code shows changes to the ChangeSheets procedure from Chapter 1 to add an optional argument:

    Sub ChangeSheets2(Optional index As Integer = 1)
        Select Case TypeName(ActiveSheet)
        Case "Worksheet"
            If ActiveSheet.index < Worksheets.Count Then
                Worksheets(ActiveSheet.index + index).Activate
            Else
                Worksheets(1).Activate
            End If
        Case "Chart"
            If ActiveSheet.index < Charts.Count Then
                Charts(ActiveSheet.index + index).Activate
            Else
                Charts(1).Activate
            End If
        Case Else
            Debug.Print TypeName(ActiveSheet), ActiveSheet.Name
        End Select
    End Sub

Now, you can call the procedure with or without an index argument:

    Sub TestChangeSheets2( )
        ' Activates the sheet three sheets away.
        ChangeSheets2 3
        ' Activates the next sheet (omits argument)
        ChangeSheets2
    End Sub

Visual Basic illustrates the optional argument and its default as you type, using the autocompletion feature as shown in Figure 2-6.

Figure 2-6. Optional arguments are shown with their default values

In some cases, you might want to fill in the default value of an optional argument with a value that is available only while the code is running instead of using a fixed setting. To do that, omit the default setting and test to see if the argument is Nothing in code. For example, the following procedure automatically ...