有空数组时处理错误9 [英] Handle Error 9 when there is an Empty Array

问题描述

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

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.

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

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

推荐答案

您的函数失败了，因为如果 UBound（）没有引发任何错误（即，数组的尺寸已确定），则 Err.Number 为0 nd：

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

执行后返回错误结果。

最简单的方法就是捕获错误：

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

这篇关于有空数组时处理错误9的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！