VBA IsEmpty Function

What is the VBA IsEmpty function?

The IsEmpty function is used to check if a cell in an Excel sheet is empty.
This function is also used to check if a variable is initialized.
It returns True if the variable is uninitialized.
It also returns True if a variable is explicitly kept empty.
If a variable is assigned a value, IsEmpty returns False.

An example of using IsEmpty with a sheet cell

Let us first start our examples of IsEmpty function with an Excel sheet.

We will use a range and then check if the B3 cell is empty or not.

The status is displayed in the message box.

VBA Code:

Sub IsEmpty_ex()

   If IsEmpty(Range("C3").Value) = True Then

      MsgBox "Cell C3 is empty"

   Else

      MsgBox Range("C3").Value

   End If

End Sub

Result:

You can see, the result is True as the cell is empty.

Iterating through a range and filling empty cells with background color

For this example, we will create a range of B2 to D5 cells and check if any of the cells are empty.

For the empty cells found, we will fill the interior color of the cell by Font.InteriorColor property:

Code:

Sub IsEmpty_ex()

Dim ChkRange As Range
Dim cell

'Range to be checked for empty cells

Set ChkRange = Range("B2:D5")

For Each cell In ChkRange

    'Using IsEmpty in if statement and assign the Interior color if cell  is empty

    If IsEmpty(cell) = True Then

       cell.Interior.ColorIndex = 3

    End If

Next cell

End Sub

Result:

You may compare the empty cells filled in this graphic with the first example’s graphic.

Write a value to empty cells example

You may also write some value to the empty cells rather than filling it with color.

In the example below, we will write “Absent” to all cells that are empty in our range:

Code:

Sub IsEmpty_ex()
Dim ChkRange As Range
Dim cell

'Range to be checked for empty cells
Set ChkRange = Range("B2:D5")

For Each cell In ChkRange

    'Write Absent to all empty cells

    If IsEmpty(cell) = True Then

       cell.Value = "Absent"

    End If

Next cell

End Sub

Output:

Using IsEmpty with a variable example

Let us use the IsEmpty function with variables in VBA now.
We are using two variables, out of which one is assigned a value (var2 and one is not initialized (var1).
In the IsEmpty function, we will pass these variables and see the returned results:

Sub IsEmpty_ex()
Dim var1
Dim var2

MsgBox "IsEmpty returned for var1 = " & IsEmpty(var1)

var2 = "Not empty"

MsgBox "IsEmpty returned for var2 = " & IsEmpty(var2)

End Sub