VBA check if whole row of multidimensional variant is empty without loops

user_4602442 0
Viewed 146

Is there a quick way to check whether a whole row of a variant is empty?

My multi-dimensional array / variant has n-rows and m-columns.

The only way I can think of is to loop through the columns (of a specific row) and use the IsEmpty() function to determine if a cell is empty.

The variant only consists strings.

Do you know a faster way? Maybe something like this pseudo-code: IsEmpty(myarr(1,*))

this pseudocode would mean to check the all columns of the first row if they are empty.

variant vba excel arrays
2 Answers

You could try something like:

Sub Test()

Dim myarr() As Variant, indx As Long

myarr = Range("A8:C20").Value 'Or however you initialize your array.
indx = 1 'Or whichever row you would want to check.

With Application
    Debug.Print Join(.Index(myarr, indx, 0), "") <> ""
End With

End Sub

Not sure if it will be faster than a loop though, since we call a worksheet application.

user_97581940

No, there isn't a faster way especially considering that arrays in VBA are stored column-wise in memory. The values on a single row are not stored adjacent in memory as it's the case with column values - you could easily test this by running a For Each loop on an array.

That being said, you should probably consider having a Function that checks if a specific row is empty so that you can call it repeatedly and maybe also check for null strings if needed. For example a range of formulas returning "" will not be empty but you might want to have the ability to consider them empty.

For example, you could use something like this:

Public Function Is2DArrayRowEmpty(ByRef arr As Variant _
                                , ByVal rowIndex As Long _
                                , Optional ByVal ignoreEmptyStrings As Boolean = False _
) As Boolean
    Const methodName As String = "Is2DArrayRowEmpty"
    '
    If GetArrayDimsCount(arr) <> 2 Then
        Err.Raise 5, methodName, "Array is not two-dimensional"
    ElseIf rowIndex < LBound(arr, 1) Or rowIndex > UBound(arr, 1) Then
        Err.Raise 5, methodName, "Row Index out of bounds"
    End If
    '
    Dim j As Long
    Dim v As Variant
    '
    For j = LBound(arr, 2) To UBound(arr, 2)
        v = arr(rowIndex, j)
        Select Case VBA.VarType(v)
        Case VbVarType.vbEmpty
            'Continue to next element
        Case VbVarType.vbString
            If Not ignoreEmptyStrings Then Exit Function
            If LenB(v) > 0 Then Exit Function
        Case Else
            Exit Function
        End Select
    Next j
    '
    Is2DArrayRowEmpty = True 'If code reached this line then row is Empty
End Function

Public Function GetArrayDimsCount(ByRef arr As Variant) As Long
    If Not IsArray(arr) Then Exit Function
    '
    Const MAX_DIMENSION As Long = 60
    Dim dimension As Long
    Dim tempBound As Long
    '
    'A zero-length array has 1 dimension! Ex. Array() returns (0 to -1)
    On Error GoTo FinalDimension
    For dimension = 1 To MAX_DIMENSION
        tempBound = LBound(arr, dimension)
    Next dimension
Exit Function
FinalDimension:
    GetArrayDimsCount = dimension - 1
End Function

Notice that I haven't checked for IsObject as your values are coming from a range in Excel but you would normally check for that in a general case.

Your pseudocode IsEmpty(myarr(1,*)) could be translated to:

Is2DArrayRowEmpty(myarr, 1, False) 'Empty strings would not be considered Empty

or

Is2DArrayRowEmpty(myarr, 1, True) 'Empty strings would be considered Empty
user_84889130
Related