How to Use VBA IsEmpty Function (5 Relevant Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you need to check whether a cell remains empty/blank or not. In such circumstances, you may utilize the VBA IsEmpty function to identify the emptiness. In this article, I’ll demonstrate 5 examples of using the VBA IsEmpty along with the basics of the function.

First of all, let’s have a look at the following screenshot.

VBA IsEmpty Function Overview

The above figure shows the overview of the utilization of the VBA IsEmpty function. Before going into the main section, you may grasp the basics of the function.


VBA IsEmpty Function Overview

IsEmpty, a built-in function introduced in Excel 2000 and also available in Excel 365, checks if the cell is empty (not initialized) or the cell is not empty (initialized). As it is a VBA function, obviously you may use the function in macro code to find the expected output. Once you enter the formula in VBA, you’ll see the following screenshot.

Formula

  • Function Objective:

Returns a boolean value representing if the cell is empty or not.

  • Syntax:

IsEmpty(expression

  • Arguments Explanation:
Argument Required/Optional Explanation
expression Required Refers to a variant which might be a numeric or string
  • Return Value:
Return Value Explanation
TRUE If the cell is empty
FALSE If the cell is not empty

Now, we’ll see the efficient examples of the IsEmpty function for executing this, we have a dataset as shown in the following picture where Item List (Expression) is available. And we need to check whether the cell is empty or not.

Dataset


1. Applying VBA IsEmpty Function to Return a Boolean Value (True or False)

As we want to use VBA, we must know how to insert a VBA code.

Truly speaking, it’s a simple process.

Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

Secondly, go to Insert>Module.

How to Insert VBA Code

If you want to find the boolean value and show the output using the MsgBox tool for a cell e.g. B6, you may copy the following code into the created module.

Sub Return_Boolean()
Dim A As String
A = IsEmpty(Range("B6").Value)
MsgBox A
End Sub

VBA IsEmpty to Return a Boolean Value

Here, I’ve declared a String type variable A. Then within the variable storing the result value of IsEmpty, applied to the B6 cell. And lastly, use a message box (MsgBox) to provide the output.

Next, run the code. Also, you may use the keyboard shortcut F5 or Fn+F5 (varies from the keyboard) for running the code.

VBA IsEmpty to Return a Boolean Value

Formerly, I discussed how the IsEmpty function returns TRUE if the cell is blank.

As the B6 cell is blank, the output is TRUE, that is to say, the cell is not initialized.

Read More: How to Return a Value in VBA Function (Both Array and Non-Array Values)


2. Utilizing VBA IsEmpty Function to Investigate for Variable Values

Additionally, we may investigate whether a variable is initialized or not. If the output is TRUE that means the function is not initialized and vice-versa.

In this example, we’ll examine the VBA IsEmpty function for two variables.

For the first variable (variable1), I declare a blank expression (Expression1) as a variant.

Then copy the following code.

Sub Check_Variable1()

   Dim Expression1 As Variant

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

End Sub

Investigate for Variable Values

In the above code, I declared a blank expression as a Variant. Then I used the IsEmpty function to test the variable if it is initialized.

After running the code, Excel shows Variable has been declared but not initialized. Because I declared Expression1 as a variant but I didn’t assign any value.

Investigate for Variable Values

In the case of the second variable (variable2), I both declare Expression1 as a variant and I assign a string i.e. Miles to Go Before I Sleep.

Then copy the code below.

Sub Check_Variable2()

   Dim Expression1 As Variant
   
   Expression1 = "Miles to Go Before I Sleep"

   MsgBox IsEmpty(Expression1)
   
End Sub

Investigate for Variable Values

Here, I provided a string value for Expression1 which is also declared as a Variant.

And run the code. The output is FALSE as Expression1 has a string value.

Investigate for Variable Values

Read More: How to Use VBA Val Function in Excel (7 Examples)


3. Applying the VBA IsEmpty Function to Check a Cell on the Worksheet

Assuming that you have a worksheet, you need to find out if a single cell is empty or not. More importantly, you don’t want to find the boolean value e.g. TRUE or FALSE rather you want to show the output as The cell is empty or The cell is not empty.

However, we’ll find out such output for the B6 cell.

VBA IsEmpty to Check a Cell on Worksheet

Now, you may utilize the following code.

Sub Check_Cell()
If IsEmpty(Sheet3.Range("B6").Value) = True Then
 
    Sheet3.Range("C6").Value = "The cell B6 is empty"
 
Else
 
    Sheet3.Range("C6").Value = "The value in B6 is " & Sheet3.Range("B6").Value
 
End If
End Sub

VBA IsEmpty to Check a Cell on Worksheet

Here, Sheet3 is the source of the cells B6 & C6 Then the IsEmpty function is used to check cell B6, if it is TRUE then it will show “The Cell B6 is empty”; otherwise it will display the cell value.

When you run the code, you’ll get the following output.

VBA IsEmpty to Check a Cell on Worksheet

One more interesting thing, you also can find and display the cell value using the above code.

Related Content: How to Use IsNull Function in Excel VBA (5 Examples)


Similar Readings:


4. Checking a Cell Range on Worksheet by VBA IsEmpty Function

This is our dataset where the B5:B14 cell range shows the Expression.

Check a Cell Range on Worksheet

Now, if you need to check a cell range whether each cell is empty or not, you may use the following code.

Sub Check_CellRange()

    Dim k As Integer

    For k = 5 To 14
        Cells(k, 3).Value = IsEmpty(Cells(k, 2).Value)
    Next k

End Sub

Check a Cell Range on Worksheet

In the above code, I declared k as an Integer which is mainly the row number and it is 5 to 14 as the cell range is B5:B14. Then the output of IsEmpty for the cells (k,2) is applied to cells (k,3).

Immediately, you may run the code and the output will be as follows.

Check a Cell Range on Worksheet

Here, the output TRUE represents that the concerned cell is not empty and FALSE shows that the cell is not empty.

Related Content: How to Use Fix Function in Excel VBA (4 Examples)


5. Combining VBA IsEmpty Function with IF Logical Function

Furthermore, we can make the use of IsEmpty more purposeful by adding any other logical functions with the current function.

For example, we can utilize the combination of VBA IsEmpty and IF functions for the following problem-solving case.

In the following dataset, we have data for some expressions (Item List) like if the item is delivered or not delivered. But we don’t have any data in the case of some items.

Nevertheless, we need to summarise the whole data into a different cell i.e. Checking Delivery Information.

Under this individual cell, we want to get the output as Checked if any data is available or Not checked if there is no data in column C.

VBA IsEmpty with IF Logical Function

Now copy the following code.

Sub VBA_IsEmpty_IF()

  If IsEmpty(Range("C5").Value) Then
    Range("D5").Value = "Not Checked"
  Else
    Range("D5").Value = "Checked"
  End If

  If IsEmpty(Range("C6").Value) Then
    Range("D6").Value = "Not Checked"
  Else
    Range("D6").Value = "Checked"
  End If
 
  If IsEmpty(Range("C7").Value) Then
    Range("D7").Value = "Not Checked"
  Else
    Range("D7").Value = "Checked"
  End If
  
  If IsEmpty(Range("C8").Value) Then
    Range("D8").Value = "Not Checked"
  Else
    Range("D8").Value = "Checked"
  End If
  
End Sub

VBA IsEmpty with IF Logical Function

Here, we have four segments. Each segment is similar except for the cell name.

Firstly, in the case of the D5 cell, the Not Checked will be the output if the IsEmpty function returns TRUE for the C5. Otherwise the Checked will be the output if the function finds FALSE for the C5 cell. Later, a similar process is applied to the rest D6, D7, D8 & D9 cells.

After running the code, the output will be as follows.

VBA IsEmpty with IF Logical Function

In addition, if you want to explore 5 outstanding methods to check if the cell is empty, you may visit the How to Check If the Cell is Empty in the VBA  article.

Read More: How to Use VBA IsError Function (6 Examples)


Things to Remember

1. If you declare the variant as Null, the VBA IsEmpty function will return FALSE.

2. ISBLANK, one kind of IS Function in Excel, checks if a cell is empty or not. However, the ISBLANK and VBA IsEmpty return almost similar results except the IsEmpty function is not case-sensitive in the case of the string value.


Download Practice Workbook


Conclusion

In the above article, I tried to cover the necessary examples of the VBA IsEmpty function along with the ins and outs of the function. I strongly believe that this article will articulate your Excel journey. However, if you have any queries or suggestions, please let me know in the comments section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo