Checking arrays for exact matches

I currently use the following function (or a variation of it, depending on whether I feel like searching for it) to check if a given value is in an array.

Function IsInArray(arr As Variant, valueToCheck As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, valueToCheck)) > -1)
End Function

Unfortunately, there's no built-in way to check if a value is in array. This is as close as we get.

While using it recently I found the following "problem".

Sub TestArray()

Dim listOfNames As Variant

listOfNames = Array("John", "Mary Jane", "Joe")

If IsInArray(listOfNames, "Mary") Then
  MsgBox "found it!"
End If

End Sub

Load some names into an array, then check if a given name is in the array. Simple, right? You would expect this to return false. But it doesn't. Since
Mary

is a substring of
Mary Jane

, this will return true. It depends on the situation, but most times this is not what I want. How do I fix this without looping through the array?

My Solution

This is what I came up with. I joined the array with a comma delimiter, then parse the resulting string for the occurrence of the alleged "match" (according to Filter). Then we do a string comparison to see if it is truly a match.

Sub TestArray()

  Dim listOfNames As Variant
  Dim nameToSearchFor As String
  Dim nameslist As String
  Dim startPosition As Long
  Dim nextCommaPosition As Long

  Dim matchedName As String

  listOfNames = Array("John", "Mary Jane", "Joe")
  nameToSearchFor = "Mary"

  If IsInArray(listOfNames, nameToSearchFor) Then
    nameslist = Join(listOfNames, ",")

    ' start from the name ....
    startPosition = InStr(nameslist, nameToSearchFor)
    ' get position of the comma after the name, starting from after the name ...
    nextCommaPosition = InStr(startPosition + 1, nameslist, ",")
    ' the alleged "match" is in between
    matchedName = Mid$(nameslist, startPosition, nextCommaPosition - startPosition)
    If StrComp(nameToSearchFor, matchedName) = 0 Then
      MsgBox "found it!"
    End If
  End If

End Sub

Of course, we don't want to do all of this in a procedure. So we'll have to modify our nice one-line function and turn it into this monster:

Function IsInArray(arr As Variant, valueToCheck As String, _
   Optional exactMatch As Boolean = True) As Boolean
  Dim wordList As String
  Dim startPosition As Long
  Dim nextCommaPosition As Long
  Dim matchedTerm As String

  If UBound(Filter(arr, valueToCheck)) > -1 Then
    wordList = Join(arr, ",")
    ' start from the allegedly matched term ....
    startPosition = InStr(wordList, valueToCheck)
    ' get position of the comma after the allegedly matched term ...
    nextCommaPosition = InStr(startPosition + 1, wordList, ",")
    ' the alleged "match" is in between
    matchedTerm = Mid$(wordList, startPosition, _
         nextCommaPosition - startPosition)
    If exactMatch Then
      IsInArray = (StrComp(valueToCheck, matchedTerm) = 0)
    Else
      IsInArray = (StrComp(valueToCheck, matchedTerm) <> 0)
    End If
  End If
End Function

I still want the option of a substring match, but 99.9% of the time I want an exact match, so I'll use an optional parameter that defaults to true.

This code assumes that the matched term is only located once in the source array. In other words, it only matches the first occurrence because it only searches once (using InStr). This is trivial because we don't care how many times the string appears, only whether it appears at all.

I feel like there's a way to leverage the boolean parameter at the end of the function, but I can't figure out how. Any thoughts?