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 

Magpie

Answer 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.

Hyman

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

@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. – Hyman

Answer 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

Manna

Answer 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.

Monia

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. – Hyman

@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. – Monia

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

Answer 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

Cymophane

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

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." learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/… – Irrecoverable

Doesn't work in VBA, evaluates to -1 – Turpentine

Answer 5

Most of these refer to the variant type, or test if a value is blank.

However, sometimes you want to check if a range, workbook, worksheet, or other type of object is not passed, without checking things like sheetnames.

In that case:

DesiredRange is Nothing

Returns a boolean. For example:

    If DestinationRange Is Nothing Then
        MsgBox "Need a destination range when importing data"
    Else
        'We're happy
    End If

Stalker

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 – Coreencorel

Answer 6

If IsMissing(arg) Then ...

Susurrant

Answer 7

With a variant I would use the NZ function:

Function func (Optional ByRef arg As Variant = Nothing)
    If nz ( arg, 0 ) = 0 Then
        MsgBox "NOT SENT"
    End If
End Function 

It can be used with other data types too, just keep in mind that Zero counts as neither Null nor Zero-Length, so nz(0,"") still returns 0.

Retrogression

Answer 8

"IsMissing(var)" for variants
"StrPtr(var) = 0" for strings

For other data types there's no perfect solution, you can only test for their default values, therefore not distinguishing between a not passed argument and a passed argument valued as default.

Lanyard

Answer 9

"IsMissing"...Figured there would have to be a way. Thanks all!

SQL has a function, In(), where you can pass multiple arguments to see if the target value is in the list. I've always liked that as a solution, so here's my take on that, hope it helps:

Public Function IsIn(ByVal TestVal, ByVal VersusVal1, _
            Optional ByVal VersusVal2, Optional ByVal VersusVal3, _
            Optional ByVal VersusVal4, Optional ByVal VersusVal5, _
            Optional ByVal VersusVal6, Optional ByVal VersusVal7, _
            Optional ByVal VersusVal8, Optional ByVal VersusVal9, _
            Optional ByVal VersusVal10, Optional ByVal VersusVal11, _
            Optional ByVal VersusVal12, Optional ByVal VersusVal13, _
            Optional ByVal VersusVal14, Optional ByVal VersusVal15, _
            Optional ByVal VersusVal16, Optional ByVal VersusVal17, _
            Optional ByVal VersusVal18, Optional ByVal VersusVal19, _
            Optional ByVal VersusVal20) As Boolean

Dim CheckVals(1 To 20) as Variant
VersusVals(1) = VersusVal1
VersusVals(2) = VersusVal2
VersusVals(3) = VersusVal3
VersusVals(4) = VersusVal4
VersusVals(5) = VersusVal5
VersusVals(6) = VersusVal6
VersusVals(7) = VersusVal7
VersusVals(8) = VersusVal8
VersusVals(9) = VersusVal9
VersusVals(10) = VersusVal10
VersusVals(11) = VersusVal11
VersusVals(12) = VersusVal12
VersusVals(13) = VersusVal13
VersusVals(14) = VersusVal14
VersusVals(15) = VersusVal15
VersusVals(16) = VersusVal16
VersusVals(17) = VersusVal17
VersusVals(18) = VersusVal18
VersusVals(19) = VersusVal19
VersusVals(20) = VersusVal20

On Error Goto 0

IsIn = False

For x = 1 To 20
   If Not IsMissing(VersusVals(x)) Then
      If TestVal = VersusVals(x) Then
         IsIn = True
         Exit For
      End If
   End If
Next x

End Function

So, that's obviously why I needed "IsMissing"; doesn't work without it.

Castrate

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