How to Check if an Array is Empty in Excel VBA

When using Arrays in Excel VBA you sometimes need to test whether the array is empty or not.

 Most of the time you could simply test to see if an array is populated by checking the Lbound of the array.  When testing, if you get an error then the array is empty.  Whereas no error suggests the array is not empty. 

So, to test  the Lbound you could use the following the VBA code.  ThisArray would simply be replaced with your array name.

Dim ThisArray() As Long
Dim Nmbr As Long
On Error Resume Next
Err.Clear
Nmbr = LBound(ThisArray)
If Err.Number = 0 Then
'Do this... (array is NOT empty)
Else
'Do this... (array IS empty)
End If

I hope you find this snippet of VBA code useful.

Leave a Reply

Your email address will not be published. Required fields are marked *