What is VBA IsEmpty Function in Excel?
The VBA IsEmpty function is an inherent tool within Excel’s Visual Basic for Applications (VBA) module. It is specifically designed to determine if a particular cell lacks it. The function produces a Boolean result, which can either be True or False. In case the designated cell is empty, the function outputs True; on the other hand, if the cell contains a value or a formula, it returns False.
Let us look at an example. In this example, we will use the VBA IsEmpty function to check if a specific cell, in this case, “A1,” is empty. In this subroutine named ‘Example1,’ an ‘If’ statement combined with the IsEmpty function evaluates the status of cell A1.
If the function returns ‘True,’ indicating that the cell is empty, a message box appears with the text “Cell is empty.” Alternatively, if the function returns ‘False,’ indicating the cell is not empty, a different message box appears with the text “Cell is not empty.”
Table of contents
- The VBA IsEmpty function determines if a cell or a Variant type of variable is empty or uninitialized.
- When using the VBA IsEmpty Column procedure, it’s crucial to remember that the VBA IsEmpty function checks cells individually, not collectively, as a column. You’d need to apply a loop structure that iterates through each cell to evaluate an entire column.
- For checking an empty string in VBA, using the ‘Len’ function or direct comparison is more effective than the VBA IsEmpty String procedure.
- The function can be combined with If conditions to execute different code blocks based on whether a variable or a cell is empty.
What Does IsEmpty Function Do in VBA?
The principal task of the VBA IsEmpty function is to ascertain whether a particular variable has been initialized. By doing this, it helps to prevent the occurrence of runtime errors that can ensue when an uninitialized variable is utilized. Thus, the function is a significant asset in preventing possible execution errors.
How to use IsEmpty Function in VBA?
The utilization of the IsEmpty function in VBA involves the following steps:
Step 1: In a new Excel workbook, press “ALT + F11” in Excel to open the Visual Basic for Applications (VBA) editor.
Step 2: In the VBA Editor, you create a new module to write your code. It can be done by selecting Insert from the menu and then choosing Module.
Step 3: Within this new module, start writing your code. It begins with Sub followed by the name you want to give to your subroutine.
Step 4: Use the IsEmpty function to test whether a particular cell or variable is empty in the subroutine. If so, the function will return True; otherwise, it will return False.
Step 5: After checking with IsEmpty, you use an If statement along with the IsEmpty function to run particular code if the condition is True (cell or variable is empty) and optionally, some other code if it’s False (cell or variable, is not empty).
Step 6: Once your code is ready, you can run your subroutine. It can be done by pressing F5 within the VBA Editor when selecting your subroutine.
Let us look at a few examples on how to use IsEmpty.
Example #1: Basic Use of the IsEmpty Function
Suppose we have some large data in Excel where some of the cells are empty. It would be tedious to know the full list of empty cells manually.
Therefore, we will write a code listing all empty cells in the specified range, effectively demonstrating the utility of the VBA IsEmpty range function.
Step 1: In the new module, we start the code by declaring a new subroutine named ‘Example4.’
Step 2: In this line, we declare a new variable, ‘cell,’ referring to each cell in the range we’ll examine.
Step 3: Here, we declare a string variable ’emptyCells’ that will hold the addresses of all the empty cells.
Step 4: Next, we start a loop that will iterate over each cell from A1 to K35.
Step 5: We use the “If IsEmpty(cell) Then” condition to check if the current cell is empty using the IsEmpty function. If it is empty, we’ll proceed to the next line.
Step 6: In this line of code, we check if the current cell is empty. If so, we append the address to the ’emptyCells’ string, preceded by a comma and space for separation.
Step 7: Here, we use the “Next cell” command to help direct Excel to move to the next cell in the range, returning to the If statement until all cells in the range have been examined.
Step 8: We use the “If Len(emptyCells) > 0 Then” condition to check if the ‘emptyCells‘ string has any content (i.e., if any empty cells were found). If it does, we proceed to the next line.
Step 9: In this line, we will remove the leading comma and space from the ’emptyCells’ string to clean it up.
Step 10: Finally, we will use the “Msgbox” function to display a message box that lists all the addresses of the empty cells in the range.
Step 11: Save the VBA macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box. Select the “Example4” macro from the list and click “Run.”
Step 12: Once you execute the code, you will see a message box with all the empty cells.
Here is the complete code.
Dim cell As Range
Dim emptyCells As String
For Each cell In Range(“A1:K35”)
If IsEmpty(cell) Then
emptyCells = emptyCells & “, ” & cell.Address
If Len(emptyCells) > 0 Then
emptyCells = Mid(emptyCells, 3) ‘ Remove leading comma and space
MsgBox “Empty cells: ” & emptyCells
Example #2: Combining the IsEmpty Function with an If Condition
This example demonstrates how the IsEmpty function can be combined with an If condition to determine whether a range of cells is empty.
Step 1: In the new modules, we start the code by declaring a new subroutine named ‘Example2’.
Step 2: In the next line, we declare a new variable named ‘cell’. This variable will be used as a placeholder for each cell in the range we want to check.
Step 3: Next, we write a code to create a VBA loop “For Each cell In Range(“A1:A10”).” This line initiates a loop going through each cell from A1 to A10.
Step 4: In this line, we write the “If IsEmpty(cell) Then” code which will check whether the current cell (held by the ‘cell’ variable) is empty. If it is, Excel will proceed to the next line.
Step 5: Here, we create a message box. If the current cell is empty, Excel will display a message box stating the address of the cell and that it is empty.
Step 6: In this line, we use the “Else” condition, which directs Excel to an alternative course of action if the initial condition (the current empty cell) is unmet.
Step 7: Here, we code for another message box. If the current cell contains any data, Excel will display a message box stating the cell’s address and that it is not empty.
Step 8: In this line, we write “Next cell,” which tells Excel to go to the next cell in the range, bringing the loop back to the If statement until all cells in the range have been evaluated.
Step 9: Save the VBA macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box. Select the “Example2” macro from the list and click “Run.”
Step 10: You get the following result once you execute the macro.
Here is the full code:
Dim cell As Range
For Each cell In Range(“A1:A10”)
If IsEmpty(cell) Then
MsgBox “Cell ” & cell.Address & ” is empty”
MsgBox “Cell ” & cell.Address & ” is not empty”
Example #3: An Alternative to the IsEmpty Function
Sometimes, the Len function can be an alternative to the IsEmpty function. This example shows how to use Len excel function to determine if a cell (A1) is empty.
Step 1: In the new module, first, we start by creating a subroutine named ‘Example3.’
Step 2: In the next line we provide an if condition “If Len(Range(“A1”).Value) = 0 Then”. This line will check the length of the value in cell A1. If it’s 0, that means the cell is empty, and Excel will proceed to the next line.
Step 3: Next, we create a message box. If the value length in cell A1 is 0 (meaning the cell is empty), Excel will display a message box with the text “Cell is empty.”
Step 4: Here, we use the “Else” condition, which signals an alternative course of action if the initial condition (the length of the value in cell A1 is 0) is not met.
Step 5: Next, we create another message box. If the value length in cell A1 is anything other than 0 (meaning the cell contains data), Excel will display a message box with the text “Cell is not empty.”
Step 6: Save the macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box. Select the “Example3” macro from the list and click “Run.”
Step 7: Once you execute the macro, you will see
Here is the full code:
If Len(Range(“A1”).Value) = 0 Then
MsgBox “Cell is empty”
MsgBox “Cell is not empty”
Important Things To Note
- The VBA IsEmpty function is strictly limited to variables of the Variant type. It is not functional with other VBA data types like Strings or Integers.
- The VBA IsEmpty function will return False if the examined variable has been initialized with a value or a Null.
- The VBA IsEmpty function will only return True when it encounters uninitialized variables.
- Using VBA IsEmpty Collection isn’t directly applicable as the VBA IsEmpty function doesn’t effectively evaluate the emptiness of a Collection; instead, you should check if the Collection’s Count property equals zero.
Frequently Asked Questions (FAQs)
The VBA IsEmpty function is specifically designed for Variant types. Therefore, it will not function as expected if you try applying it to other data types, such as String or Integer.
While they appear similar, the VBA IsEmpty and IsBlank functions have different purposes.
The VBA IsEmpty function is a VBA function that checks if a variable (of the variant type) has been initialized or not. On the other hand, IsBlank is a worksheet function that checks if a specific cell in Excel is empty or not.
The primary difference between VBA IsEmpty and Is Nothing lies in their function. The IsEmpty function checks if a variable has been initialized, while the Is Nothing function checks if an object reference is set.
This article must be helpful to understand the VBA IsEmpty, with its formula and examples. You can download the template here to use it instantly.
Guide to VBA IsEmpty. Here we learn to use IsEmpty Function in excel VBA along with examples & downloadable excel template. You can learn more from the following articles –