When you’re writing multiple subroutines or functions, you often need to send data between them. You do this by adding parameters, or variables, in your subroutine declaration, but what if the sending subroutine doesn’t know what value to pass? In that case, you can make the parameter optional. When you use optional parameters, it’s important for you to check whether the optional parameter has been passed to avoid breaking your code.

One of the easiest ways to check whether an optional parameter has been supplied is to use the built-in VBA IsMissing function. Let’s take a look at this function now.

Note: All mentions of a subroutine can be interchanged with a function throughout this tutorial.

Declaring Optional Parameters

Before we demonstrate how to check whether optional parameters are missing, let’s first remind you how to declare your own optional parameters.

For a subroutine to receive a parameter from another subroutine, it simply needs to say so in its declaration line:

Optional parameters are shown in square brackets

In this code sample, the user can decide whether or not to pass the parameter first_variable, and the Intellisense popup shows the variable in square brackets []. VBA uses square brackets to indicate that a parameter is optional. If we were to remove the Optional tag, receive_optional would always require the argument be passed. In this case, you would receive an error if you didn’t supply a variable when calling receive_optional.

In some cases, passing a variable may not be desirable or even possible. However, it is redundant to rewrite the entire code block for a case where the variable cannot be passed, and therefore we employ the use of the Optional tag to cover both situations.

The Default Parameter Type

If you do not declare a data type when adding optional variables, the optional variable will automatically default to the Variant type .

Sub main_send_optional()
Call receive_optional
End Sub

Sub receive_optional(Optional first_variable)
Debug.Print first_variable
End Sub

This will print Error 448 to the Immediate Window, which means the argument, or variable, was not found. Indeed, looking at the Locals window (View > Locals Window in the VBE), you can see that first_variable is a Variant/Error data type and the value column is listed as Missing:

Missing argument in locals window

Using the VBA IsMissing Function

The VBA IsMissing function acts on an argument and returns TRUE or FALSE, depending on whether the calling subroutine has supplied the parameter. If the parameter is missing, the IsMissing function returns TRUE. Otherwise, it returns FALSE. Here’s a demonstration:

Sub use_ismissing(Optional missed_var)
If IsMissing(missed_var) Then
    MsgBox ("You forgot to pass the variable!")
End If
End Sub

Sub Demo()
Call use_ismissing
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

When you execute the Demo routine, a message box will appear since no parameter is passed to the use_ismissing subroutine as the missed_var variable. For the IsMissing function to properly work, it’s critical that the data type of the parameter is a Variant, though. The VBA IsMissing function will return FALSE for other data types, even if no argument was passed.

This is an important point, so let’s talk about it a bit more.

Only accurate with Variants

If you declare the optional variable as any data type other than Variant, IsMissing will be FALSE, even if no variable is passed into the receiving subroutine. This is because the dimensioning in the declaration line initializes the optional variable into that data type, assigning the data type’s default value. Thus the variable has some value and therefore it is not actually missing.

This demo explains what I mean:

Sub receive_optional2(Optional first_variable As Integer)
Debug.Print first_variable
End Sub

Sub Demo2()
Call receive_optional2
End Sub

When the Demo2 subroutine makes its call statement, the receive_optional2 subroutine will print 0 in the Immediate Window. That’s because the parameter first_variable is initialized as an Integer as soon as the receive_optional subroutine begins, and the default value for an integer is zero. Dimensioning as a String will result in an initial value of "", an empty string. The variable exists for that subroutine, and it is assigned a value. Thus, IsMissing can only detect whether a variable has not been passed if that variable is typed as a Variant.

A lot of people think the VBA IsMissing function isn’t working for them, but the truth is they don’t realize that non-variant data types are initialized with a default value. Even a Range data type in an optional parameter is initialized with a default value of Nothing, which is different than a default value of Missing for Variant data types.

