How to Check if an Array Is Empty in Excel VBA (3 Ways)

Arrays are a common way to store multiple values in Excel VBA, but sometimes you need to find out if an array has any data or if it’s empty. This check is important to avoid errors and make your macros work better.

In this article, we’ll cover easy ways to check if an array is empty using methods like IsEmpty, LBound and UBound, and checking if the array is initialized. You’ll get simple examples to understand how to use each method.

Function IsArrayEmpty(arr As Variant) As Boolean
    On Error Resume Next
    IsArrayEmpty = (UBound(arr, 1) < LBound(arr, 1)) Or (UBound(arr, 2) < LBound(arr, 2))
    If Err.Number <> 0 Then
        IsArrayEmpty = True
        Err.Clear
    End If
    On Error GoTo 0
End Function
Sub TestIsArrayEmpty()
    ' Assumes studentArray is loaded elsewhere as a 2D array from worksheet
    If IsArrayEmpty(studentArray) Then
        MsgBox "Array is empty."
    Else
        MsgBox "Array has " & UBound(studentArray, 1) - LBound(studentArray, 1) + 1 & " elements (rows)."
    End If
End Sub

Sometimes you load data from a worksheet into an array, but you want to first check if the array is empty before working with it. The IsEmpty function in VBA can test whether the array variable has been initialized at all, which helps avoid errors when using an unassigned array..

For this demonstration, we’ll use a worksheet named Students where cells A2:A11 contain student names.

First, one macro will load the student names from the sheet into a public array. Then, a separate macro will use IsEmpty to check if the array is empty or has data.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, then choose Insert >> Module.

➤ Paste the following code to load the student names into a public array:

Public studentArray As Variant
Sub LoadStudentNames()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Students")
    ' Load values from A2:A11 into the array
    studentArray = ws.Range("A2:A11").Value
    MsgBox "Student names loaded into array."
End Sub

➤ Next, insert another module (Insert >> Module) and paste this code to check if the array is empty:

Sub CheckArray_IsEmpty()
    If IsEmpty(studentArray) Then
        MsgBox "The array is empty."
    Else
        MsgBox "The array contains data."
    End If
End Sub

➤ Return to Excel and press  Alt  +  F8  to run the macro LoadStudentNames first to load data into the array.
➤ Then run the macro CheckArray_IsEmpty to see the result.

If the array has data, a message box will say “The array contains data.” Otherwise, it will say “The array is empty.”

Sometimes you need to check if a string or a variant array contains any data. Instead of looping through each element, you can quickly join all elements into one long string and see if its length is zero. If it is, the array is either empty or all elements are empty strings.

For this demonstration, assume the student names are loaded into an array in another module.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:

Sub CheckArray_UsingJoin()
    Dim flatArray() As String
    Dim i As Long
    Dim totalRows As Long
    On Error GoTo EmptyArray
    ' Ensure studentArray is not empty
    If IsEmpty(studentArray) Then GoTo EmptyArray
    ' Get the number of rows in the 2D array
    totalRows = UBound(studentArray, 1)
    ReDim flatArray(1 To totalRows)
    ' Flatten studentArray into a 1D string array
    For i = 1 To totalRows
        flatArray(i) = CStr(studentArray(i, 1))
    Next i
    ' Use Join to check for content
    If Len(Join(flatArray, "")) = 0 Then
        MsgBox "Array is empty or contains only empty strings."
    Else
        MsgBox "Array has data."
    End If
    Exit Sub
EmptyArray:
    MsgBox "Array is uninitialized or empty."
    Err.Clear
End Sub

➤ Return to Excel and press  F5  to run the macro.

➤ Check the message box to see if the array has data or is empty.

When working with arrays in VBA, creating a reusable function to check if an array is empty can simplify your code. This method uses UBound and LBound inside an error-handling function to return True if the array is empty or uninitialized, and False otherwise.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code into the new module:

Function IsArrayEmpty(arr As Variant) As Boolean
    On Error Resume Next
    IsArrayEmpty = (UBound(arr, 1) < LBound(arr, 1)) Or (UBound(arr, 2) < LBound(arr, 2))
    If Err.Number <> 0 Then
        IsArrayEmpty = True
        Err.Clear
    End If
    On Error GoTo 0
End Function
Sub TestIsArrayEmpty()
    ' Assumes studentArray is loaded elsewhere as a 2D array from worksheet
    If IsArrayEmpty(studentArray) Then
        MsgBox "Array is empty."
    Else
        MsgBox "Array has " & UBound(studentArray, 1) - LBound(studentArray, 1) + 1 & " elements (rows)."
    End If
End Sub

➤ Return to Excel and press  F5  to run TestIsArrayEmpty.
➤ The message box will show whether the array is empty or contains elements.

Frequently Asked Questions

How do I check if an array is empty in VBA?

You can check if an array is empty by using functions like IsArray, checking UBound and LBound, or converting the array to a string with Join and testing the result.

What does IsEmpty do with arrays in VBA?

IsEmpty checks if a variable is uninitialized, but it doesn’t work reliably on arrays. Use UBound and LBound or IsArray to test arrays instead.

How can I avoid errors when checking an empty array?

Use error handling or check if the array is initialized with IsArray and test bounds with UBound and LBound before accessing array elements to avoid runtime errors.

Why does UBound give an error on an empty array?

UBound raises an error if the array is uninitialized or empty. Always check if the array exists with IsArray or use error handling before calling UBound.

Wrapping Up

Knowing how to check if an array is empty in VBA helps you write better, error-free code. Whether you use IsArray with Not Not, combine UBound and LBound, or apply the Join function, these methods let you safely confirm if an array contains data. Choosing the right approach depends on your specific needs. Using these techniques will make your Excel VBA projects more reliable, prevent runtime errors, and ensure your macros handle arrays smoothly every time.