CODEWITHSUNDEEP
vbaexcelfilter
Stupid_Intern
Stupid_Intern

Reputation: 3450

Ubound(Filter()) Function returns true for partial string matches

Below function returns true for partial matches, how can I modify it to return True only if Whole string is matched.

E.g. if array has AAA, A , BB , B

and if I pass AAA it returns True for A as well.

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

is there a simple modification to this or should I write separate code to implement that ?

Upvotes: 2

Views: 3973

Answers (2)

T.M.
T.M.

Reputation: 9948

This alternative comes closest to the the original question 'Is there a simple modification?'. Instead of using a different approach, it uses the same Filter function applied to the same (1dim) array:

Syntax Filter(sourcearray, match[,include [,compare]])

       Used compare argument: vbTextCompare, not case sensitive
                              (use vbBinaryCompare if CaseSensitive)

Additional Features

Furthermore it allows to choose between

  • i) whole string search.
  • ii) partial match,

The code is tested and executes without loss of performance.

Code

Public Function IsInArray(stringToBeFound As String, arr As Variant, Optional bFull = False) As Boolean
'  Purpose: search strings in 1dim arrays using the Filter function
'           i)  whole strings   (bFull = True) or
'           ii) partial matches (bFull = False, default)
'  Exclude empty strings "" (Note: if not wanted, comment the next line out!)
   If stringToBeFound = "" Then Exit Function
If bFull Then             ' i)  search whole strings
   IsInArray = (UBound(Filter(Split(Join(arr, ".|") & ".|", "|"), stringToBeFound & ".", , vbTextCompare)) > -1)
Else                      ' ii) partial match
   IsInArray = (UBound(Filter(arr, stringToBeFound, , vbTextCompare)) > -1)
End If
End Function

Note

It is to be remarked, that the Filter function would accept empty ""-strings, too. That's why I excluded them in the IsInArray function as well; if you don't want that, simply comment the first code line out.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You can use markers to remove any partial matches from:

ary = Array("AAA", "A", "BB", "B")

Let's assume that the $ character does not appear anywhere in the array.
We make a string like:

st = "$" & Join(ary, "$") & "$"

which is $AAA$A$BB$B$

If we want to see if A is in the array, check for InStr(st,$A$) > 0
If we want to see if AAA is in the array, check for InStr(st,$AAA$) > 0
etc.

If $ is in the array, use something like Chr(1) as the marker.

EDIT#1:


IMPLEMENTATION:

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim st As String, M As String

    M = "$"
    st = M & Join(arr, M) & M
    IsInArray = InStr(st, stringToBeFound) > 0
End Function

EDIT#2:

USAGE:

So if we wanted to see if the string AA is in the array:

Sub MAIN()
    ary = Array("AA", "BB", "CC", "", "DD")
    MsgBox IsInArray("$AA$", ary)
End Sub

and if we want to see if any of the array elements is empty:

Sub MAIN()
    ary = Array("AA", "BB", "CC", "", "DD")
    MsgBox IsInArray("$$", ary)
End Sub

Upvotes: 4

Related Questions