Using Optional Arguments

You can specify that arguments to a procedure be optional by using the Optional keyword in the argument list. If you specify an optional argument, all subsequent arguments in the argument list must also be optional and must be declared with the Optional keyword. Optional arguments must be of the Variant data type.

For example, the following procedure takes two required arguments and two optional arguments. The procedure selects a range of cells on the worksheet named "SampleText" and then optionally makes the text red and sorts the range by the values in the first column.

Sub FormatList(startRow As Integer, startCol As Integer, _
        Optional redText, Optional sortList)
    If IsMissing(redText) Then
        redText = False
    Else
        redText = CBool(redText)
    End If
    If IsMissing(sortList) Then
        sortList = False
    Else
        sortList = CBool(sortList)
    End If
    Set myCells = Worksheets("SampleText") _
        .Cells(startRow, startCol).CurrentRegion
    If redText Then
        textColor = 3
    Else
        textColor = xlAutomatic
    End If
    myCells.Font.ColorIndex = textColor
    If sortList Then myCells.Sort key1:=myCells.Cells(1, 1)
End Sub

The IsMissing function is used to determine whether an optional argument was passed to the procedure. For more information, see "IsMissing" in Help.

The following example calls FormatList, providing both the required and optional arguments.

Sub DoList()
    FormatList 2, 2, False, True
End Sub

The following example provides the second optional argument.

Sub DoList()
    FormatList 2, 2, , True
End Sub

If your procedure has several optional arguments, you may want to use named arguments so that you can provide any or all of the arguments, in any order. For more information, see the following section.

Note

Sub procedures with optional arguments won't appear in the Macro dialog box (Tools menu). (No Function procedures appear in the Macro dialog box.) You can, however, see procedures with optional arguments in the Object Browser.