Test For Omission of Optional Variant Parameters
In certain called functions it is advantageous to omit parameters if they don't apply. The code below shows how to test to see if the user omitted a parameter. To function properly, the passed parameters must be of the "variant" type. If they are other types, the result will be null and the "IsMissing" function will not be triggered.
Program Code
Option Explicit
Public Sub TestOptionalParameter()
' *****************************************************
' Note: For the IsMissing Function to work properly,
' The Passed Parameters Must Be of type Variant
' *****************************************************
Dim varParam1 As Variant
Dim varParam2 As Variant
Dim varParam3 As Variant
varParam1 = "Param1"
varParam2 = "Param2"
varParam3 = "Param3"
Call ParameterTest(varParam1)
Call ParameterTest(varParam1, varParam2)
Call ParameterTest(varParam1, , varParam3)
End Sub
Public Sub ParameterTest(Param1 As Variant, Optional Param2 As Variant, Optional Param3 As Variant)
If IsMissing(Param1) Then
MsgBox ("Param1 Is Missing")
Else
MsgBox ("Param1 Value is: " & Param1)
End If
If IsMissing(Param2) Then
MsgBox ("Param2 is Missing")
Else
MsgBox ("Param2 Value is: " & Param2)
End If
If IsMissing(Param3) Then
MsgBox ("Param3 is Missing")
Else
MsgBox ("Param3 Value is: " & Param3)
End If
End Sub
