How to Check if an Array Is Empty in VBA
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:
- Online games, like chess, make use of two dimensional arrays.
- 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.
Contents
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) &amp;amp;gt; 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)) &amp;amp;gt; 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(&amp;amp;lt;arrname&amp;amp;gt;) 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(&amp;amp;lt;arrname&amp;amp;gt;) 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.