vba 存在空数组时处理错误 9

Handle Error 9 when there is an Empty Array

提问by Talguy

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.

我正在编写一个脚本，该脚本将遍历 Excel 电子表格并查找是否存在所选单元格的重复项。如果有重复，则该函数将返回一个数组，其中包含重复的行并创建一个注释来告诉我这些行。

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.

我已经能够处理错误 0，但是现在当我使用 UBound 函数检查数组中是否有元素时出现错误 9。

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

回答by Dr. belisarius

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!

哼！

回答by Alex K.

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

您的函数失败，因为如果没有引发错误UBound()（即数组已确定大小），Err.Number则为 0 并且：

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

回答by Nigel Heffernan

Is your array variant Empty or Empty()?

您的数组变体是 Empty 还是 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.

'Empty' 是一个未初始化的变体：IsEmpty(myVar) 将返回 true...你可能会误以为你有一个空数组（它是 'Empty()'，而不是 'Empty' - 试着跟上，那里这将是一个简短的测试），因为 IsEmpty(myArray) 也返回 True。

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:

检查 myVar 的可靠方法是 TypeName(myVar) - 如果它是一个数组，则名称包含括号：

  
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.

完整的答案是Excellerando 上的“在 Excel VBA 中检测数组变体” 。

回答by phrebh

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

Chip Pearson多年前给出了答案，现在仍然有效。这是我在图书馆中使用了近四年的功能。

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.

基本上，它检查以确保您传递了一个数组，然后它尝试找到上限（如果数组为空，则会抛出错误），最后将下限与上限进行比较以确保数组确实不是空的。

回答by CaBieberach

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

.. 我仍然收到错误 #9 脚本越界

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

如果您收到错误 #9....这是否意味着您正在获取所需的信息（数组为空）？

回答by Kepa

You always can use "isArray()" function

你总是可以使用“isArray()”函数

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

回答by omegastripes

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

您可以通过使用 JScript 的VBArray()对象检索总元素数来检查数组是否为空（适用于变体类型的数组，单维或多维）：

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 ScriptControlActiveX (it is not available in 64-bit MS Office versions, so you can use workaround like this).

对我来说，每个元素大约需要 0.3 mksec + 15 msec 初始化，所以 10M 元素的数组大约需要 3 秒。可以通过ScriptControlActiveX实现相同的功能（它在 64 位 MS Office 版本中不可用，因此您可以使用像这样的解决方法）。

回答by Joaquinglezsantos

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.

UBound 和 LBound 将返回给定数组的上下边界。所以，如果 Ubound(arr) 等于 LBound(arr) 那么它是空的。

Dim arr() As String

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