VBA / How can I filter an array on exact string?

Question

As in my title, I'm trying to filter out specific strings from a VBA array, based on an other array.

My code looks something like this :

For Each item In exclusions_list
    updated_list = Filter(updated_list, item, False, vbTextCompare)
Next item

My issue is that I only want to exclude exact matches and I can't seem to find a way to do so.

If I have "how" in exclusions_list, I'd like to exclude "how" from updated_list but not "however".

My apologies if this has been asked before. I couldn't find a clear answer and I am not very familiar with VBA.

Thanks !

Answer 1

The Filter method only looks for substrings. It does not have a way of recognizing whole words.

One way to do this is by using Regular Expressions which include a token to recognize word boundaries. This will only work if the substrings you are considering do not include non-Word characters. Word characters are those in the set of [A-Za-z0-9_] (with some exceptions for non-English languages).

For example:

Option Explicit
Sub foo()
    Dim arr
    Dim arrRes
    Dim V
    Const sfilter As String = "gi"
    Dim col As Collection

arr = Array("Filter", "by", "bynomore", "gi", "gif")

Dim re As Object, MC As Object, I As Long
Set col = New Collection
Set re = CreateObject("vbscript.regexp")
    With re
        .ignorecase = True
        .Pattern = "\b" & sfilter & "\b"
        For I = 0 To UBound(arr)
            If .test(arr(I)) = False Then _
                col.Add arr(I)
        Next I
    End With
ReDim arrRes(0 To col.Count - 1)
    For I = 1 To col.Count
        arrRes(I - 1) = col(I)
    Next I
End Sub

The resulting array arrRes will contain gif but not gi

Answer 2

Add a reference to RegEx:

Option Explicit
Sub Filter()
    Dim words() As String
    words = Split("how,however,test3,test4,,,howevermore,how,whatsohowever,test1,test2", ",")
    Dim regex As New RegExp
    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "^how$" ' ^ means must start with and $ means must end with
    End With
    Dim i As Integer
    For i = 0 To UBound(words)
        If regex.Test(words(i)) Then
            ' Debug.Print words(i) + " is an exact match!"
            words(i) = vbNullString  ' Just clear out item, skip later.
        Else
            ' Debug.Print words(i) + " is NOT  a match!"
        End If
    Next i

    For i = 0 To UBound(words)
        If (StrPtr(words(i)) <> 0) Then ' We can use this to explicitly catch vbNullString, because "" has a pointer. 
          Debug.Print words(i)
        End If
    Next i

End Sub

Answer 3

Approach via a very simple Replace function

In addition to the valid solutions above and just to demonstrate another approach using a simple Replace function. This solution doesn't pretend to be the most efficient way to execute exclusions.

Example code

Sub Howdy()
' Purpose: exclude exactly matching array items (not case sensitive)
  Dim exclusions_list, updated_list, item
  exclusions_list = Array("How", "much")
' assign test list (with successive repetitions)
  updated_list = Split("Bla bla,How,how,Howdy,However,How,much,much,much,Much,Much,How much,something else", ",")
  ' Debug.Print UBound(updated_list) + 1 & " items in original list: """ & Join(updated_list, "|") & """"
' execute exclusions
  For Each item In exclusions_list
      updated_list = modifyArr(updated_list, item)   ' call helper function modifyArr()
      ' Debug.Print UBound(updated_list) + 1 & " items excluding """ & item & """:" & vbTab & """" & _
                    Join(updated_list, "|") & """"
  Next item
End Sub

Note

Not outcommenting the Debug.Print Statements you'd get the following results in the VBE immediate window:

13 items in original list:  "Bla bla|How|how|Howdy|However|How|much|much|much|Much|Much|How much|something else"
10 items excluding "How":   "Bla bla|Howdy|However|much|much|much|Much|Much|How much|something else"
5 items excluding "much":   "Bla bla|Howdy|However|How much|something else"

Helper function modifyArr()

Please note that it's necessary to provide for successive repetitions of strings to be excluded, as a single Replace statement wouldn't exceute every wanted replacement in subsequent string parts.

Function modifyArr(ByVal arr, ByVal item) As Variant
  Const C = ",": Dim temp$, sLen$
  temp = Replace(C & Join(arr, C) & C, C & item & C, Replace:=C, Compare:=vbTextCompare)
  Do While True             ' needed to get successive repetitions !
      sLen = Len(temp)
      temp = Replace(temp, C & item & C, Replace:=C, Compare:=vbTextCompare)
      If sLen = Len(temp) Then Exit Do
  Loop
' return
  modifyArr = Split(Mid$(temp, 2, Len(temp) - 2), C)
End Function

Answer 4

Initially, I'm not clear why people are getting into RegExp here. RegExp is for complex pattern matching, not an exact match. For more on that point, see another answer here.

Basic Loop

The simplest way to do this is to loop through the array and test each value:

Sub ShowFilterOutExact()
    startingArray = Array("Filter", "by", "bynomore", "gi", "gif")
    filteredArray = FilterOutExact("gif", startingArray)
End Sub

Function FilterOutExact(exactValue, sourceArray)

   'Start with a returnArray the same size as the sourceArray
    ReDim returnArray(UBound(sourceArray))

    For i = 0 To UBound(sourceArray)
        If sourceArray(i) <> exactValue Then
            returnArray(matchIndex) = sourceArray(i)
            matchIndex = matchIndex + 1
        End If
    Next
    
    'Now trim the returnArray down to size
    ReDim Preserve returnArray(matchIndex - 1)
    
    FilterOutExact = returnArray
    
End Function

For alternatives to the equal operator (or <> for "does not equal"), this answer has more details.

Replace and Filter

You can also do a workaround with the built in Filter() function to get an exact match.

Function FilterExactMatch(SourceArray, Match, Optional DumpValue = "#/#/#", Optional Include = True)
    'Make sure the DumpValue is not found in the sourceArray in any element
    For i = LBound(SourceArray) To UBound(SourceArray)
        ExactMatch = SourceArray(i) = Match
        If ExactMatch Xor Include Then SourceArray(i) = DumpValue
    Next
    FilterExactMatch = Filter(SourceArray, DumpValue, False)
End Function

Filter out multiple values at once

Finally, it turns out the Application.Match function can check an array of values against an array of values to see if any match. This can be used to filter out multiple values at once (or just one) on an exact basis.

Function FilterOutMultiple(unwantedValuesArray, sourceArray)
    If LBound(sourceArray) <> 0 Then
        MsgBox "sourceArray argument must be zero-based for this to work as written"
        Exit Function
    End If
    matchArray = Application.Match(sourceArray, unwantedValuesArray, 0)
    matchCount = Application.Count(matchArray) 'Count non-error values
    ReDim returnArray(UBound(sourceArray) - matchCount)
    j = -1
    For i = 0 To UBound(sourceArray)
        If IsError(matchArray(i + 1)) Then 'Keep the error indexes
            j = j + 1
            returnArray(j) = sourceArray(i)
        End If
    Next
    FilterOutMultiple = returnArray
End Function