vba - 如何在 VBA/Excel 中的用户定义函数中具有多个可选参数

Question

我需要开发一个 UDF，用于根据多个可能的条目检查单元格值，以检查单元格是否等于这些值中的任何一个。正如你所看到的，我对如何进行这项检查有一个想法。但是以可以接受多个可选条目的方式对函数进行编码对我来说并不清楚。例如，我正在寻找动态的东西，例如 EXCEL { CONCATENATE( text1, [ text2, ... text_n ] )} 中的 CONCATENATE 。我的带有 5 个可选参数的函数的代码如下:

Function IfAmong(TextToCheck As String, Text1 As String, Optional Text2 As String, _ 
    Optional Text3 As String, Optional Text4 As String, Optional Text5 As String,_ 
    Optional text6 As String) As Boolean    
    Dim dd As New Scripting.Dictionary
    dd.CompareMode = TextCompare
       dd.Add Text1, dd.Count
       If Text2 <> "" Then dd.Add Text2, dd.Count
       If Text3 <> "" Then dd.Add Text3, dd.Count
       If Text4 <> "" Then dd.Add Text4, dd.Count
       If Text5 <> "" Then dd.Add Text5, dd.Count
       If text6 <> "" Then dd.Add text6, dd.Count
    IfAmong = dd.Exists(TextToCheck)
    dd.RemoveAll
End Function

我想让它与用户所需的可选条目的数量相关(正如所说的连接)。如果可能的话，还可以通过循环自动检查条目。我尝试将文本添加为​​数组，但不起作用!

for i =2 to Ubound(text(i))
     if text(i) <>"" then..........
next

我也无法做到这一点。

谢谢和问候，中号

Answer 1

使用ParamArray。 ParamArray 必须始终是最后声明的内容，并且类型必须为 Variant。它将允许您输入任意数量的变量，而无需定义每个变量。

Function IfAmong(TextToCheck, ParamArray Text() As Variant) As Boolean
    Dim txt As Variant
    Dim dd As New Scripting.Dictionary

    dd.CompareMode = TextCompare

    For Each txt In Text()
        If Not txt = vbNullString Then dd.Add Key:=txt, Item:=dd.Count
    Next txt

    IfAmong = dd.Exists(TextToCheck)
    dd.RemoveAll
End Function

但是，我会使用以下方法来做到这一点:

Function IfAmong(TextToCheck, ParamArray Text() As Variant) As Boolean
    Dim txt As Variant
    ' Default value if not found
    IfAmong = False
    For Each txt In Text()
        ' Make sure input is text
        If TypeName(txt) = "String" Then
            ' Test if they're equal ignoring case
            If LCase(txt) = LCase(TextToCheck) Then
                ' Set to true as found
                IfAmong = True
                ' There's no point to keep searching as we've already got our answer so lets exit
                Exit For
            End If
        End If
    Next txt
End Function

这样您就可以避免使用可能导致引用错误的字典。函数中的字典也不处理重复值。字典不允许您拥有多个具有相同值的键，因此一旦您有重复的文本值，您的函数就会失败，因为这些值未得到处理。