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)
      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?

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Related Articles:

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 3 Comment(s) on Checking arrays for exact matches:

  1. Wouldn't it be faster to use a collection instead of an array, then attempt to reference the collection item using its Key?

  2. I learned about the Filter function here (thanks!) and had used it in some code as you describe at the beginning of this post and run into the same substring problem right about the time you were posting this. I searched the web and came up with this alternative using Excel's Match function:

    Not (IsError(Application.Match(StringToMatch, ArrayWithStrings, 0)))

    Don't use WorksheetFunction.Match as it will yield a runtime error.


  1. [...] has a good post about finding exact matches in arrays. I use a similar method. I Join the array with delimiters around all the values, then use Instr to [...]

This article is closed to any future comments.
Excel School