87

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
Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
Robin Rodricks
  • 110,798
  • 141
  • 398
  • 607

9 Answers9

113

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.

Ould Abba
  • 813
  • 1
  • 12
  • 25
Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
  • 33
    Also I think IsMissing only works if the argument is declared as a variant – Jon Fournier Nov 03 '09 at 15:24
  • 5
    @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 Nov 03 '09 at 17:38
25

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
Florian Fankhauser
  • 3,615
  • 2
  • 26
  • 30
10

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.

OSUZorba
  • 1,099
  • 11
  • 13
  • 3
    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 `Variant`s. – Konrad Rudolph Aug 23 '16 at 10:21
  • @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. – OSUZorba Aug 29 '16 at 05:21
  • If Num = 0 Then is problem for Int values. What you do if user set argument to 0 manually? – Hasan Merkit Apr 07 '21 at 11:47
7

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
Luka Kerr
  • 4,161
  • 7
  • 39
  • 50
user7238479
  • 79
  • 1
  • 1
  • This one is clever. The recommended "isMissing" works only the variant data type while this principle is fairly universal. – Eleshar May 03 '21 at 11:28
  • 1
    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." https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/optional-parameters – DaveP Dec 01 '21 at 16:10
  • Doesn't work in VBA, evaluates to -1 – drgs Jul 06 '23 at 14:28
4

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
Selkie
  • 1,215
  • 1
  • 17
  • 34
  • 1
    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 `Range`s or `Worksheet`s but not for other types like `String` – Marcucciboy2 Aug 23 '19 at 18:44
3

If IsMissing(arg) Then ...

Pontus Gagge
  • 17,166
  • 1
  • 38
  • 51
2

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
0

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

6diegodiego9
  • 503
  • 3
  • 14
-1

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

RLE
  • 1