check if array is empty

[kylefoley76]

These if ... then statements are getting the wrong results in my opinion. The first is returning the value 'false' when it should be 'true'. The second returns the right value. The third and fourth return an error.

Sub empty_array()


Dim arr1() As Variant


If IsEmpty(arr1) Then
MsgBox "hey"
End If


ReDim arr1(1)
arr1(1) = "hey"


If IsEmpty(arr1) Then
MsgBox "hey"
End If


If IsError(UBound(arr1)) Then
MsgBox "hey"
End If


If IsError(Application.match("*", (arr1), 0)) Then
MsgBox "hey"
End If


End Sub

 

[kylefoley76]

Wrong order: The first is returning the value 'false' when it should be 'true'. The fourth returns the right value. The second and third return an error.

Sub empty_array()
    
    
    Dim arr1() As Variant
    
    
    If IsEmpty(arr1) Then
    MsgBox "hey"
    End If
     
    
    If IsError(UBound(arr1)) Then
    MsgBox "hey"
    End If
    
    
    If IsError(Application.match("*", (arr1), 0)) Then
    MsgBox "hey"
    End If


    ReDim arr1(1)
    arr1(1) = "hey"
    
    
    If IsEmpty(arr1) Then
    MsgBox "hey"
    End If
    
    
    End Sub

 

[mikerickson]

The second and third are reacting correctly.
They are throwing VBA errors, like Type Mismatch
IsError tests whether the value is an error value like CVErr(xlErrNA)

MsgBox Application.Match("x", Array(1, 2, 3), 0) = CVErr(xlErrNA)

To test for vba errors:

Dim arr1() as Variant, x As Variant

On Error Resume Next
x = UBound(arr1)
if Err Then
    MsgBox err & vbcr & Error
End If
On Error Goto 0

As you've seen IsEmpty doesn't behave as one things.
To test if an array has been initialized, one uses a test like the one above to test if the array has a UBound.

With data arrays (as opposed to arrays of objects), when the array has its bounds set, it is filled with null values.
The problem is that value nulls are valid values. vbNullString is both the null value for strings and a string (of zero length)
0 and False are other value nulls.

 

[JoeMo]

IsEmpty returns true for a variant variable that has not been initialized. Once you declare that variable as an array, I think you need to test each element of the array.

Sub ArrTest()
Dim V As Variant, i As Long, ct As Long
MsgBox IsEmpty(V)  'true
ReDim V(1 To 3)
MsgBox IsEmpty(V)  ' false
For i = 1 To 3
    If V(i) = "" Then ct = ct + 1
Next i
If ct = UBound(V) Then MsgBox "Array V is empty"
End Sub