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
- Using the VBA IsMissing Function
- Assigning Default Values
- Practical Example
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:
In this code sample, the user can decide whether or not to pass the parameter
. VBA uses square brackets to indicate that a parameter is optional. If we were to remove the
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
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.
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.
When you execute the
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
0 in the Immediate Window. That’s because the parameter
Integer as soon as the
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
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:
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
The above table filled with volume or area and the result
If you inspect
Missing if nothing is passed. That’s why the VBA IsMissing function is an appropriate tool for telling 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.