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.
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.
- 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.
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.
Secondly, go to Insert>Module.
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
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.
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
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.
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
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.
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.
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
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.
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:
- How to Use VBA LTrim Function in Excel (4 Examples)
- Use Log Function in Excel VBA (5 Suitable Examples)
- How to Use VBA Abs Function in Excel (9 Examples)
- Use VBA Randomize Function in Excel (5 Examples)
- How to Use VBA FileDateTime Function in Excel (3 Uses)
4. Checking a Cell Range on Worksheet by VBA IsEmpty Function
This is our dataset where the B5:B14 cell range shows the Expression.
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
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.
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.
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
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.
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.
