VBA to check if an array is not filled

[Glory]

A class module fills an array if there are contents in a source range. What if there are no contents in the source range?

IsEmpty(array) doesn't work.

If array = 0 doesn't work, neither does "Is Nothing" or "Is Empty" with the lower bound index appended.

Can anybody help me out with this? How do I set set a condition to determine whether or not an array has got contents after initialization?

I could just use some indirect method like... checking the source range to see whether there are any rows there or not.

If I have to I will.

 

[Krishnakumar]

Hi,

may be...

MsgBox Application.CountA(array)

 

[Glory]

Great idea. Gives invalid procedure call or argument if the aray's elements have no contents though.

 

[Krishnakumar]

It even give 1 for the following

Dim b
MsgBox Application.CountA(b)

So need some other foolproof technique.

 

[GTO]

If I am understanding, maybe:

Option Explicit
    
Sub exa()
Dim myRange As Range
Dim aVals As Variant
    
    Set myRange = Range("A1:B10")
    aVals = myRange.Value
    If CheckArray(aVals) Then
        MsgBox "do something"
    End If
    
End Sub
    
Function CheckArray(ary As Variant) As Boolean
Dim y As Long
    
    If IsArray(ary) Then
        For y = 1 To UBound(ary, 2)
            If Application.CountA(Application.Index(ary, 0, y)) > 0 Then
                CheckArray = True
                Exit For
            End If
        Next
    End If
End Function

 

[GTO]

Sorry, brain freeze.

Function CheckArray(ary As Variant) As Boolean
Dim y As Long
    
    If IsArray(ary) Then
        If Application.CountA(ary) > 0 Then
            CheckArray = True
        End If
    End If
End Function

 

[james_lankford]

adapting GTO's code
you could test if the range is empty first
and then only fill your array variable if the range does indeed contain data

Sub testit2()
    
    Dim rnge As Excel.Range
    
    Dim av As Variant
    
    Set rnge = ThisWorkbook.Worksheets("sheet1").Range("a2:d3")
    
    If Excel.Application.WorksheetFunction.CountA(rnge) > 0 Then
        'MsgBox "yes, its an array"
        av = rnge.Value
    Else
        'MsgBox "nope, not an array"
    End If
    
    ' if not an array then this returns "Empty" 
    ' if it is an array then it returns "Variant()"
    MsgBox TypeName(av) 
    
    ' if not an array then this returns 0 
    ' if it is an array then it returns 8204 
    MsgBox VarType(av)
    
End Sub

 

[Glory]

James: That's exactly the way I've been doing this; just checking the source range and then skipping some code by going to a label if the range contains too few full cells. That's seemed like the most efficient way so far.

GTO: I guess the first post would only using the upper bound of the second dimension, which would be a problem if you needed an array with multiple dimensions that contained unequal amounts of elements?

It worked great for the example though. The only thing is that a custom function is even more convoluted than what I'm doing now. I was mostly wondering if there's a standard function in Excel or VBA that would turn the trick.

Somebody will find a use for that idea.