Md. Abdul Kader
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.
Download Practice Workbook
Introduction to the VBA IsEmpty Function
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 |
Examples of Using the VBA IsEmpty Function in Excel
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. VBA IsEmpty 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 used 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 keyboards) 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. VBA IsEmpty 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 else 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 the Expression1 as 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. VBA IsEmpty to Check a Cell on Worksheet
Assuming that you have a worksheet, you need to find 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”; else 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. VBA IsEmpty to Check a Cell Range on Worksheet
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 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. VBA IsEmpty 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 mainly 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. Else the Checked will the output if the function finds FALSE for the C5 cell. Later, a similar process is applied for 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 Functions 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.
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.
