IsMissing Function

Returns a Boolean value indicating whether an optional Variant parameter was passed to a procedure.

Syntax

IsMissing(argname)

Parameters

• argname (Required): Name of an optional Variant parameter

Return Value

Returns a Boolean: - True if the optional Variant argument was not passed - False if the optional Variant argument was passed - Only works with optional Variant parameters - Does not work with other data types (only Variant) - Does not work with ParamArray parameters - Returns False if Null, Empty, or any value was explicitly passed

Remarks

The IsMissing function is used to detect whether an optional parameter was omitted: - Only works with Optional Variant parameters - Cannot be used with typed optional parameters (Integer, String, etc.) - Returns True only when argument was completely omitted - Returns False if any value was passed (including Empty, Null, 0, "") - Useful for implementing functions with truly optional behavior - Different from checking IsEmpty - IsMissing detects omission - Empty can be explicitly passed: MyFunc Empty - IsMissing returns False - Common in COM/ActiveX programming for optional parameters - Allows distinguishing "not provided" from "provided as Empty/Null/0" - Must be used directly on parameter name, not on expressions - Parameter must be declared as Optional Variant - Cannot be used after assigning the parameter to another variable

Typical Uses

1. Optional Parameter Detection: Check if optional argument was provided
2. Default Value Logic: Apply different defaults based on whether parameter was omitted
3. API Compatibility: Maintain backward compatibility with varying parameter counts
4. COM Interop: Work with COM objects expecting optional parameters
5. Flexible Functions: Create functions with multiple optional behaviors
6. Database Operations: Handle optional WHERE clause parameters
7. Configuration Functions: Apply settings only when explicitly provided
8. Validation Logic: Distinguish "no value" from "zero value"

Basic Usage Examples

' Example 1: Simple optional parameter handling
Function Greet(name As String, Optional title As Variant) As String
    Dim greeting As String
    If IsMissing(title) Then
        greeting = "Hello, " & name
    Else
        greeting = "Hello, " & title & " " & name
    End If
    Greet = greeting
End Function
' Usage
Debug.Print Greet("Smith")              ' "Hello, Smith"
Debug.Print Greet("Smith", "Dr.")       ' "Hello, Dr. Smith"
Debug.Print Greet("Smith", Empty)       ' "Hello,  Smith" - Empty was passed!
' Example 2: Multiple optional parameters
Sub LogMessage(msg As String, Optional level As Variant, Optional timestamp As Variant)
    Dim output As String
    output = msg
    If Not IsMissing(level) Then
        output = "[" & level & "] " & output
    End If
    If Not IsMissing(timestamp) Then
        output = Format$(timestamp, "hh:nn:ss") & " " & output
    Else
        output = Format$(Now, "hh:nn:ss") & " " & output
    End If
    Debug.Print output
End Sub
' Usage
LogMessage "Application started"                    ' Uses current time, no level
LogMessage "Error occurred", "ERROR"                ' Uses current time, ERROR level
LogMessage "Debug info", "DEBUG", #1/1/2025 10:30#  ' Uses specified time
' Example 3: Distinguish missing from zero/empty
Function Calculate(value As Double, Optional multiplier As Variant) As Double
    If IsMissing(multiplier) Then
        ' No multiplier provided - return original value
        Calculate = value
    ElseIf multiplier = 0 Then
        ' Multiplier is explicitly 0 - return 0
        Calculate = 0
    Else
        ' Multiplier provided and non-zero
        Calculate = value * multiplier
    End If
End Function
Debug.Print Calculate(10)           ' 10 - multiplier missing
Debug.Print Calculate(10, 2)        ' 20 - multiplier is 2
Debug.Print Calculate(10, 0)        ' 0 - multiplier is explicitly 0
' Example 4: Database query with optional filter
Function GetRecords(table As String, Optional whereClause As Variant) As Recordset
    Dim sql As String
    sql = "SELECT * FROM " & table
    If Not IsMissing(whereClause) Then
        If whereClause <> "" Then
            sql = sql & " WHERE " & whereClause
        End If
    End If
    Set GetRecords = db.OpenRecordset(sql)
