Question

I am trying to write a UDF that takes either a range or an array from the user and iterates over it. If I declare it as a range like so:

Function Test(param As Range) As Variant
    Dim total As Integer
    Dim cell As Range
    total = 0
    For Each cell In param
        total = total + cell.Value2
    Next
    Test = total
End Function

it works fine when called like =TEST(C22:C24) but gives an error when called like =TEST({1,2,3,4}). On the other hand if I declare it as a variant like so:

Function Test(param As Variant) As Variant
    Dim i As Integer, total As Integer
    total = 0
    On Error GoTo endfunc
    For i = 1 To 100
        total = total + param(i)
    Next
endfunc:
    Test = total
End Function

当它被称作=TEST({1,2,3,4})时,当它被称作=TEST(C22:C24)时,它就始终如一地走到我所给出的范围结束(因此我给它100的上限)。我可以在变式上使用UBound,以获得上限,这就产生了错误。是否有办法在这两种情况下开展工作?

Answer 1

Aha! 采用我找到的解决办法

Function Test(param As Variant) As Variant
    Dim total As Integer
    total = 0
    If TypeName(param) = "Range" Then
        Dim cell As Range
        For Each cell In param
            total = total + cell
        Next
    Else
        Dim i As Integer
        For i = 1 To UBound(param)
            total = total + param(i)
        Next
    End If
    Test = total
End Function

Answer 2

......

Function Test(param As Variant) As Variant

    Dim total As Integer
    Dim item As Variant
    
    param = param
    
    If IsArray(param) Then
        total = 0
        For Each item In param
            total = total + item
        Next
    Else
        total = param
    End If
    
    Test = total
    
End Function

本部分<代码>第1段= ......

如果param 包含一个范围标,则该代码将各种数值从射程到相同变量,因为价值财产是地标的违约财产。
如果<代码>第<0>/代码>包含一个阵列,则该阵列被分配到相同的变量。
如果<代码>第<0>/代码>包含单一数值,则该数值与相同变量。

友情链接