vba - checking for empty array [duplicate]

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 is returning true for uninitialized and false for initialized. I want to see if it has any data/content. However, the problem is I feel the above code is returning false even when there is no data in the array. How do I check that?

(I tried setting string s equal to the byte array. That was "". That means the array is empty, right?)

Shin · Accepted Answer · 2015-09-24 19:52:11Z

7

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

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

edited Sep 24, 2015 at 19:52

Shin

6732 gold badges13 silver badges30 bronze badges

answered May 12, 2012 at 10:04

assylias

330k84 gold badges680 silver badges806 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2018-05-08 21:48:18Z

6

I am simply pasting below the code by the great Chip Pearson.
Also check out his page on array functions.

I 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

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Sep 30, 2013 at 13:56

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges212 bronze badges

2 Comments

David Zemens Over a year ago

+1 I've been using this for a while, as well as some of his other array helper functions.

Jean-François Corbett Over a year ago

You posted the same answer twice.

Siddharth Rout · Accepted Answer · 2012-05-12 00:28:19Z

4

Try this

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

enter image description here

FOLLOWUP

does your code assume the array stores strings only? – TPG 7 mins ago

Yes. It did. If you want to test for all conditions then 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

edited May 12, 2012 at 0:28

answered May 12, 2012 at 0:05

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

1 Comment

TPR Over a year ago

does your code assume the array stores strings only?

Collectives™ on Stack Overflow

vba - checking for empty array [duplicate]

3 Answers 3

Comments

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Linked

Related