有没有一种方法可以创建一个VBA函数，可以将数组和范围都作为输入？

Question

我将尝试创建一个函数，可以接受范围或数组来执行一些进一步的计算。当传入数组时，函数可以正常工作，但是当在工作表上使用该函数处理范围时，会出现VALUE!错误。

代码如下：

Function COMRET(data as variant, N as integer)

     Dim nrows as long

     If IsArray(data) Then
        N = UBound(data,1)
     Else
        N = data.rows.count
     End If

     '... some other calculations here

End Function

问题似乎来自于上面的数组标识...当我注释掉上面的IF部分时，代码的其他部分似乎都正常。不确定我做错了什么。感谢帮助。谢谢！

- LYang

Answer 1

您正确地声明了data as Variant。当您将范围传递给它时，它将被正确地传递为Variant/Object/Range，但接下来的事情会变得有些复杂。

当您调用IsArray函数时，它将尝试使用传递的Range对象的默认属性，即Value。如果范围由多个单元格组成，则IsArray(Range.Value)为True。但是，当您调用UBound函数时，它是一个非常特殊的函数，因为它被突出显示为关键字，它不会尝试获取data.Value，而是直接操作data，而data是单个Range对象，因此它不能有上限（而其.Value可以）。

您需要正确检测您所传递的内容：

Public Function COMRET(data As Variant, N As Integer)
    Dim nrows As Long

    If TypeOf data Is Range Then
        N = data.Rows.Count
    ElseIf IsArray(data) Then
        N = UBound(data, 1)
    Else
        ' ???
    End If

End Function

- GSerg

---

谢谢。我认为我更喜欢这个解决方案来解决我的问题。 - LYang

Answer 2

你正在将data作为变量传递，这将导致无论在调用函数时是否传递范围或数组，IsArray(data)始终返回true。

添加第三个必需参数也可以，指示data变量的数据类型：

Function COMRET(data As Variant, Is_Array As Boolean, N As Integer)

     Dim nrows As Long

     If Is_Array = True Then
        N = UBound(data, 1)
     Else
        N = data.Rows.Count
     End If

     '... some other calculations here

End Function

或者，您可以添加一个检查，使用尝试在范围上使用UBound时抛出的错误。这样就不需要第三个参数了：

Function COMRET(data As Variant, N As Integer)

    Dim nrows As Long

    On Error Resume Next
        N = UBound(data, 1)
        If Err.Number <> 0 Then N = data.Rows.Count
        Err.Clear
    On Error GoTo 0

    '... some other calculations here

End Function

- Tim Stack

---

我明白了，谢谢你的快速回答。我会相应地调整我的代码。 - LYang

很高兴能够帮助！ - Tim Stack

Answer 3

VarType()应该成为你的好帮手：

' Const vbArray = 8192 (&H2000)
' Const vbString = 8
Debug.Print VarType(data)  ' = vbArray (8192) Or vbString (8) = 8200

' Is data an array of sorts?
Debug.Print CBool((VarType(data) And vbArray) = vbArray)

来自 MSDN 帮助：

VarType 函数永远不会单独返回 vbArray 的值。它总是与其他一些值相加，以指示特定类型的数组。常量 vbVariant 仅在与 vbArray 结合使用时返回，以指示 VarType 函数的参数是 Variant 类型的数组。例如，对于整数数组，返回的值计算为 vbInteger + vbArray，即 8194。如果对象具有默认属性，则 VarType（object）返回对象的默认属性的类型。

- Hel O'Ween