End Function
' Usage
Set rs = GetRecords("Customers")                    ' All records
Set rs = GetRecords("Customers", "State = 'CA'")    ' Filtered records
Set rs = GetRecords("Customers", "")                ' All records (empty string passed)

Common Patterns

' Pattern 1: Optional parameter with computed default
Function ProcessData(data As String, Optional maxLength As Variant) As String
    Dim limit As Long
    If IsMissing(maxLength) Then
        limit = Len(data)  ' Use full length when not specified
    Else
        limit = maxLength
    End If
    ProcessData = Left$(data, limit)
End Function
' Pattern 2: Cascading optional parameters
Sub SaveFile(filename As String, Optional path As Variant, Optional createBackup As Variant)
    Dim fullPath As String
    Dim backup As Boolean
    If IsMissing(path) Then
        fullPath = App.Path & "\" & filename
    Else
        fullPath = path & "\" & filename
    End If
    If IsMissing(createBackup) Then
        backup = False  ' Default to no backup
    Else
        backup = createBackup
    End If
    ' Save file logic...
End Sub
' Pattern 3: Count provided optional parameters
Function CountProvided(Optional arg1 As Variant, Optional arg2 As Variant, _
                       Optional arg3 As Variant) As Integer
    Dim count As Integer
    count = 0
    If Not IsMissing(arg1) Then count = count + 1
    If Not IsMissing(arg2) Then count = count + 1
    If Not IsMissing(arg3) Then count = count + 1
    CountProvided = count
End Function
' Pattern 4: Optional override parameter
Function GetSetting(key As String, Optional overrideValue As Variant) As Variant
    If IsMissing(overrideValue) Then
        ' Load from registry or config file
        GetSetting = LoadFromConfig(key)
    Else
        ' Use provided override
        GetSetting = overrideValue
    End If
End Function
' Pattern 5: Optional parameter affects behavior
Sub PrintReport(data As Variant, Optional includeHeader As Variant)
    If Not IsMissing(includeHeader) Then
        If includeHeader Then
            PrintHeader
        End If
    Else
        ' Default: always include header when not specified
        PrintHeader
    End If
    PrintData data
End Sub
' Pattern 6: Validation with optional strict mode
Function ValidateEmail(email As String, Optional strictMode As Variant) As Boolean
    Dim strict As Boolean
    If IsMissing(strictMode) Then
        strict = False  ' Default to lenient validation
    Else
        strict = strictMode
    End If
    If strict Then
        ValidateEmail = ValidateEmailStrict(email)
    Else
        ValidateEmail = ValidateEmailBasic(email)
    End If
End Function
' Pattern 7: Optional range parameters
Function GetSubstring(text As String, Optional startPos As Variant, _
                      Optional length As Variant) As String
    Dim start As Long
    Dim len As Long
    If IsMissing(startPos) Then
        start = 1
    Else
        start = startPos
    End If
    If IsMissing(length) Then
        len = Len(text) - start + 1
    Else
        len = length
    End If
    GetSubstring = Mid$(text, start, len)
End Function
' Pattern 8: Build parameter list dynamically
Function BuildCommand(command As String, Optional arg1 As Variant, _
                      Optional arg2 As Variant) As String
    Dim cmd As String
    cmd = command
    If Not IsMissing(arg1) Then
        cmd = cmd & " " & arg1
    End If
    If Not IsMissing(arg2) Then
        cmd = cmd & " " & arg2
    End If
    BuildCommand = cmd
End Function
' Pattern 9: Optional error handler callback
Function ProcessRecords(records As Variant, Optional errorHandler As Variant) As Long
    Dim count As Long
    Dim i As Long
    count = 0
    For i = LBound(records) To UBound(records)
        On Error Resume Next
        ProcessRecord records(i)
        If Err.Number <> 0 Then
            If Not IsMissing(errorHandler) Then
                ' Call custom error handler if provided
                Application.Run errorHandler, records(i), Err.Number
            End If
            Err.Clear
        Else
            count = count + 1
        End If
        On Error GoTo 0
    Next i
    ProcessRecords = count
