check if array is empty (vba excel)

Arr1 becomes an array of 'Variant' by the first statement of your code:

Dim arr1() As Variant

Array of size zero is not empty, as like an empty box exists in real world.

If you define a variable of 'Variant', that will be empty when it is created.

Following code will display "Empty".

Dim a as Variant

If IsEmpty(a) then
  MsgBox("Empty")
Else
  MsgBox("Not Empty")
End If

Adding into this: it depends on what your array is defined as. Consider:

dim a() as integer
dim b() as string
dim c() as variant

'these doesn't work
if isempty(a) then msgbox "integer arrays can be empty"
if isempty(b) then msgbox "string arrays can be empty"

'this is because isempty can only be tested on classes which have an .empty property

'this do work
if isempty(c) then msgbox "variants can be empty"

So, what can we do? In VBA, we can see if we can trigger an error and somehow handle it, for example

dim a() as integer
dim bEmpty as boolean

bempty=false

on error resume next
bempty=not isnumeric(ubound(a))
on error goto 0

But this is really clumsy... A nicer solution is to declare a boolean variable (a public or module level is best). When the array is first initialised, then set this variable. Because it's a variable declared at the same time, if it loses it's value, then you know that you need to reinitialise your array. However, if it is initialised, then all you're doing is checking the value of a boolean, which is low cost. It depends on whether being low cost matters, and if you're going to be needing to check it often.

option explicit

'declared at module level
dim a() as integer
dim aInitialised as boolean

sub DoSomethingWithA()
if not aInitialised then InitialiseA
'you can now proceed confident that a() is intialised
end sub

sub InitialiseA()
'insert code to do whatever is required to initialise A
'e.g. 
redim a(10)
a(1)=123
'...
aInitialised=true
end sub

The last thing you can do is create a function; which in this case will need to be dependent on the clumsy on error method.

function isInitialised(byref a() as variant) as boolean
isInitialised=false
on error resume next
isinitialised=isnumeric(ubound(a))
end function

I may be a bit late, but following simple stuff works with string arrays:

Dim files() As String

files = "..." 'assign array

If Not Not files Then
    For i = LBound(files) To UBound(files)
        'do stuff, array is not empty
    Next
End If

That's all the code for this.

@jeminar has the best solution above.

I cleaned it up a bit though.

I recommend adding this to a FunctionsArray module

• isInitialised=false is not needed because Booleans are false when created
• On Error GoTo 0 wrap and indent code inside error blocks similar to with blocks for visibility. these methods should be avoided as much as possible but ... VBA ...

Function isInitialised(ByRef a() As Variant) As Boolean
    On Error Resume Next
    isInitialised = IsNumeric(UBound(a))
    On Error GoTo 0
End Function

I would do this as

if isnumeric(ubound(a)) = False then msgbox "a is empty!"

This is what worked for me, thankful to all above suggestions.

Public Function IsInitialized(ByRef arr As Variant) As Boolean
    IsInitialized = True
    Dim lngLbound As Long
    On Error Resume Next
        lngLbound = LBound(arr)
    If Err.Number = 9 Then IsInitialized = False
    On Error GoTo 0
End Function

Above methods didn´t work for me. This did:

  Dim arrayIsNothing As Boolean

    On Error Resume Next
    arrayIsNothing = IsNumeric(UBound(YOUR_ARRAY)) And False
    If Err.Number <> 0 Then arrayIsNothing = True
    Err.Clear
    On Error GoTo 0

    'Now you can test:
    if arrayIsNothing then ...

The problem with VBA is that there are both dynamic and static arrays...

Dynamic Array Example

Dim myDynamicArray() as Variant

Static Array Example

Dim myStaticArray(10) as Variant
Dim myOtherStaticArray(0 To 10) as Variant

Using error handling to check if the array is empty works for a Dynamic Array, but a static array is by definition not empty, there are entries in the array, even if all those entries are empty.

So for clarity's sake, I named my function "IsZeroLengthArray".

    Public Function IsZeroLengthArray(ByRef subject() As Variant) As Boolean
        'Tell VBA to proceed if there is an error to the next line.
        On Error Resume Next
    
        Dim UpperBound As Integer
        Dim ErrorNumber As Long
        Dim ErrorDescription As String
        Dim ErrorSource As String
    
        'If the array is empty this will throw an error because a zero-length 
          'array has no UpperBound (or LowerBound).
        'This only works for dynamic arrays.  If this was a static array there 
          'would be both an upper and lower bound.
        UpperBound = UBound(subject)


        'Store the Error Number and then clear the Error object 
         'because we want VBA to treat unintended errors normally
        ErrorNumber = Err.Number
        ErrorDescription = Err.Description
        ErrorSource = Err.Source
        Err.Clear
        On Error GoTo 0
    
        'Check the Error Object to see if we have a "subscript out of range" error.  
          'If we do (the number is 9) then we can assume that the array is zero-length.
        If ErrorNumber = 9 Then
            IsZeroLengthArray = True

        'If the Error number is something else then 9 we want to raise 
        'that error again...
        ElseIf ErrorNumber <> 0 Then
            Err.Raise ErrorNumber, ErrorSource, ErrorDescription

        'If the Error number is 0 then we have no error and can assume that the
         'array is not of zero-length
        ElseIf ErrorNumber = 0 Then
            IsZeroLengthArray = False
        End If
    End Function

