Check for empty array indexes in VBA (that can include objects)

Question

I need to do a is nothing check on a Variant array to exclude empty indexes from being used. I use is nothing to capture empty indexes which hold (empty) objects, it works fine but for indexes that hold regular data types (not objects), it throws an exception.

    Dim arrArray() as Variant
    '... fill array with values but leave some indexes out

    'Loop through the array
    For i = LBound(arrArray) To UBound(arrArray)

        'Check if the current array item is an empty object
        If arrArray(i) Is Nothing Then
            'don't debug.print
        
        'Debug if it's not an empty object 
            Else
            Debug.Print arrArray(i)
            End If

        Next

I could use on error resume next but since error handling is done dynamically it would change the error handling status so I would like to avoid that. If it can't be avoided please check my other question.

Note: Currently I just work with empty objects, at some point in the future I might get an actual object. So in the long run I will have to check if the index contains an existing object (otherwise - I presume - debug.print will throw an error).

Answer 1

Please, try the next function. It will return a cleaned array (without empty elements) for a wide range of elements type:

Function elimEmptyArrayElements(arrX As Variant) As Variant
 Dim i As Long, arrNoEmpty, k As Long
 ReDim arrNoEmpty(UBound(arrX)): k = 0
 For i = LBound(arrX) To UBound(arrX)
    If Not IsMissing(arrX(i)) Then
       If Not IsObject(arrX(i)) Then
            If TypeName(arrX(i)) = "String" Then
                If arrX(i) <> "" Then
                    arrNoEmpty(k) = arrX(i): k = k + 1
                End If
            Else
                If Not IsEmpty(arrX(i)) Then
                    arrNoEmpty(k) = arrX(i): k = k + 1
                End If
            End If
       Else
            Set arrNoEmpty(k) = arrX(i): k = k + 1
       End If
    End If
 Next i
 ReDim Preserve arrNoEmpty(k - 1)
 elimEmptyArrayElements = arrNoEmpty
End Function

Please, test it using the next Sub. It will stop on each pair of initial/cleaned array representation. When possible, both arrays are joined in Immediate Window.

If not possible, only the number of their elements (Ubound(arr)) is returned. You may iterate between each array elements and see that no empty one exists:

Sub testElimEmptyArrayElements()
   Dim arr
   arr = Split("1,7,9,,10,5,6,,2,8,3,4", ",")
   Debug.Print Join(arr, "|") 'just to visually see the initial array content
   
   arr = elimEmptyArrayElements(arr)
   Debug.Print Join(arr, "|"): Stop 'the cleaned array
   
   arr = Application.Transpose(Range("A2:A20").value) 'a 1D array extracted from a column range
   Debug.Print Join(arr, "|")

    arr = elimEmptyArrayElements(arr)
    Debug.Print Join(arr, "|"): Stop 'the cleaned array

    arr = Array(1, 2, 3, , 4, , 5): Debug.Print "Initial number of numeric elements: " & UBound(arr)
    arr = elimEmptyArrayElements(arr): Debug.Print "Cleaned array number of numeric elements: " & UBound(arr): Stop

    arr = Array(Range("A2"), Range("A3"), , Range("A6")): Debug.Print "Initial number of Range Object elements: " & UBound(arr)
    arr = elimEmptyArrayElements(arr): Debug.Print "Cleaned array number of Range elements: " & UBound(arr): Stop
    
    arr = Array(ActiveSheet, , ActiveSheet.Next): Debug.Print "Initial number of Sheet Object elements: " & UBound(arr)
    arr = elimEmptyArrayElements(arr): Debug.Print "Cleaned array number of Sheet Object elements: " & UBound(arr): Stop
    
    arr = Array("my string", 100, Range("A2"), , ActiveSheet, , ThisWorkbook, "test", 6): Debug.Print "Initial number of variate elements: " & UBound(arr)
    arr = elimEmptyArrayElements(arr): Debug.Print "Cleaned array number of variate types elements: " & UBound(arr)
      Debug.Print arr(2).value        'the cell value
      Debug.Print arr(3).name         'the activesheet name
      Debug.Print arr(4).Sheets.count 'activeworkbook number of sheets
End Sub