End Function
' Pattern 10: Optional configuration object
Sub Initialize(Optional config As Variant)
    If IsMissing(config) Then
        ' Use default configuration
        LoadDefaultConfig
    Else
        ' Apply provided configuration
        ApplyConfig config
    End If
End Sub

Advanced Usage Examples

' Example 1: Flexible query builder
Public Class QueryBuilder
    Private m_sql As String
    Public Sub SelectFrom(table As String, Optional fields As Variant, _
                         Optional whereClause As Variant, _
                         Optional orderBy As Variant)
        If IsMissing(fields) Then
            m_sql = "SELECT * FROM " & table
        Else
            m_sql = "SELECT " & fields & " FROM " & table
        End If
        If Not IsMissing(whereClause) Then
            If whereClause <> "" Then
                m_sql = m_sql & " WHERE " & whereClause
            End If
        End If
        If Not IsMissing(orderBy) Then
            If orderBy <> "" Then
                m_sql = m_sql & " ORDER BY " & orderBy
            End If
        End If
    End Sub
    Public Function GetSQL() As String
        GetSQL = m_sql
    End Function
End Class
' Usage:
Dim qb As New QueryBuilder
qb.SelectFrom "Customers"                           ' SELECT * FROM Customers
qb.SelectFrom "Customers", "Name, Email"            ' SELECT Name, Email FROM Customers
qb.SelectFrom "Customers", , "State = 'CA'"         ' SELECT * FROM Customers WHERE State = 'CA'
qb.SelectFrom "Customers", "Name", "Active = 1", "Name"  ' Full query
' Example 2: Logger with flexible output
Public Class Logger
    Private m_logFile As String
    Public Sub Initialize(Optional filename As Variant)
        If IsMissing(filename) Then
            m_logFile = App.Path & "\app.log"
        Else
            m_logFile = filename
        End If
    End Sub
    Public Sub Log(message As String, Optional level As Variant, _
                   Optional timestamp As Variant, Optional writeToFile As Variant)
        Dim output As String
        Dim logLevel As String
        Dim useTimestamp As Date
        Dim toFile As Boolean
        ' Determine log level
        If IsMissing(level) Then
            logLevel = "INFO"
        Else
            logLevel = level
        End If
        ' Determine timestamp
        If IsMissing(timestamp) Then
            useTimestamp = Now
        Else
            useTimestamp = timestamp
        End If
        ' Determine output destination
        If IsMissing(writeToFile) Then
            toFile = True  ' Default to file
        Else
            toFile = writeToFile
        End If
        ' Build output
        output = Format$(useTimestamp, "yyyy-mm-dd hh:nn:ss") & " [" & _
                 logLevel & "] " & message
        ' Write output
        Debug.Print output
        If toFile Then
            WriteToLogFile output
        End If
    End Sub
    Private Sub WriteToLogFile(text As String)
        Dim fileNum As Integer
        fileNum = FreeFile
        Open m_logFile For Append As fileNum
        Print #fileNum, text
        Close fileNum
    End Sub
End Class
' Example 3: HTTP request builder
Public Class HttpRequest
    Public Function Get(url As String, Optional headers As Variant, _
                       Optional timeout As Variant) As String
        Dim http As Object
        Set http = CreateObject("MSXML2.XMLHTTP")
        ' Set timeout if provided
        If Not IsMissing(timeout) Then
            http.SetTimeouts timeout, timeout, timeout, timeout
        End If
        http.Open "GET", url, False
        ' Add custom headers if provided
        If Not IsMissing(headers) Then
            Dim headerList As Variant
            Dim i As Long
            If IsArray(headers) Then
                For i = LBound(headers) To UBound(headers)
                    http.setRequestHeader Split(headers(i), ":")(0), _
                                         Trim$(Split(headers(i), ":")(1))
                Next i
            End If
        End If
        http.send
        Get = http.responseText
    End Function
