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?

Possible duplicate of How to check for empty array in vba macro — LWC
– LWC, Commented Apr 8, 2017 at 17:06

Community · Accepted Answer · 2018-05-08 21:49:10Z

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

Kelaref · Accepted Answer · 2016-08-05 08:20:39Z

2

Check first with isArray(arrItems) Then check the ubound

answered Aug 5, 2016 at 8:20

Kelaref

5151 gold badge9 silver badges26 bronze badges

2 Comments

yuro Over a year ago

I have checked now with isArray(parArray) and it returns me true but the ubound doesn't work.

Kelaref Over a year ago

perhaps the array is two-dimensional

yuro · Accepted Answer · 2016-08-05 09:01:11Z

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 5, 2016 at 9:01

answered Aug 5, 2016 at 8:42

yuro

2,2296 gold badges43 silver badges79 bronze badges

Comments

Mike Parks · Accepted Answer · 2020-02-23 05:40:35Z

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

Collectives™ on Stack Overflow

How to check if an array is empty in VBA?

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related