How to use IsEmpty function in VBA?

In VBA (Visual Basic for Applications), the `IsEmpty` function is used to determine whether a variable has been initialized or not. It returns a Boolean value (`True` or `False`) indicating whether the variable is uninitialized (or explicitly set to Empty).

Here’s the syntax for the `IsEmpty` function:

IsEmpty(expression)
  • `expression`: This is the variable you want to check. This could be any variable, and in case of object variables, it also checks if they are set to `Nothing`.

Here’s a simple example of how to use the `IsEmpty` function:

Sub CheckIfEmpty()
    Dim MyVar As Variant
    
    ' MyVar is not initialized, so it is Empty
    If IsEmpty(MyVar) Then
        MsgBox "The variable is empty"
    Else
        MsgBox "The variable is not empty"
    End If
    
    ' Now we assign a value to MyVar
    MyVar = 10
    
    ' MyVar is no longer Empty
    If IsEmpty(MyVar) Then
        MsgBox "The variable is empty"
    Else
        MsgBox "The variable is not empty"
    End If
End Sub

In this example, the first `IsEmpty` call would return `True` because `MyVar` has not been initialized with a value. Once the value `10` is assigned to `MyVar`, the `IsEmpty` call would return `False`.

Keep in mind that `IsEmpty` only works with variables that are declared as `Variant` types since other data types are implicitly initialized with default values (e.g., `0` for numeric data types, `””` for strings, `#1/1/0001#` for dates, `False` for Booleans, etc.).

To check if a string is empty, you would use the `Len` function instead, like so:

Sub CheckIfStringEmpty()
    Dim MyString As String
    
    ' MyString is initialized by default to an empty string
    If Len(MyString) = 0 Then
        MsgBox "The string is empty"
    Else
        MsgBox "The string is not empty"
    End If
End Sub

This checks if the length of the string is zero, which indicates that it’s empty or uninitialized.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project