End Class
' Example 4: Validation framework
Public Class Validator
    Public Function Validate(value As Variant, Optional minValue As Variant, _
                            Optional maxValue As Variant, _
                            Optional pattern As Variant) As Boolean
        Dim isValid As Boolean
        isValid = True
        ' Check minimum value
        If Not IsMissing(minValue) Then
            If value < minValue Then
                isValid = False
                Exit Function
            End If
        End If
        ' Check maximum value
        If Not IsMissing(maxValue) Then
            If value > maxValue Then
                isValid = False
                Exit Function
            End If
        End If
        ' Check pattern (if string)
        If Not IsMissing(pattern) Then
            If VarType(value) = vbString Then
                isValid = MatchesPattern(CStr(value), CStr(pattern))
            End If
        End If
        Validate = isValid
    End Function
    Private Function MatchesPattern(text As String, pattern As String) As Boolean
        ' Simple pattern matching implementation
        MatchesPattern = (text Like pattern)
    End Function
End Class

Error Handling

IsMissing itself does not raise errors, but improper usage can:

' ERROR: Cannot use with non-Variant optional parameters
Function BadExample(Optional value As Integer) As Boolean
    BadExample = IsMissing(value)  ' Compile error!
End Function
' CORRECT: Must use Optional Variant
Function GoodExample(Optional value As Variant) As Boolean
    GoodExample = IsMissing(value)  ' Works correctly
End Function
' ERROR: Cannot use after assignment
Function BadExample2(Optional value As Variant) As Boolean
    Dim temp As Variant
    temp = value
    BadExample2 = IsMissing(temp)  ' Always False! Use value directly.
End Function
' CORRECT: Use parameter directly
Function GoodExample2(Optional value As Variant) As Boolean
    GoodExample2 = IsMissing(value)  ' Works correctly
End Function

Performance Considerations

• Fast Operation: IsMissing is a very fast check with minimal overhead
• Compile-Time Check: VB6 can optimize IsMissing checks
• Variant Overhead: Optional Variant parameters have more overhead than typed parameters
• Use Sparingly: Only use when you truly need to distinguish missing from provided

Best Practices

1. Use Only with Variant: IsMissing only works with Optional Variant parameters
2. Direct Check: Always check IsMissing directly on the parameter name
3. Document Behavior: Clearly document what happens when parameter is omitted
4. Provide Defaults: Consider if default values on Optional parameters would work instead
5. Avoid Complexity: Don't overuse optional parameters - can make APIs confusing
6. Check Early: Test IsMissing before using the parameter value
7. Combine Wisely: Can combine with IsEmpty, IsNull checks for complete validation
8. API Design: Use for true optional behavior, not just to avoid typing

Comparison with Related Functions

IsMissing vs IsEmpty

Sub Test(Optional param As Variant)
    ' Case 1: Parameter not provided
    ' Call: Test
    Debug.Print IsMissing(param)  ' True - was not provided
    Debug.Print IsEmpty(param)    ' True - is Empty
    ' Case 2: Explicit Empty passed
    ' Call: Test Empty
    Debug.Print IsMissing(param)  ' False - was provided (even though Empty)
    Debug.Print IsEmpty(param)    ' True - is Empty
    ' Case 3: Null passed
    ' Call: Test Null
    Debug.Print IsMissing(param)  ' False - was provided
    Debug.Print IsEmpty(param)    ' False - is Null, not Empty
    ' Case 4: Zero passed
    ' Call: Test 0
    Debug.Print IsMissing(param)  ' False - was provided
    Debug.Print IsEmpty(param)    ' False - has value (0)
End Sub

Platform and Version Notes

• Available in all VB6 versions
• Part of VBA core functions
• Returns Boolean type
• Only works with Optional Variant parameters
• Compile-time requirement: parameter must be Optional Variant
• Cannot be used with ParamArray
• Common in COM/ActiveX programming

Limitations

• Only works with Optional Variant parameters (not Integer, String, etc.)
• Cannot be used on expressions, only on parameter names
• Cannot be used after assigning parameter to another variable
• Does not work with ParamArray parameters
• Cannot detect which parameter in ParamArray was omitted
• Requires Variant type (introduces type safety concerns)
• Can make function signatures more complex

Related Functions

• IsEmpty: Check if Variant is uninitialized (Empty)
• IsNull: Check if Variant is Null
• VarType: Get detailed Variant type information
• TypeName: Get type name as String