How do I test if optional arguments are supplied or not?

Question

How do I test if optional arguments are supplied or not? -- in VB6 / VBA

Function func (Optional ByRef arg As Variant = Nothing)

    If arg Is Nothing Then   <----- run-time error 424 "object required"
        MsgBox "NOT SENT"
    End If

End Function 

Solution 1

Use IsMissing:

If IsMissing(arg) Then
    MsgBox "Parameter arg not passed"
End If

However, if I remember correctly, this doesn’t work when giving a default for the argument, and in any case it makes using the default argument rather redundant.

Solution 2

You can use the IsMissing() Function. But this one only works with the Variant datatype.

Sub func(Optional s As Variant)
   If IsMissing(s) Then
      ' ...
   End If
End Sub

Solution 3

If you are using a string or number variable you can check the value of the variable. For example:

Function func (Optional Str as String, Optional Num as Integer)

If Str = "" Then
    MsgBox "NOT SENT"
End If

If Num = 0 Then
    MsgBox "NOT SENT"
End If

End Function

This allows you to use non-variant variables.

Solution 4

You can use something like:

function func(optional vNum as integer:=&HFFFF) '&HFFFF value that is NEVER set on vNum

If vNum = &HFFFF Then
    MsgBox "NOT SENT"
End If

End Function

Solution 5

If IsMissing(arg) Then ...

View more solutions

Comments

[Jon Fournier]

Also I think IsMissing only works if the argument is declared as a variant

[Konrad Rudolph]

@Jon: true, since IsMissing is implemented in terms of a flag in the VARIANT struct (IIRC, VT_EMPTY). I didn’t mention this since the OP’s question already used Variant anyway.

[Konrad Rudolph]

It cannot distinguish perfectly valid usage though: func("", 0) will incorrectly flag up unset parameters. In general there is no way to have this distinction without using Variants.

[OSUZorba]

@KonradRudolph That is a very good point. You'd want to make sure you never used it in a situation were you might send an empty string or a number of zero.

[Konrad Rudolph]

Actually you don’t need and shouldn’t use IsMissing here. Instead, this is an application for a ParamArray.

[Marcucciboy2]

The tricky part is that not every type of object can be checked using is Nothing whereas any object can be stored in a Variant type. So to be clear, this is a solution for objects like Ranges or Worksheets but not for other types like String

[Hasan Merkit]

If Num = 0 Then is problem for Int values. What you do if user set argument to 0 manually?

[Eleshar]

This one is clever. The recommended "isMissing" works only the variant data type while this principle is fairly universal.

[DaveP]

This is is the correct answer per Microsoft as "A procedure cannot detect at run time whether a given argument has been omitted... set an unlikely value as the default." docs.microsoft.com/en-us/dotnet/visual-basic/programming-gui‌​de/…