I hope that this helps others as it helped me.

I'm using this

If UBound(a) >= 0 Then
    ' not empty
Else 
    ' empty... UBound(a) = -1
End If

IsEmpty(a) did not work for me... I hate VBA

Dim arr() As Variant
Debug.Print IsEmpty(arr) ' False
Debug.Print UBound(arr) ' raises error
arr = Array()
Debug.Print IsEmpty(arr) ' False
Debug.Print UBound(arr) ' -1
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = "test"
Debug.Print IsEmpty(arr) ' False
Debug.Print UBound(arr) ' 0

The below function works for both static and dynamic arrays.

     Function array_Empty(testArr As Variant) As Boolean

        Dim i As Long, k As Long, flag As Long

        On Error Resume Next

        i = UBound(testArr)

        If Err.Number = 0 Then

           flag = 0
    
           For k = LBound(testArr) To UBound(testArr)
    
              If IsEmpty(testArr(k)) = False Then
        
                 flag = 1
                 array_Empty = False
                 Exit For
        
              End If
    
            Next k
    
            If flag = 0 Then array_Empty = True

        Else

            array_Empty = True

        End If

     End Function

Here's what I use to check if variable is an array with data in it.

Function dataInArray(arr) As Boolean
    'check if there is data in array (return true) or if it is empty (return false)
    'note - will return false for strings, integers, empty strings, null values, etc
        
    Dim varUbound As Variant
        
    dataInArray = False

    'if any other variable type, return false
    If VarType(arr) < 8192 Then Exit Function
    
    'if array initialized as variant (without parenth) it is empty (ie: uninitialized)
    If IsEmpty(arr) Then Exit Function
    
    'if array initialized as variant (with parenth) it is not empty (it is initialized so isempty returns false)
    On Error Resume Next
    varUbound = UBound(arr)
    
    If Err.Number = 9 Then GoTo dataInArray_Exit
    
    If Not varUbound >= 0 Then GoTo dataInArray_Exit
    
    dataInArray = True
    
dataInArray_Exit:
    Err.Clear
    On Error GoTo 0
    
End Function

All variables in this test function return false.

Sub testArrayFxn()
    Dim varNoParen As Variant
    Dim varParen() As Variant
    Dim strParen() As String
    Dim intParen() As Integer
    Dim strNoParen As String
    Dim intNoParen As Integer
    
    Debug.Print ("varNoParen returns " + CStr(dataInArray(varNoParen)))
    Debug.Print ("varParen returns " + CStr(dataInArray(varParen)))
    Debug.Print ("strParen returns " + CStr(dataInArray(strParen)))
    Debug.Print ("intParen returns " + CStr(dataInArray(intParen)))
    Debug.Print ("strNoParen returns " + CStr(dataInArray(strNoParen)))
    Debug.Print ("intNoParen returns " + CStr(dataInArray(intNoParen)))
End Sub

this worked for me:

Private Function arrIsEmpty(arr as variant)
    On Error Resume Next
    arrIsEmpty = False
    arrIsEmpty = IsNumeric(UBound(arr))
End Function

Tente isso:

Function inic(mtz As Variant) As Boolean
On Error Resume Next
    
    Dim x As Boolean
    x = UBound(mtz):    inic = x
    
End Function
...

if inic(mymtz) then
   debug.print "iniciada"
else
   debug.print "não iniciada"
end if

oh, a ten years old question. You don't need isempty/ isnumeric.

The following code is for Array defined as Variant.
This function will return String, "EMPTY" when there is an empty variant array passed in.

The Code:

Function AR_EMPTY_CHECK(AR1() As Variant)

        j = 0
        On Error GoTo Line_Result
        For i = LBound(AR1) To UBound(AR1)
                j = 1
                GoTo Line_Result
        Next i
        
Line_Result:

        If j = 0 Then
                AR_EMPTY_CHECK = "EMPTY"
        Else
                AR_EMPTY_CHECK = "NOT_EMPTY"
        End If

End Function