How can I check if optional arguments are specified or not?

Question

How to check if additional 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 

Answer 1

Use IsMissing :

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

However, if I remember correctly, this does not work when providing a default value for the argument, and in any case, it makes using the default argument rather redundant.

Answer 2

You can use the IsMissing () function. But this one only works with the Variant data type.

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

Answer 3

If you use a string or a numeric 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 the use of non-invariant variables.

Answer 4

If IsMissing (arg) Then ...

Answer 5

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 

Answer 6

In 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 also be used with other data types, just keep in mind that Zero is considered neither Null nor Zero-Length, so nz(0,"") still returns 0.

Answer 7

"IsMissing" ... Thought there should be a way. Thanks everyone!

SQL has an In () function, where you can pass a few arguments to see if there is a target value in the list. I always liked this as a solution, so here is my opinion, I hope this 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 why I needed IsMissing; not working without it.

Answer 8

Most of them refer to the type of variant or check if the value is empty.

However, sometimes you want to check if a range, workbook, sheet, or object of another type has been transferred, without checking things like sheet names.

In this case:

 DesiredRange is Nothing 

Returns a boolean value. For example:

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