More array matching

In Checking arrays for exact matches I showed how a simple one-line function can check if a given value is in an array.

However, this revealed a problem when a value is found only in a substring of an array element, which is usually not what I want. I want to match the whole array element.

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

Dim arr As Variant
  arr = Split("a,man,a,plan,a,canal,panama", ",")
  ' returns True even though "c" is not a discrete element
  Debug.Print IsInArray(arr, "c")

Even though the letter "c" is not an element of the array, it is a substring, so this function returns
True

.

As a solution, I came up with a function that joins the array elements, looks for delimiters, then checks if the target string is the same value as the parsed substring. Here is another solution which simply loops through the array.

Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
  Dim i As Long
  For i = LBound(arr) To UBound(arr)
    If StrComp(arr(i), valueToFind) = 0 Then
      IsInArray = True
      Exit For
    End If
  Next i
End Function

Not as intelligent, but simpler. As an alternative, if we know that the array element is in a specific position, we can use an offset like this:

Function IsInArray(arr As Variant, valueToFind As Variant, _
    Optional offset As Long) As Boolean
  Dim i As Long

  For i = (LBound(arr) + offset) To UBound(arr)
    If StrComp(arr(i), valueToFind) = 0 Then
      IsInArray = True
      Exit For
    End If
  Next i

End Function

Ex: I have a large array and I know that the string I'm searching for is somewhere in the second half. I could call the above function like this:

IsInArray(arr, "c", (UBound(arr) / 2))

In the next few articles we'll focus on working with arrays and some (hopefully) useful functions you can leverage to make working with arrays easier.