XLA routines: EE_IsInArray

Nick's picture
EE_IsInArray is a function to check whether a value is in an array
Function EE_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
'http://excelexperts.com/xla-routines-eeIsInArray    for updates on this function

    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
        If nextCommaPosition = 0 Then
            matchedTerm = Mid$(wordList, startPosition)
            matchedTerm = Mid$(wordList, startPosition, _
                nextCommaPosition - startPosition)
        End If
        If exactMatch Then
            EE_IsInArray = (StrComp(valueToCheck, matchedTerm) = 0)
            EE_IsInArray = (StrComp(valueToCheck, matchedTerm) <> 0)
        End If
    End If
End Function