Handle Error 9 when there is an Empty Array

Question

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

score 8 · Answer 1 · answered Nov 04 '10 at 15:24

8

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!

answered Nov 04 '10 at 15:24

Dr. belisarius

60,527
15
115
190

3

I claim no special knowledge, but apparently this method of checking for an empty array is exploiting a bug in VBA and shouldn't be used: http://stackoverflow.com/questions/183353/how-do-i-determine-if-an-array-is-initialized-in-vb6/183356#183356 – jtolle Nov 04 '10 at 15:38
@jtolle - your link is surely the definitive discussion of this question. perhaps this Q should marked a dup. or at least your link should be the answer to Talguy's Q. – hawbsl Nov 04 '10 at 15:50
1

@jtolle May be. I used this thing before, but as I didn't created it, I don't claim any virtue about it. – Dr. belisarius Nov 04 '10 at 15:52

score 7 · Answer 2 · answered Nov 05 '10 at 10:43

7

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

answered Nov 05 '10 at 10:43

Alex K.

171,639
30
264
288

that worked better but I am still getting error #9 script out of bounds – Talguy Nov 05 '10 at 12:10
1

`Dim a(): Debug.Print IsArrayEmpty(a)` works fine for me ... Make sure you don't have Tools > Options > General > Break on all Errors set – Alex K. Nov 05 '10 at 16:24

score 4 · Answer 3 · answered Dec 02 '10 at 19:27

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.

score 3 · Answer 4 · answered Nov 19 '18 at 15:21

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.

A case of commented "weird behavior" can be seen in evaluation of result array after `Split()` of empty string: `UBound(Split("","splitter")) = -1`, `LBound(Split("","splitter")) = 0`. Perhaps it is just an (awkward) way to indicate error from basic string functions, similarly as some string functions traditionally return -1 in various languages and platforms. — miroxlav, Sep 03 '19 at 10:35

score 1 · Answer 5 · answered Dec 01 '10 at 18:20

1

.. 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)?

answered Dec 01 '10 at 18:20

CaBieberach

1,748
2
17
26

that is the information that I want but it program does not jump to the error handler instead I get the pop that says error 9 – Talguy Dec 02 '10 at 14:28
Hi Talguy, have you tried Alex K. sugestion? According to this link if you change it in one office program, it could be transfered to the other. – CaBieberach Dec 02 '10 at 15:58
Check also this link . Section "Error Handling Blocks And On Error Goto". (Multiple "On Error" statement in the same procedure, wont catch correctly.) – CaBieberach Dec 02 '10 at 16:00

score 1 · Answer 6 · answered Nov 27 '19 at 14:37

1

You always can use "isArray()" function

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

answered Nov 27 '19 at 14:37

Kepa

9
2

score 0 · Answer 7 · edited May 23 '17 at 12:01

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).

Joaquinglezsantos · Answer 8 · 2018-05-18T13:04:07.120

0

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

edited May 18 '18 at 13:04

answered May 18 '18 at 12:56

Joaquinglezsantos

1,510
16
26

SBF · Answer 9 · 2023-08-03T15:34:28.873

0

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)

edited Aug 03 '23 at 15:34

answered Oct 05 '22 at 17:50

SBF

1,252
3
12
21

Handle Error 9 when there is an Empty Array

9 Answers9

Linked