Vba - checking an empty array

Question

Function IsVarArrayEmpty(anArray As Variant) Dim i As Integer On Error Resume Next i = UBound(anArray, 1) If Err.Number = 0 Then IsVarArrayEmpty = False Else IsVarArrayEmpty = True End If End Function 

It returns true for uninitialized and false for initialization. I want to see if it has any data / content. However, the problem is that I feel that the above code returns false even when there is no data in the array. How can I check this?

(I tried to set the string s to the byte array. It was a "". This means the array is empty, right?)

Answer 1

I personally use this - now if you are a ReDim array with ReDim v (1 To 5) As Variant , isArrayEmpty(v) will return false because v has 5 elements, although they are not all initialized.

 Public Function isArrayEmpty(parArray As Variant) As Boolean 'Returns true if: ' - parArray is not an array ' - parArray is a dynamic array that has not been initialised (ReDim) ' - parArray is a dynamic array has been erased (Erase) If IsArray(parArray) = False Then isArrayEmpty = True On Error Resume Next If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True Exit Function Else isArrayEmpty = False End If End Function 

Answer 2

I just paste under the code of the great Chip Pearson.
Also check out his page on array functions .

Hope this helps.

 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 LB As Long Dim UB As Long 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. UB = UBound(Arr, 1) If (err.Number <> 0) Then IsArrayEmpty = True Else '''''''''''''''''''''''''''''''''''''''''' ' On rare occassion, under circumstances I ' cannot reliably replictate, Err.Number ' will be 0 for an unallocated, empty array. ' On these occassions, LBound is 0 and ' UBound is -1. ' To accomodate the weird behavior, test to ' see if LB > UB. If so, the array is not ' allocated. '''''''''''''''''''''''''''''''''''''''''' err.Clear LB = LBound(Arr) If LB > UB Then IsArrayEmpty = True Else IsArrayEmpty = False End If End If End Function 

Answer 3

try it

 Sub Sample() Dim Ar As Variant Dim strTest As String strg = "Blah" Ar = Split(strg, "|") Debug.Print "TEST1 : "; IsArrayEmpty(Ar) strg = "Blah|Blah" Ar = Split(strg, "|") Debug.Print "TEST2 : "; IsArrayEmpty(Ar) End Sub Function IsArrayEmpty(Ar As Variant) As Boolean If InStr(TypeName(Ar), "(") > 0 Then If Not IsEmpty(Ar) Then If UBound(Ar) > 0 Then IsArrayEmpty = False Else IsArrayEmpty = True End If End If End If End Function 

Snapshot

Followup

Does your code assume that the array only stores strings? - TPG 7 minutes ago

Yes. So it was. If you want to test all conditions, I would recommend using the API. Here is an example

 Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ (pDst As Any, pSrc As Any, ByVal ByteLen As Long) Sub Sample() Dim Ar() As Long Debug.Print ArrayNotEmpty(Ar) '<~~ False ReDim Ar(1) Debug.Print ArrayNotEmpty(Ar) '<~~ True End Sub Public Function ArrayNotEmpty(Ar) As Boolean Dim Ret As Long CopyMemory Ret, ByVal VarPtr(Ar) + 8, ByVal 4 CopyMemory Ret, ByVal Ret, ByVal 4 ArrayNotEmpty = (Ret <> 0) End Function