> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# The Missing Keyword in VBA
- URL: https://nolongerset.com/the-missing-keyword/
- Published: 2023-08-16T21:32:58.000Z
- Updated: 2026-05-08T12:35:18.000Z
- Description: Wondering what the IsMissing() function is all about in VBA? We'll explore that plus all the ins and outs of the VBA keyword that isn't: Missing.
- Author: Mike Wolfe
- Tags: VBA, Intermediate, #Import 2026-05-20 02:59

*Missing is one of many ways to express the concept of nothingness in VBA. For the others, check out [An Article About Nothing](https://nolongerset.com/an-article-about-nothing/).*

---

According to the [VBA Language Specification](https://learn.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/c86480b2-aef2-4488-b177-f55e13cc51f2), `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](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/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):

```vba
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:

```vba
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:

```vba
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.

```vba
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
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/08/image-18.png)

Calling the `GetOrdinalDay()` function without and with an *AsOfDate*.

*Cover image created with [Microsoft Designer](https://designer.microsoft.com/)*

***UPDATE*** *\[2023-09-06\]: Added introductory paragraph with a link to [An Article About Nothing](https://nolongerset.com/an-article-about-nothing/).*