How to check if an array is totally empty

[Glory]

I've declared a public array at the module level. I need to know what statement will check the array to see whether it has any values assigned to it.

"Isempty" doesn't work. Neither does "IsNull". Any ideas?

 

[shg]

A dynamic array or a static array?

Of what data type?

 

[Glory]

Dynamic array, with um... Long data, I think. 5- to 7-digit numbers.

 

[djreiswig]

I use

If Join(Arr) <> "" Then ... 'The array contains some values

It basically puts all of the elements together into a string so if there is at least one value then the string will not be null.

 

[JoeMo]

I use

If Join(Arr) <> "" Then ... 'The array contains some values

It basically puts all of the elements together into a string so if there is at least one value then the string will not be null.

This doesn't work for me. May have to check element by element:

Option Base 1
Dim vArr(10) As Variant
Sub myMod()
For i = 1 To UBound(vArr, 1)
    If IsEmpty(vArr(i)) Then Ct = Ct + 1
Next i
If Ct = UBound(vArr, 1) Then
    MsgBox "Array is empty"
Else
    MsgBox "Array is not empty"
End If
End Sub

 

[djreiswig]

Sorry, JoeMo, you are correct.

What I posted I use to keep UBound() from erroring out on an array that I have dimmed without a size. I use this if I am filling an array with an unknown number of elements. I just Dim vArr() as Variant and then ReDim vArr(i) in a loop when adding the data to the array. If I later want to loop through the array and try using ubound in my loop it causes an error if for some reason the redim part of my code wasn't executed, like if there wasn't any data that met the criteria to go into the array. I found Join() is the only way to prevent this. Use it before the loop using ubound and it will bypass the loop if there are no elements in the array.

I thought it might work with an empty array as well, but it appears that an array with dimensions isn't really empty, so Join() makes a string out of those characters.

 

[djreiswig]

If you just want to know if it is empty, you could simplify the code and use something like this. You don't have to count the non empty elements.

Dim vArr(10) As Variant
Sub myMod()
For i = 1 To UBound(vArr, 1)
    If Not IsEmpty(vArr(i)) Then
        MsgBox "Array is not empty"
        Exit For
    End If
Next i

 

[mirabeau]

There was a thread on a very similar theme not that long ago.

Perhaps may be of some use to you.

http://www.mrexcel.com/forum/showthread.php?t=531761

 

[Kenneth Hobson]

'http://www.mrexcel.com/forum/showthread.php?t=330424
Public Function IsBounded(vArray As Variant) As Boolean

    'If the variant passed to this function is an array, the function will return True;
    'otherwise it will return False
    On Error Resume Next
    IsBounded = IsNumeric(UBound(vArray))

End Function

 

[ZVI]

Dynamic array, with um... Long data, I think. 5- to 7-digit numbers.

Ones the long type dynamic array is dimensioned it contains zero values.
Seems that you are interested in a way to check if the dynamic array has been dimensioned (bounded) or not.
It’s just for fun, but the method works with dynamic array variables:

Dim MyArr() ' As Long

Function IsDimentioned(Arr) As Boolean
  IsDimentioned = (Not MyArr) <> -1
End Function
 
Sub Test()
  Debug.Print IsDimentioned(MyArr)
  ReDim MyArr(1 To 7)
  Debug.Print IsDimentioned(MyArr)
End Sub

If variant variable is used instead of any type of array variable then it doesn’t work - use the Kenneth’ solution for any cases.