How to use IsMissing function in VBA?

In VBA (Visual Basic for Applications), the IsMissing function is used to determine whether an optional variant argument has been provided to a procedure. This is particularly useful for functions and procedures that have parameters declared with the Optional keyword and are of the Variant type.

Here’s how to use the IsMissing function in VBA:

Basic Usage


Sub TestOptionalParameter(Optional ByVal myOptionalParam As Variant)

    ' Check if the optional parameter was provided
    If IsMissing(myOptionalParam) Then
        MsgBox "The optional parameter was not provided."
        MsgBox "The optional parameter was provided with the value: " & myOptionalParam
    End If

End Sub

In this example, TestOptionalParameter is a Sub procedure that takes an optional parameter myOptionalParam. It uses the IsMissing function to check whether myOptionalParam has been supplied when the procedure is called.

To call the procedure without supplying the optional argument:


Sub CallTest()
    ' Call the TestOptionalParameter procedure without the optional parameter
End Sub

This would result in a message box indicating that the optional parameter was not provided.

To call the procedure with the optional argument:


Sub CallTestWithValue()
    ' Call the TestOptionalParameter procedure with the optional parameter
    TestOptionalParameter "Hello, VBA!"
End Sub

This would result in a message box displaying the message: “The optional parameter was provided with the value: Hello, VBA!”.

Remember that the IsMissing function can only be used with optional parameters of the Variant data type. It cannot be used with other data types such as String, Integer, or Object. If you need to check for other optional types, you might consider using default values for the parameters and then checking against those values.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *