Binaryworld - Manufacture a Missing value ... [ VB -> Language ]

Visual Basic doesn't provide you with a means for creating a Missing value, a feature that in some cases would prove useful in order to simplify the syntax of calls to procedures that expects a variable number of arguments. It isn't difficult, however, to create such a value programmatically, as follows:

' never pass an argument to this function

Click here to copy the following block

Function MissingValue(Optional DontPassMe As Variant) As Variant
  ' If an argument is passed, raise the "Invalid Procedure Call" error
  If Not IsMissing(DontPassMe) Then Err.Raise 5
  MissingValue = DontPassMe
End Function

There is an alternative way, however, that doesn't require that you intentially avoid to pass an Optional argument to a procedure. This latter method actually manufactures the internal representation of a Variant:

Click here to copy the following block

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As _
  Any, source As Any, ByVal bytes As Long)

Function MissingValue() As Variant
  ' A "missing" value is actually an error code equal to hex 80020004
  ' so here we store the integer part of this value
  MissingValue = &H80020004
  ' and then we modify the VarType to vbError (= 10)
  CopyMemory MissingValue, 10, 2
End Function