How to use the ISEMPTY Function (VBA)

Description

The Microsoft Excel ISEMPTY function can be used to check for blank cells or uninitialized variables.

Syntax

The syntax for the ISEMPTY function in Microsoft Excel is:

IsEmpty( value )

Parameters or Arguments

value

The value that you want to test. If value is a blank cell or uninitialized variable, this function will return TRUE. Otherwise, the function will return FALSE.

Note

• See also the ISBLANK function (worksheet function).

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• VBA function (VBA)

Example (as VBA Function)

The ISEMPTY function can only be used in VBA code in Microsoft Excel. We can use the ISEMPTY function to test a worksheet cell or a variable. Let's look at both of these cases.

With a Worksheet Cell

If you wish to test whether a worksheet cell is empty in VBA, you can not use the worksheet function called ISBLANK. In VBA, you must use the ISEMPTY function.

Here is an example of how to test whether a worksheet cell is empty using the ISEMPTY function:

Sub TestCellA1()

   'Test if the value is cell A1 is blank/empty
   If IsEmpty(Range("A1").Value) = True Then
      MsgBox "Cell A1 is empty"
   End If

End Sub

In this example, we will test whether cell A1 is empty. If cell A1 is empty, the message "Cell A1 is empty" will be displayed.

With a Variable

The ISEMPTY function can also be used to test whether a variable has been initialized. If the variable has not been initialized, the ISEMPTY function will return true. Otherwise, the function it will return false.

Variable is Uninitialized

Let's first look at an example of when a variable has not been initialized:

Sub TestVariable()

   Dim LResult

   'Test if the variable has been initialized
   If IsEmpty(LResult) = True Then
      MsgBox "Variable has not been initialized."
   End If

End Sub

In this example, the variable called LResult has been declared, but has not been initialized with a value. As a result, the ISEMPTY function will return true and display the message "Variable has not been initialized."

Variable is Initialized

Now, we will modify the example above and initialize the LResult variable before calling the ISEMPTY function.

Sub TestVariable()

   Dim LResult

   'Initialize the variable called LResult
   LResult = "TechOnTheNet.com is a great resource!"

   'Test if the variable has been initialized
   If IsEmpty(LResult) = True Then
      MsgBox "Variable has not been initialized."
   End If

End Sub

Since the LResult variable has now been initialized to the value "TechOnTheNet.com is a great resource!", the ISEMPTY function will return false and the message box will not be displayed.