Don’t forget this behavior, because it can cause errors that become very frustrating to find. Data type issues are easy to overlook when debugging, sending you on a 2-hour bug chase that ends up with a facepalm when you finally read the subroutine declaration.

Assigning Default Values

Developers often use IsMissing to avoid Type Mismatch errors and to prevent inaccurate data from invalidating results. In the latter case, if your subroutine needs data and it is missing, the code may still run, but the outcome will be wrong.

Many developers rightfully like to specify data types for their passed arguments to enforce code integrity (for example, integers are always added to integers to avoid strange, automatic data type conversions). If you like to enforce data types, you won’t be able to use IsMissing to check for missing variables because the function only works with Variants.

To circumvent the issue, use default values. These will automatically be assigned to any missing optional variables. You basically just need to set defaults that are so abstract that a user wouldn’t realistically pass those parameters to your subroutines. Consider the follow line of code:

Sub optional_with_defaults(Optional x As Integer = -5, Optional first_sentence as String = "I'm Missing")

This line enforces data type integrity, but it also applies a specific value by default. If you know your data ranges, you can use this knowledge to construct your own IsMissing alternative, like this

Sub optional_with_defaults(Optional x As Integer = -1, Optional first_sentence as String = "aJ89*3$Sfa$%")

If x = -1 Then
    'do stuff for a missing x variable
Else
    'do stuff for a passed x variable
End If

If first_sentence = "aJ89*3$Sfa$%" Then
    'do stuff for a missing first_sentence variable
Else
    'do stuff with passed first_sentence variable
End If

End Sub

As long as your passed x values will always be positive, and as long as the default first_sentence string will not be real data, you can now enforce data type integrity and check whether a parameter was passed.

A Practical Example

Let’s look at a situation where you would actually use the IsMissing function to check whether a variable was passed or not.

Let’s say your user can enter dimensions on a spreadsheet and get the area of a 2D object or volume of a 3D object. For sake of simplicity, let’s also assume these shapes are all rectangles or rectangular prisms.

Your user’s input sheet might look like this:

5 column table with length, width, optional depth, and type and result
A user-input table to determine area or volume of object

You can use the missing cells to determine whether to output area or volume:

Sub get_area_volume_info()

For i = 2 To 6
    length = Cells(i, 1)
    width1 = Cells(i, 2)
    height = Cells(i, 3)
    
    If height = 0 Then
        Cells(i, 4) = "area"
        Cells(i, 5) = area_or_volume(length, width1)
    Else
        Cells(i, 4) = "volume"
        Cells(i, 5) = area_or_volume(length, width1, height)
    End If
Next i

End Sub

Function area_or_volume(l, w, Optional h)

If IsMissing(h) Then
    area_or_volume = l * w
Else
    area_or_volume = l * w * h
End If

End Function

Running the get_area_volume_info subroutine will yield this final table on the spreadsheet:

Filled table with area or volume in the type column
The above table filled with volume or area and the result

If you inspect h in the area_or_volume function, you can see it is passed as a Variant and appears Missing if nothing is passed. That’s why the VBA IsMissing function is an appropriate tool for telling the area_or_volume whether or not the parameter was passed. A more robust function would need to check if the passed parameters are numeric using the VBA IsNumeric function, and you could even convert the subroutine to its own UDF if you wanted to call formulas directly from your spreadsheet.

Conclusion

Using the IsMissing function in your VBA macros is relatively straightforward. The important thing to remember is that it only accurately checks whether a variable was not passed if the parameter is a variant (the default if no data type is specified in the declaration line). If all optional variables are specified with a data type in the declaration line, they will be set to their respective default values at initialization, causing IsMissing to correctly but misleadingly return FALSE.

If you are adamant about enforcing data type integrity, consider writing your own missing variable checks. Make sure to run some quality assurance tests, too, because users are known to add all sorts of strange inputs that could trip up macros that aren’t robust.

That’s all for today’s tutorial. Fill out the form below and we’ll share our best time-saving VBA tips.