How to check if an array is empty in VBA?

Question

I have a function, which checks whether the array is empty or not. Since today I'm getting an runtime error 9. I don't know why.

Here is the code:

When db table contains data, pass it to the variable => arrItems
arrItems as Variant
ArrEmpty as Boolean

With rs
  If Not .EOF Then
      arrItems = .GetRows
      .Close
  End If
End With

ArrEmpty = IsArrayEmpty(arrItems) 

Private Function IsArrayEmpty(parArray As Variant) As Boolean
    IsArrayEmpty = IIf(UBound(parArray) > 0, False, True) //Here is invoked the runtime error 9
End Function

How can I check if the array is empty?

http://stackoverflow.com/questions/206324/how-to-check-for-empty-array-in-vba-macro — Siddharth Rout, Aug 05 '16 at 08:20
Possible duplicate of [How to check for empty array in vba macro](http://stackoverflow.com/questions/206324/how-to-check-for-empty-array-in-vba-macro) — LWC, Apr 08 '17 at 17:06

score 5 · Answer 1 · edited May 08 '18 at 21:49

There is a function on Chip Pearson's website that has always worked for me link

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
    ' UBoung 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

score 2 · Answer 2 · answered Aug 05 '16 at 08:20

2

Check first with isArray(arrItems) Then check the ubound

answered Aug 05 '16 at 08:20

Kelaref

547
1
8
26

1

I have checked now with `isArray(parArray)` and it returns me `true` but the ubound doesn't work. – yuro Aug 05 '16 at 08:30
perhaps the array is two-dimensional – Kelaref Aug 05 '16 at 08:57

yuro · Accepted Answer · 2016-08-05T09:01:11.853

0

Ok I haven't found a better solution like this:

With rs
  If Not .EOF Then
      arrItems = .GetRows
  Else
      arrItems = Array()
  End If
  .Close
End With

ArrEmpty = IsArrayEmpty(arrItems)

edited Aug 05 '16 at 09:01

answered Aug 05 '16 at 08:42

yuro

2,189
6
40
76

score 0 · Answer 4 · answered Feb 23 '20 at 05:40

I'm a C# coder by background and am shocked by how bad VBA is with arrays. I took a different approach. I've found Split() works really well and is efficient, so didn't pass as Array from a function but built a CSV string and tested if it was empty, or not, before processing.

I'm putting it here in the hope that this pattern helps someone who, like me, thinks VBA's handling of Arrays is awful and doesn't want to dig around in memory or use "hacks" that, I'm sure work, but seem unsatisfying from a programming POV...

 Private Function GetCsvOfStrings() As String
    Dim strDelim As String
    Dim strCsv As String
    strDelim = ","
    strCsv = ""

    Dim i As Integer
    For i = 1 To 10
        If Len(strCsv) > 0 Then
             strCsv = strCsv & strDelim & "test string (" & i & ")"
        Else ' it's an empty string
             strCsv = "test string (" & i & ")"
        End If
    Next i
    GetCsvOfStrings = strCsv
End Function

Public Sub Test()
    Dim strCsv As String
    Dim strArr() As String
    strCsv = GetCsvOfStrings()
    If Len(strCsv) > 0 Then 'I've got an "array" of strings
        strArr = Split(strCsv, ",")
        Dim i As Integer
        For i = LBound(strArr) To UBound(strArr)
            Debug.Print (strArr(i))
        Next i
    End If
End Sub

How to check if an array is empty in VBA?

4 Answers4

Linked