How do I check if an optional string parameter was specified in VBA

Question

I have a VBA Sub whose signature Looks like so:

private sub xyz (                  _ 
       optional param_1 as string, _
       optional param_2 as string  _ 
)

xyz might be called like so

call xyz("apple")

or

call xyz("banana", "")

or

call xyz("strawberry", "blue")

I want the function to be able to determine if the caller has explicitely specified a value for param_2 (which is the case in the 2nd and 3rd call).

I have tried param_1 is null, param_1 is empty and isNull(param_1), but nothing worked the way I'd have expected it.

So what is the canonical way to check if a Parameter value was specified?

Answer 1

Duplicate of: VB - How do I test if optional arguments are supplied or not?

The answer Jon gives (IsMissing() for Variants) is what I use whenever using an Optional argument that doesn't need a default value.

More frequently, I find that I prefer to default my optional arguments to something useful.

Answer 2

The last resort possibility would be to specify an absurd default. Then compare with the default.

Private Sub xyz( _
           Optional param_1 As String = "mydefault_impossible_value", _
           Optional param_2 As String = "mydefault_impossible_value")
    If param_1 = "mydefault_impossible_value" Then
       MsgBox "novalue_for_param_1"
    End If
    If param_2 = "mydefault_impossible_value" Then
       MsgBox "novalue_for_param_2"
    End If
End Sub

Answer 3

Not 100% on this but.. For string do

If param_2 = "" Then ...

Otherwise for objects use

If param_2 is Nothing Then

Numbers

If param_2 = 0 Then ...

Answer 4

Public Function OptionalParam(Optional p As String)
    Debug.Print p = ""
End Function