EDUCBA

Home VBA VBA Resources VBA Information Functions VBA IsEmpty
 

VBA IsEmpty

Madhuri Thakur
Article byMadhuri Thakur

Updated June 12, 2023

VBA IsEmpty

 

 

VBA IsEmpty

IsEmpty is a function that is used to check whether the cell being referred to is empty or not. It is very similar to the ISBLANK function in Excel. The IsEmpty function in Excel VBA is also called an information function in Excel, as it gives information on whether the given cell is blank or not.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The isEmpty function is an inbuilt function in Excel VBA. As explained in the above definition, it is used to determine whether the given cell is blank or not. If the given cell is empty, we can display a message to the user that the cell is empty, and if it is not empty, we can display the message that the cell is not empty.

Syntax of IsEmpty in Excel VBA

The syntax for the Isempty function is as follows:

Syntax of VBA IsEmpty

We can use this function to check whether a single cell is empty or the whole data range is empty. This function returns two values. One is true, while another one is false. If the given cell is blank, the function returns the value as true; if the given cell is not blank, it gives the value as false.

How to Use VBA IsEmpty Function in Excel?

We will learn how to use a VBA IsEmpty function with a few examples in Excel.

You can download this VBA IsEmpty Excel Template here – VBA IsEmpty Excel Template

VBA IsEmpty – Example #1

First, let us see how the value returned by this function works.

Follow the below steps to use the VBA IsEmpty function in Excel:

Step 1: Go to the developer’s tab and click visual basic.

VBA IsEmpty Example 1-1

Step 2: Write the following code in the project.

Code:

Sub Check1()

Dim MyCheck As String
MyCheck = IsEmpty(Range("A1").Value)
MsgBox MyCheck

End Sub

VBA IsEmpty Example 1-2

First, let us understand the code written above step by step:

  • Check is the name of the subfunction defined.
  • Mycheck is the variable we have defined as a string because the Isempty function returns a logical value.
  • Mycheck stores the value of Isempty returned when it checks cell A1.
  • The value stored in Mycheck Variable is displayed by the MsgBox function.

Step 3: Run the code by clicking the run button.

Result of Example 1-3

We can see that value returned by the function is true as cell A1 is empty.

VBA IsEmpty – Example #2

Now let us use the Isempty function with the if function to check for a certain cell in a worksheet whether it is blank or not.

Follow the below steps to use the VBA IsEmpty function in Excel:

Step 1: Click on Visual Basic under the code section in the developer’s tab.

VBA IsEmpty Example 2-1

Step 2: Write the following code in the code window,

Code:

Sub Sample1()
If IsEmpty(Range("A1")) = False Then

    MsgBox "Cell A1 is not empty"
Else

    MsgBox "Cell A1 is empty"
End If
End Sub

VBA IsEmpty Example 2-2

Let us again understand the code written above once again.

  • First, we have defined our subfunction as Sample1.
  • We nest the Isempty function with the If function to check whether cell A1 is empty or not.
  • If cell A1 is empty, we use the msgbox function to display the message that the given cell is empty.
  • If cell A1 is not empty, we use the msgbox function to display the message that the given cell is not empty.

Step 3: Run the above code by clicking on the run button.

Result of Example 2-3

We see the result displayed as cell A1 is empty.

Step 4: Now put a random value in cell A; for example, I have put a value A in cell A1.

VBA IsEmpty Example 2-4

Step 5: Now run the code again and get the following result.

Result of Example 2-5

VBA IsEmpty – Example #3

Now let’s use this function to find out whether the given range of cells is blank or not. Earlier, we used this function in a single cell. In this example, our data range will be from B1:D7.

Follow the below steps to use the VBA IsEmpty function in Excel:

Step 1:In the developer’s tab, click on Visual Basic under the code section.

VBA IsEmpty Example 3-1

Step 2: Write the following code in the code window,

Code:

Sub Sample2()
Dim cell As Range
Dim bIsEmpty As Boolean

bIsEmpty = False
For Each cell In Range("B1:D7")
    If IsEmpty(cell) = True Then
        bIsEmpty = True
        Exit For
    End If
Next cell

If bIsEmpty = True Then

    MsgBox "empty cells"
Else

    MsgBox "cells have values!"
End If
End Sub

VBA IsEmpty Example 3-2

Let us understand the above-written code step by step.

  • After defining the subfunction as Sample 2, we have defined a variable named the cell as range, and B is empty as Boolean as Boolean stores logical values.
  • We have predefined that Bisempty will be false if the cell range given is not empty.
  • If the given cell range is empty, Bisempty will store a true value.
  • If the Bisempty variable holds a true value, we display the message “Empty cells.” If the variable holds a false value, we display the message “Cells have values.

Step 3: Run the above code by clicking on the run button.

Result of Example 3-3

We see the following result displayed as cell A1 is empty.

Things to Remember

There are a few things that we need to remember about Isempty Function in Excel VBA:

  • Isempty is similar to the Isblank function in Excel.
  • IsEmpty is an information function.
  • The function “IsEmpty” returns one of two logical values, true or false.
  • You can use the isEmpty function to check if a single cell or a range of cells is empty.

Recommended Articles

This has been a guide to VBA IsEmpty. Here we discussed how to use Excel VBA IsEmpty Function, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. Excel VBA Macro
  3. VBA Subscript out of Range
  4. VBA IsError