how to have multiple optional arguments in a user defined function in VBA/ Excel

Question

I need to develop an UDF for checking a cell value against several possible entries to check whether the cell is equal to any of those values or not. As you see I have an idea regarding how to do this check. But coding the function in a way that can accept multiple optional entries in not clear for me. for instance I am looking something dynamic like CONCATENATE in EXCEL { CONCATENATE( text1, [ text2, ... text_n ] )}. My code for such function with 5 optional argument is as follow:

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

I want to make it relative to number of optional entries desired by the user (As said like concatenate). and also if possible make the checking of entries automatic through a loop. I tried adding the texts as an array, but not working!

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

I was not able to do that as well.

Thanks and regards, M

Answer 1

Use the ParamArray. The ParamArray must always be the last thing to be declared and must be of the type Variant. It will allow you to enter as many variables as you like without having to define each one of them.

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

However, I would do this using:

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

This way you avoid using a dictionary which could cause reference errors. Your Dictionary in your function also doesn't handle duplicate values. A Dictionary doesn't allow you to have multiple keys of the same value so as soon as you have a duplicate text value your function will fall over as these are not handled.