Introduction to IsEmpty
The IsEmpty VBA function can be used to check if a cell is blank, just like the Excel ISBLANK worksheet function. But, there’s more!
Not only can IsEmpty check if a cell or range is empty, it can check if a variable is uninitialized. In other words, you can use it to see if your macro has assigned a value to a variable.
We’ll get to some examples shortly, but first, let’s start this tutorial with some IsEmpty Basics.
IsEmpty accepts one argument:
It gives you your result as a Logical Boolean. Recall, a Logical Boolean returns either
The “Expression” argument IsEmpty accepts is a Variant, so it can be pretty much anything - a variable, an object, a range and more.
As I mentioned in my IsNumeric VBA Tutorial, it’s a good practice to check if your cells are empty before applying certain VBA functions.
Let’s take a look at some examples to see what I mean.
Check if a Cell is Blank
Sub IsEmptyExample1() If IsEmpty(Range("A2")) = False Then 'Cell A2 is not blank MsgBox "Cell A2 is not empty" Else 'Cell A2 is blank MsgBox "Cell A2 is empty" End If End Sub
Make powerful spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
This is a basic macro that checks whether or not you have entered content in cell
Not recognizing that a required cell is empty is one of the most common causes of VBA runtime errors and undesirable outcomes.
Capturing empty cells gives you control over your macro and lets you handle these exceptions.
Check if All Cells in a Range are Blank
Sub IsEmptyRange() Dim cell As Range Dim bIsEmpty As Boolean bIsEmpty = False For Each cell In Range("A1:B5") If IsEmpty(cell) = True Then 'An empty cell was found. Exit loop bIsEmpty = True Exit For End If Next cell If bIsEmpty = True Then 'There are empty cells in your range '**PLACE CODE HERE** MsgBox "There are empty cells in your range" Else 'There are NO empty cells in your range '**PLACE CODE HERE** MsgBox "All cells have values!" End If End Sub
The comments in this VBA example explain what’s going on. This macro checks all the values in range
Check if a Variable is Initialized
Sub IsEmptyExample2() Dim str1 As Variant MsgBox IsEmpty(str1) 'Returns True str1 = "Hello there!" MsgBox IsEmpty(str1) 'Returns False str1 = Empty MsgBox IsEmpty(str1) 'Returns True End Sub
MsgBox returns True because the variable
Once you assign it the value of
Only when you set the variant back to
Empty will the IsEmpty command once again return True. That’s what we did right before our 3rd MsgBox.
Now it’s time for the shocker. This doesn’t work for all data types! It only works for the Variant in this example because the default value for a variant is
Empty. If we declare the variable as a string, the IsEmpty function will always return False. That’s because the default value for a string is the empty string
An empty string is not the same as an uninitialized variable. An empty string is NOT considered empty, as defined by the IsEmpty function. Try it yourself:
Sub IsEmptyExample3() Dim str1 As String MsgBox IsEmpty(str1) 'Returns False str1 = "Hello there!" MsgBox IsEmpty(str1) 'Returns False str1 = Empty MsgBox IsEmpty(str1) 'Returns False End Sub
You can prove the default value is an empty string by right clicking str1 and clicking “Add Watch.” By stepping into your macro (pressing the F8 button once), you can see see the default value is
Although it can be used to check whether or not variables have been initialized, the most common use of IsEmpty is to check for blank cells. By learning these examples, you can now perform IsEmpty checks for individual cells, and for entire ranges!
Remember, when applied to a cell, the VBA function IsEmpty is the same as the Excel Worksheet Function ISBLANK. If you’re familiar with using =ISBLANK() in your worksheet cells, learning the IsEmpty VBA function will be a breeze.
I know you want to learn more VBA. To see more VBA tips, tricks and techniques, subscribe using the form below.