When dealing with arrays in VBA, you must realize that there are two different types, and that is static and dynamic.
Static arrays are of a fixed size, we declare them only once. Dynamic arrays can be changed, or re-dimensioned. Now, until they do not have assigned values, dynamic arrays are empty. To check if our array is empty or not, we need to create a separate function that will check if the array is empty or not.
We will show how to do it in the example below.
Creating Function to Check if an Array is Empty in VBA
The first thing that we need to do if the array is empty is to create a function that will do this job for us. For this, we need to open our VBA module. While in the Excel file, click on ALT + F11, and then right-click on the left window that appears, go to Insert >> Module:
Once there, this is the function that we will have to input:
Function Array_Empty(var As Variant) 'Declaring the variable Dim k As Integer On Error Resume Next 'setting the variable k = UBound(var, 1) 'If formula If Err.Number = 0 Then Array_Empty = False Else Array_Empty = True End If End Function
And this is what it looks like in the module:
This function, called Array_Empty with one parameter (var as a variant) will check every value in our array and give us the results: 1) False if any value if all of the values in our array are populated and 2) True if our array is empty.
Check If an Array is Empty
Once we have our function in place, we are ready for the code. This is what our code will be:
Sub CheckingIfArrayIsEmpty() 'Declaring the array variable Dim a() As Variant 'The array will be empty as it has no assigned values Range("b2").Value = Array_Empty(a) 'Defining length of array ReDim a(6) Range("b3").Value = Array_Empty(a) 'We check if the array is empty before assigning values 'Assigning values For i = 0 To 6 'We will allocate values with For Next Loop a(i) = Cells(i + 2, 1).Value Next 'check if the array is empty at this point Range("b4").Value = Array_Empty(a) End Sub
For the first thing in our code, we will declare the variable:
Dim a() As Variant
In this stage, our array will be empty, as there are no values assigned to it, which is the exact thing we wrote in the comment section of our code as well. We will make the table in our worksheet, to check the values of our array at any given moment:
The next line of our code is very important:
Range("b2").Value = Array_Empty(a)
In this part, we are determining the value of cell B2 to be the current value of our array based on the result of our function Array_Empty. We can only have two values, as we said- TRUE (if the array is empty) and FALSE (if it is not).
In our case, for the first check of the array, we should get the result TRUE, as our array is empty.
When we step through our code by pressing F8 on our keyboard (we do so until we reach the line of the code ReDim a(6)) we will get the following results in the worksheet:
In the next part of the code:
ReDim a(6) Range("b3").Value = Array_Empty(a)
We use ReDim to re-dimension our array and to define its length. Our array will have six values. Now, at this point, we are not sure if it is empty or not, as we still did not assign any values to it. We will step through our code one more time, and this is what we will end up with:
This means that our array will not be empty, i.e. it will not be considered as such when re-dimensioned, even when it does not have certain values assigned to it.
For the final part of our code, we will assign some values to our array with For Next Loop and then check if our array is empty or not (we will see the results in cell B4).
For i = 0 To 6 a(i) = Cells(i + 2, 1).Value Next Range("b4").Value = Array_Empty(a)
When we step through this part of the code, we get this picture:
Meaning, of course, that our array is not empty when it has values assigned to it.
For every part of the code, we are calling our function to check if our array is empty or not. Use the function described in the first part and alternate your code as you please, to achieve the same results.