Handle Error 9 when there is an Empty Array

Question

I am writing a script that will loop through an Excel spreadsheet and find if there are duplicates of selected cells. If there are duplicates then the function will return an array of which rows are duplicates and create a comment to tell me the rows.

I have been able to handle error 0 but now I am getting error 9 when I check if there are elements in the array using the UBound function.

How do I validate if the array of integers is empty?

Function IsArrayEmpty(anArray As Variant) As Boolean
    Dim i As Integer

    On Error Resume Next
        i = UBound(anArray, 1)
    Select Case (Err.Number)
        Case 0
            IsArrayEmpty = True
        Case 9
            IsArrayEmpty = True
        Case Else
            IsArrayEmpty = False
    End Select
End Function

Answer 1

Try this to check an empty array:

Dim arr() As String

If (Not arr) = -1 Then
   Debug.Print "empty"
Else
   Debug.Print "UBound is " & UBound(X)
End If  

HTH!

Answer 2

Your function is failing because if there is no error raised by UBound() (i.e. the array is dimensioned) then Err.Number is 0 and:

Case 0
  IsArrayEmpty = True

is executed returning an incorrect result.

The simplest way is to just trap the error:

Function IsArrayEmpty(anArray As Variant) As Boolean
On Error GoTo IS_EMPTY
If (UBound(anArray) >= 0) Then Exit Function
IS_EMPTY:
    IsArrayEmpty = True
End Function