VBA Sub optional parameters with default values

Question

I am trying to write a VBA sub that allows optional parameters with default values. I tried the sample code from Microsoft Docs - Optional Parameters (Visual Basic) but it leads to the sub not showing on list of available subs (i.e. from View Macros).

Sub notify(ByVal company As String, Optional ByVal office As String = "QJZ")
    If office = "QJZ" Then
        Debug.WriteLine("office not supplied -- using Headquarters")
        office = "Headquarters"
    End If
    ' Insert code to notify headquarters or specified office.
End Sub

The sub declaration I tried, but couldn't get to appear, was:

Sub HighlightByFont( _
        Optional ByVal highlightFont As Variant = "", _
        Optional ByVal highlightColor As Variant = "", _
        Optional ByVal highlightText As Variant = "", _
        Optional ByVal matchWildcards As Variant = False, _
        Optional ByVal useGUI As Variant = False, _
        Optional ByVal highlightBold As Variant = False, _
        Optional ByVal highlightItalic As Variant = False)

For now, I have had to settle for the below with IsMissing logic:

Sub HighlightByFont( _
        Optional ByVal highlightFont As Variant, _
        Optional ByVal highlightColor As Variant, _
        Optional ByVal highlightText As Variant, _
        Optional ByVal matchWildcards As Variant, _
        Optional ByVal useGUI As Variant, _
        Optional ByVal highlightBold As Variant, _
        Optional ByVal highlightItalic As Variant)

Is it (still^†) possible, and if so, how:

1. to set up the parameter declaration?
2. to get it to appear in the View Macros list?

Environment:

• Word 2016 x64
• Windows 10

^{† All references, including SO answers related to VBA optional paramaters are from 2015.}

Answer 1

Optional Values Parameter Declaration

Yes, optional parameters with default values still works as late as Word 2016. The VBA reference states:

The arglist argument has the following syntax and parts:

[ Optional ] [ ByVal | ByRef ] [ ParamArray ] varname [ ( ) ] [ As type ] [ = defaultvalue ]

Optional Optional. Keyword indicating that an argument is not required. If used, all subsequent arguments in arglist must also be optional and declared by using the Optional keyword. Optional can't be used for any argument if ParamArray is used.

defaultvalue Optional. Any constant or constant expression. Valid for Optional parameters only. If the type is an Object, an explicit default value can only be Nothing.

Sub OptParam_Test_1()
  'Shows in Macro list
End Sub

Sub OptParam_Test_2(param)
  'Does not show in Macro list
    Debug.Print (param) 'Output for OptParam_Test_2 "hello": hello
End Sub

Sub OptParam_Test_3(Optional param)
  'Shows in Macro list
    Debug.Print (param) 'Output: hello
End Sub

Sub OptParam_Test_4(Optional param As String = "hello")
  'Does not show in Macro list
    Debug.Print (param) 'Output: hello
End Sub

Sub OptParam_Test_5(Optional param As Variant = "hello again")
  'Does not show in Macro list
    Debug.Print (param) 'Output: hello again
End Sub

An unexpected interface behavior when declaring parameters with default values is the sub will stop appearing in the Macros list.

This should not be interpreted as the sub having an issue or not being available for use.

• Understandably, it seems to have been a design decision that subs with parameters need to be called from another sub or the Immediate window¹ and hence don't show in Macros list
• Logically, subs with only/all Optional parameters (and without default values^†) do show² in the Macros list since invocation, by definition, doesn't require an explicit argument to be supplied
• ^†Inconsistently, subs with Optional parameters and default values provided do not show in the Macros list

Invocation Options

Two options for invoking subs containing a parameter with default value are:

1. Declare a parameterless calling sub which calls the sub in question. This calling sub will appear in the Macros list.

    Sub OptParam_Test_4(Optional param As String = "hello")
      'Does not show in Macro list
        Debug.Print (param) 'Output: hello
    End Sub

    Sub OptParam_Test_4_()
      'Shows in Macro list
      OptParam_Test_4
      'Output: hello
    End Sub

2. Use the Immediate window¹ to call the sub in question

^{1Developer - Visual Basic - View - Immediate Window (Ctrl+G)}

^{2contrary to various comments on mulitple SO posts related to this topic}

Answer 2

Despite the fact, that the Sub with (all) arguments Optional is not visible from Macro List, you can type-in the name of that Sub and .. voila: Run button enables itself and Sub can be Run.

Answer 3

The link you have provided is to a help page for VB Net and Not VBA. VBA and VB .Net are similar but have very different use cases. VBA is the built in scripting language used by Microsoft Office applications. VB Net is a full bore .Net Language which has its roots in VBA but is not used by Office Application unless you write specific VSTO addins or applications.

Optional arguments in VBA work fine. The VBA version of the code sample you provided above is.

Sub notify(ByVal company As String, Optional ByVal office As String = "QJZ")
    If office = "QJZ" Then
        Debug.print "office not supplied -- using Headquarters"
        office = "Headquarters"
    End If
    ' Insert code to notify headquarters or specified office.
End Sub

You could also do us all a bit of a favour and learn to use the line break so that

Sub HighlightByFont(Optional ByVal highlightFont As Variant = "", Optional ByVal highlightColor As Variant = "", Optional ByVal highlightText As Variant = "", Optional ByVal matchWildcards As Variant = False, Optional ByVal useGUI As Variant = False, Optional ByVal highlightBold As Variant = False, Optional ByVal highlightItalic As Variant = False)

is written as

Sub HighlightByFont _
( _
    Optional ByVal highlightFont As Variant = "", _
    Optional ByVal highlightColor As Variant = "", _
    Optional ByVal highlightText As Variant = "", _
    Optional ByVal matchWildcards As Variant = False, _
    Optional ByVal useGUI As Variant = False, _
    Optional ByVal highlightBold As Variant = False, _
    Optional ByVal highlightItalic As Variant = False _
)

You should also be aware that any optional parameters defined with a default can never be missing and that sometimes IsMissing is the better option to use because its not possible to provide a sensible default.