The Missing Keyword in VBA

Missing is one of many ways to express the concept of nothingness in VBA.  For the others, check out An Article About Nothing.

According to the VBA Language Specification, Missing is:

A single distinguished value corresponding that is used to indicated that no value was passed corresponding to an explicitly declared optional parameter.

If that grammatically horrifying definition makes your head hurt as much as mine, allow me to clarify:

In VBA, "Missing" is the default value assigned to an optional variant parameter if you don't assign a default value of your own.

IsMissing() Function

We check for the Missing value using the IsMissing() function.  A call to IsMissing(Foo) returns True only if all of the following conditions are met:

• Foo is an Optional parameter
• Foo has a Variant data type
• Foo has No Default Value
• Foo is Not a ParamArray
• The calling code did not provide a value for Foo

Missing Is Not a Keyword

Unlike the keyword Empty, Missing is not a reserved identifier in VBA.

Thus, if you try to compare an optional parameter to Missing in code, you'll get a "Variable not defined" compile error (unless you've declared your own Missing variable or constant...or if you don't have Option Explicit turned on):

Option Explicit

Sub DemoMissing(Optional Foo As Variant)
    Debug.Print Foo = Missing  '<-- Compile error: Variable not defined
End Sub

Given this information, I should explain this article's title.  Chances are you read it wrong.  This article is not about the Missing keyword in VBA.  This article is about the missing keyword in VBA: Missing.  Get it?  

"Missing" is the keyword that seems like it should exist but doesn't.

Variants Can Be Declared Explicitly or Implicitly

One thing to keep in mind with the Variant requirement is that a parameter with no explicitly assigned data type will implicitly be assigned the Variant type.

In other words, the following two method declarations are identical in the eyes of the compiler:

Sub DemoExplicit(Optional Foo As Variant)
    Debug.Print IsMissing(Foo)
End Sub

Sub DemoImplicit(Optional Foo)
    Debug.Print IsMissing(Foo)
End Sub

Null and Missing are Not the Same

Bear in mind that Null is not the same as Missing.

For example, if you provide a default value of Null for your optional parameter, the IsMissing() check will always return False:

Sub DemoExplicit(Optional Foo As Variant = Null)
    Debug.Print IsMissing(Foo)  'Always returns False
End Sub

Sub DemoImplicit(Optional Foo = Null)
    Debug.Print IsMissing(Foo)  'Always returns False
End Sub

Sample Usage

I had some difficulty conjuring a sample usage for the IsMissing() function.

That's for a couple of reasons:

• I avoid using Variants in general, preferring stronger types like Long/String/Date as these provide better type safety
• I generally prefer to use explicit defaults with Optional parameters, such as: Optional Foo As Variant = Null

That said, I think IsMissing() makes sense to use in something like the following GetOrdinalDay() function.  That's especially true since I don't like using completely arbitrary values as defaults, and I consider the default date value of 0 (which translates to December 30, 1899) as one such arbitrary number.

Function GetOrdinalDay(Optional AsOfDate As Variant) As Integer
    Dim AsOf As Date
    
    If IsMissing(AsOfDate) Then
        AsOf = Date
    Else
        AsOf = CDate(AsOfDate)
    End If
    
    GetOrdinalDay = DateDiff("d", DateSerial(Year(AsOf), 1, 1), AsOf) + 1
End Function

Sub TestOrdinalDay()
    Dim CurrentDate As Date
    Dim OrdinalDay As Integer
    
    CurrentDate = Date
    
    ' Calculate ordinal day for the current date
    OrdinalDay = GetOrdinalDay()
    Debug.Print "Ordinal day for " & Format(CurrentDate, "yyyy-mm-dd") & ": " & OrdinalDay
    
    ' Calculate ordinal day for a specific date
    OrdinalDay = GetOrdinalDay("2023-02-01")
    Debug.Print "Ordinal day for 2023-02-01: " & OrdinalDay
End Sub

Cover image created with Microsoft Designer

UPDATE [2023-09-06]: Added introductory paragraph with a link to An Article About Nothing.