When a list or collection of items that are the same data type are stored in continuous memory locations, we call it an array. Arrays are useful objects that are widely used in software development to organize data. Some real examples of where you might use them might include:

1. Online games, like chess, make use of two dimensional arrays.
2. An ECG waveform is a realtime example of array usage.

I’ll show you a couple array examples, then we’ll get into how to check if one is empty.

Sub array_demo()

'Declaration of array variable
Dim arr() As String

'Defining length of array
ReDim arr(8)

For i = 0 To 8
    'Allocate data for each array item through a loop
    arr(i) = Cells(i + 2, 1).Value
    
    'Print each item
    Debug.Print arr(i)
Next

End Sub

Here is another example that creates an array as a result of using the Split function.

Sub array_split_demo()

'list of days in a string separated by a single space
days_string = "sunday monday tuesday wednesday thursday friday saturday"

'splitting the string into an array using a delimiter
weekdays = Split(days_string, " ")

'printing them all
For i = LBound(weekdays) To UBound(weekdays)
Debug.Print weekdays(i)
Next

End Sub

The above code prints a list of days while iterating through the array in that was created when the string was split and stored.

Types of arrays

In VBA arrays can either be static or dynamic.

Static arrays

The size of the array is fixed and cannot be changed.

Dynamic arrays

The size of the array is not fixed and can be modified based on your requirements.

Check if an array is empty or contains data

In VBA, unless we first define the size of an array, we cannot use the Lbound() and UBound functions. It will throw an error. So, we need to use the Redim keyword and define a size as soon as we declare an array.

Also there isn’t a specific function that can validate the existence of data in an array. So, we have to make use pf other available functions, along with some logic, to determine if an array is empty or not.

Below are a few methods you can utilize.

Join function

Just like how we used the split function to split a string into an array, a Join function can be used to concatenate all elements of an array into a single string.

Syntax:

Join ( <source array> , [ < Delimiter > ] )

Where

Source array is the name of the array whose elements need to be joined.

Delimiter is the character that is going to be placed between the concatenation of every two elements while joining the array elements. This is an optional parameter and if it’s not provided, a space “ “ is used by default.

If a zero length string “” is provided as a delimiter, the joining happens without inserting any characters during concatenation.

For example:

Sub join_arraydemo()

' Join together the strings "Orange", "Apple" and "Mango".
Dim all_fruits As String
Dim fruits(0 To 2) As String

'assign values for all fruits
fruits(0) = "Orange"
fruits(1) = "Apple"
fruits(2) = "Mango"

'Join using the built-in function
all_fruits = Join(fruits)

' The variable all_fruits is now set to "Orange Apple Mango"
Debug.Print all_fruits
End Sub

So, first all the elements of the array are concatenated using the Join function, then the resulting string’s length can be checked to check if the array is empty or not.

The below piece of code can be added to the above code sample to notify you if the array is empty or not.

If Len(all_fruits) > 0 Then
Debug.Print "fruits array is not empty"
Else
Debug.Print "Fruits array is empty"
End If

In short, we can use the below line to achieve the same results.

If Len(Join(all_fruits)) > 0 Then

Iterate through all the items in the array

Using a For loop, we can iterate through each element in an array to validate whether the array has some data or it is completely empty. Here’s a ready to use function for you.

Function IsEmptyArray(arr)

'a normal flag variable
flag = 0

'loop to check if atleast one element of te array has data
For i = LBound(arr) To UBound(arr)
If IsEmpty(arr(i)) = False Then
    Debug.Print "The array has data"
    flag = 1
    Exit For
End If
Next

'Check if flag value has changed because data is present
If flag = 0 Then
    Debug.Print "The array is empty"
End If

End Function

UBound function + bypass an error

Because an error will be thrown if we use the Ubound or Lbound function on an empty array, we are going to use the “On Error Resume Next” statement and catch the error number to test if the array is empty. Here the array is considered empty if it doesn’t have a size defined. I created a function to help you better understand and use the code.

Sub array_check_demo()

'Declaration of array variable
Dim arr() As Variant

'The array should be empty now
Debug.Print Is_Var_Array_Empty(arr)

'Defining length of array
ReDim arr(8)

'The array has a size . So, as per this logic, it is not empty
Debug.Print Is_Var_Array_Empty(arr)
'We check if the array is empty before assigning values

'Assign values to array elements
For i = 0 To 8
    'Allocate data for each array item through a loop
    arr(i) = Cells(i + 2, 1).Value
Next

'validate if array is empty again
Debug.Print Is_Var_Array_Empty(arr)

End Sub
Function Is_Var_Array_Empty(arr_var As Variant)

Dim p As Integer

On Error Resume Next
    p = UBound(arr_var, 1)
If Err.Number = 0 Then
    Is_Var_Array_Empty = False
Else
    Is_Var_Array_Empty = True
End If

End Function

StrPtr() function for a byte array

A byte array is nothing but a series or characters. A string can directly be assigned to a byte array. Two elements of an array are used to allocate space for a character from a string. The below code should help you understand further.

Sub byte_array_demo()

'declare a byte array and a string
Dim arr1() As Byte
Dim samplestr As String

'initiate the string
samplestr = "Coffee"

'Check if the array is empty. StrPtr(<arrname>) would return 0 if the array is empty.
Debug.Print StrPtr(arr1) = 0

'allocate the string directly to the byte array
arr1() = samplestr

'View what has been stored in the array
For i = LBound(arr1) To UBound(arr1)
Debug.Print arr1(i)
Next

'Check if the array is empty. StrPtr(<arrname>) would return 0 if the array is empty.
Debug.Print StrPtr(arr1) = 0

End Sub

Explaining the output:

Initially the array is empty , so StrPtr(arr1) returns true.

Ascii value of “C” is 67

Ascii null character

Ascii value of “o” is 111

Ascii null character

Ascii value of “f” is 102

Ascii null character

Ascii value of “f” is 102

Ascii null character

Ascii value of “e” is 101

Ascii null character

Ascii value of “e” is 101

Ascii null character

Finally the array is not empty , so StrPtr(arr1) returns false.

Conclusion

In VBA, other than StrPtr() function for byte arrays , there are no direct methods to find if an array is empty or not. However our best friend, Magical VBA, offers flexible functions that can be combined with your own logic to check the size of an array or the existence or non-existence of data in an array of any datatype.

The meaning of “EMPTY” here lies with the user. It could either be an array that does not have a size defined or an array that has a specific size but no data in it.

Of the above methods, I recommend just grabbing a user-defined function that you can easily use to check if an array is empty or not.

If you’re looking to see how to check if a cell is empty, check out our article here.