I use a lot of arrays in my procedures and l use this procedure from Chip Pearson to check if the array is empty. IMO it is more efficient to check that before trying to do something with the array.
Author: Chip Pearson
Public Function IsArrayEmpty(arr As Variant) As Boolean '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' IsArrayEmpty ' This function tests whether the array is empty (unallocated). Returns TRUE or FALSE. ' ' The VBA IsArray function indicates whether a variable is an array, but it does not ' distinguish between allocated and unallocated arrays. It will return TRUE for both ' allocated and unallocated arrays. This function tests whether the array has actually ' been allocated. ' ' This function is really the reverse of IsArrayAllocated. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim LB As Long Dim UB As Long Err.Clear On Error Resume Next If IsArray(arr) = False Then ' we weren't passed an array, return True IsArrayEmpty = True End If ' Attempt to get the UBound of the array. If the array is ' unallocated, an error will occur. UB = UBound(arr, 1) If (Err.Number <> 0) Then IsArrayEmpty = True Else '''''''''''''''''''''''''''''''''''''''''' ' On rare occassion, under circumstances I ' cannot reliably replictate, Err.Number ' will be 0 for an unallocated, empty array. ' On these occassions, LBound is 0 and ' UBoung is -1. ' To accomodate the weird behavior, test to ' see if LB > UB. If so, the array is not ' allocated. '''''''''''''''''''''''''''''''''''''''''' Err.Clear LB = LBound(arr) If LB > UB Then IsArrayEmpty = True Else IsArrayEmpty = False End If End If End Function