使用UBound的Excel VBA自定义函数返回#VALUE错误求助

在VBA里，普通Sub过程和工作表自定义函数（UDF）处理单元格区域的逻辑完全不一样：

• 写Sub的时候，x = Range("A1:A5") 这行代码会自动把区域里的所有值读成一个二维数组（结构是(1到行数, 1到列数)），这时候用UBound(x)默认取第一维（行）的最大值，自然能拿到5。
• 但在工作表单元格里直接调用UDF时，Excel对传入的区域参数会做自动转换：如果是单列/单行的连续区域，它会转成一维数组；但如果你的UDF参数没明确声明类型，Excel可能直接把Range对象传进来——而UBound只能处理数组，碰着Range对象就直接报错返回#VALUE了。

两种修正方式：

• 第一种，直接把UDF的参数声明成Range类型，先把区域转成数组再用UBound：

Function test(x As Range)
    Dim arr As Variant
    arr = x.Value
    test = UBound(arr)
End Function

• 第二种，兼容单个单元格和数组的情况，先判断参数是不是数组：

Function test(x As Variant)
    If IsArray(x) Then
        test = UBound(x, 1)
    Else
        test = 1
    End If
End Function

内容的提问来源于stack exchange，提问作者Ivan