ExcelBanter - Variant array is empty problem

Perry,

Try code like following. The IsArrayEmpty function is one of about 25 array
information and manipulation procedures at
http://www.cpearson.com/excel/vbaarrays.htm/

Sub AAA()

Dim V As Variant
'''''''''''''''''''''''''''''''
' do something with V
'''''''''''''''''''''''''''''''
If IsArray(V) = True Then
If IsArrayEmpty(Arr:=V) = True Then
MsgBox "Array is empty and unallocated."
Else
MsgBox "Array contains " & _
Format(UBound(V) - LBound(V) + 1, "#,##0") & " elemnts."
End If
End If

End Sub

Public Function IsArrayEmpty(Arr As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''
' IsArrayEmpty
' This function tests whether the array is empty (unallocated). Returns TRUE
or FALSE.
'
' The VBA IsArray function indicates whether a variable is an array, but it
does not
' distinguish between allocated and unallocated arrays. It will return TRUE
for both
' allocated and unallocated arrays. This function tests whether the array
has actually
' been allocated.
'
' This function is really the reverse of IsArrayAllocated.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''

Dim Var As Variant
Err.Clear
On Error Resume Next
If IsArray(Arr) = False Then
' we weren't passed an array, return True
IsArrayEmpty = True
End If

' Attempt to get the UBound of the array. If the array is
' unallocated, an error will occur.
Var = UBound(Arr, 1)
If (Err.Number < 0) Or (Var < 0) Then
IsArrayEmpty = True
Else
IsArrayEmpty = False
End If

End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

wrote in message
ps.com...
I have written this code and for some reason i cannot make the logic
work correctly.

Dim site()
If IsEmpty(site) Then
test = 1
Else
test =0
End If

It always makes test = 0 when I am trying to get it to trip the logic
trap and create an output of test = 1. Thanks in advance for any help.

-perry