VBA处理空数组错误 [英] VBA Handle Empty Array Error

问题描述

我正在写一个脚本，通过我的excell电子表格循环，找到所选单元格是否有重复。如果有dups，那么函数将返回一个数组，哪些行是dups，并创建一个注释告诉我哪些行是dups。我已经能够处理错误0，但是当我检查数组时是否收到错误9，如果使用UBound函数有元素。有没有人知道如何验证数组的整数，如果它的空或不是因为我的代码似乎没有做这个工作。以下是我的代码

 函数IsArrayEmpty（anArray As Variant）As Boolean 
 Dim i As Integer 
 
 On Error Resume Next 
i = UBound（anArray，1）
选择案例（Err.Number）
案例0 
 IsArrayEmpty = True 
案例9 
 IsArrayEmpty = True 
案例Else 
 IsArrayEmpty = False 
结束选择
结束函数
  

解决方案

您的函数失败，因为如果没有由 UBound（）（即数组的大小），然后 Err.Number 为0和：

 案例0 
 IsArrayEmpty = True 
  

执行返回错误的结果。

最简单的方法是捕捉错误：

 函数IsArrayEmpty（anArray As Variant） As Boolean 
 On Error GoTo IS_EMPTY 
 If（UBound（anArray） > = 0）然后退出函数
 IS_EMPTY：
 IsArrayEmpty = True 
结束函数
  

I am writing a script that will loop through me excell spreadsheet and find if there are any duplicates of the selected cells. if there are dups then the function will return an array of which rows are dups and create a comment tell me which rows are dups. I have been able to handle error 0 but now I am getting error 9 when I check the array if there are elements in it using the UBound function. Does anyone know how to validate the array of integers if its empty or not cause my code seems to not be doing the job. Below is my code

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

解决方案

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

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