CODEWITHSUNDEEP
excelvbaerror-handlingexcel-2003
Talguy
Talguy

Reputation: 1095

Handle Error 9 when there is an Empty Array

I am writing a script that will loop through an Excel spreadsheet and find if there are duplicates of selected cells. If there are duplicates then the function will return an array of which rows are duplicates and create a comment to tell me the rows.

I have been able to handle error 0 but now I am getting error 9 when I check if there are elements in the array using the UBound function.

How do I validate if the array of integers is empty?

Function IsArrayEmpty(anArray As Variant) As Boolean
    Dim i As Integer

    On Error Resume Next
        i = UBound(anArray, 1)
    Select Case (Err.Number)
        Case 0
            IsArrayEmpty = True
        Case 9
            IsArrayEmpty = True
        Case Else
            IsArrayEmpty = False
    End Select
End Function

Upvotes: 10

Views: 33687

Answers (9)

SBF
SBF

Reputation: 1369

While writing a VBA macro in MS Excel (Office 365 64-bit) I came across this "subscript out of range" issue...

Adding a call to VarType() before the UBound() call solves this weird problem:

Dim arr() As String    ' Empty array - UBound() should return -1 but throws error 9 instead
Dim VT As VbVarType: VT = VarType(arr)
Dim upper_bound: upper_bound = UBound(arr)

Upvotes: 0

Kepa
Kepa

Reputation: 9

You always can use "isArray()" function

Dim yourArray as variant
if not isArray(your_array) then
   msgbox("empty")
else
   msgbox("with data")
end if

Upvotes: 1

phrebh
phrebh

Reputation: 160

Chip Pearson gave the answer years ago and it still works. Here's the function I've had in my library for almost four years.

Public Function IsArrayEmpty(arr As Variant) As Boolean
    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 occasion, under circumstances I
        ' cannot reliably replicate, Err.Number
        ' will be 0 for an unallocated, empty array.
        ' On these occasions, LBound is 0 and
        ' UBound is -1.
        ' To accommodate 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

    Err.Clear
End Function

Basically, it checks to make sure you passed an array, then it attempts to find the upper bound (which will throw an error if the array is empty), and finally it compares the lower bound to the upper bound to make sure the array really isn't empty.

Upvotes: 3

Joaquinglezsantos
Joaquinglezsantos

Reputation: 1620

The UBound and LBound will return the upper and lower boundries of a given array. So, if Ubound(arr) is equal than LBound(arr) then it is empty.

Dim arr() As String

If UBound(arr) = LBound(arr) Or UBound(arr) <= 0 Then
   Debug.Print "empty"
Else
   Debug.Print "not empty"
End If

Upvotes: 0

omegastripes
omegastripes

Reputation: 12602

You can check if the array is empty by retrieving total elements count using JScript's VBArray() object (works with arrays of variant type, single or multidimensional):

Sub Test()

    Dim a() As Variant
    Dim b As Variant
    Dim c As Long

    ' Uninitialized array of variant
    ' MsgBox UBound(a) ' gives 'Subscript out of range' error
    MsgBox GetElementsCount(a) ' 0

    ' Variant containing an empty array
    b = Array()
    MsgBox GetElementsCount(b) ' 0

    ' Any other types, eg Long or not Variant type arrays
    MsgBox GetElementsCount(c) ' -1

End Sub

Function GetElementsCount(aSample) As Long

    Static oHtmlfile As Object ' instantiate once

    If oHtmlfile Is Nothing Then
        Set oHtmlfile = CreateObject("htmlfile")
        oHtmlfile.parentWindow.execScript ("function arrlength(arr) {try {return (new VBArray(arr)).toArray().length} catch(e) {return -1}}"), "jscript"
    End If
    GetElementsCount = oHtmlfile.parentWindow.arrlength(aSample)

End Function

For me it takes about 0.3 mksec for each element + 15 msec initialization, so the array of 10M elements takes about 3 sec. The same functionality could be implemented via ScriptControl ActiveX (it is not available in 64-bit MS Office versions, so you can use workaround like this).

Upvotes: 0

Nigel Heffernan
Nigel Heffernan

Reputation: 4726

Is your array variant Empty or Empty()?

'Empty' is an uninitialised variant: IsEmpty(myVar) will return true... And you could be fooled into thinking you have an empty array (which is 'Empty()', not 'Empty' - try to keep up, there will be a short test after this class) because IsEmpty(myArray) returns True, too.

Dim myVar as Variant      ' this is currently Empty, and Ubound returns an error

Dim myArray() as variant  ' this is currently Empty(), and Ubound returns an error  



Redim myVar(0 to 0)   ' this is no longer empty, and has a valid Ubound

Redim myArray(0 to 0) ' this is no longer empty, and has a valid Ubound

A reliable way to check myVar is TypeName(myVar) - if it's an array, the name contains brackets:

  
If Instr(Typename(myVar), "(") > 0 then  

    ' we now know it is an array  
    If Not IsEmpty(myVar) Then  

       ' We can now check its dimensions  
        If Ubound(myVar) > 0  
             ' insert error-free code here  
        Endif  

    Endif  

Endif

The full answer is 'Detecting an array variant in Excel VBA' on Excellerando.

Upvotes: 4

CaBieberach
CaBieberach

Reputation: 1768

.. I am still getting error #9 script out of bounds

if you get error #9....doesn't that mean that you are geting the info that you need (array is empty)?

Upvotes: 1

Alex K.
Alex K.

Reputation: 175766

Your function is failing because if there is no error raised by UBound() (i.e. the array is dimensioned) then Err.Number is 0 and:

Case 0
  IsArrayEmpty = True

is executed returning an incorrect result.

The simplest way is to just trap the error:

Function IsArrayEmpty(anArray As Variant) As Boolean
On Error GoTo IS_EMPTY
If (UBound(anArray) >= 0) Then Exit Function
IS_EMPTY:
    IsArrayEmpty = True
End Function

Upvotes: 7

Dr. belisarius
Dr. belisarius

Reputation: 61016

Try this to check an empty array: 

Dim arr() As String

If (Not arr) = -1 Then
   Debug.Print "empty"
Else
   Debug.Print "UBound is " & UBound(X)
End If

HTH!

Upvotes: 9

Related Questions