VBA - Filter Function

A Filter Function, which returns a zero-based array that contains a subset of a string array based on a specific filter criteria.



Parameter Description

  • Inputstrings − A required parameter. This parameter corresponds to the array of strings to be searched.

  • Value − A required parameter. This parameter corresponds to the string to search for against the inputstrings parameter.

  • Include − An optional parameter. This is a Boolean value, which indicates whether or not to return the substrings that include or exclude.

  • Compare − An optional parameter. This parameter describes which string comparison method is to be used.

    • 0 = vbBinaryCompare - Performs a binary comparison

    • 1 = vbTextCompare - Performs a textual comparison


Add a button and add the following function.

Private Sub Constant_demo_Click()
   Dim a,b,c,d as Variant
   a = array("Red","Blue","Yellow")
   b = Filter(a,"B")
   c = Filter(a,"e")
   d = Filter(a,"Y")
   For each x in b
      msgbox("The Filter result 1: " & x)
   For each y in c
      msgbox("The Filter result 2: " & y)
   For each z in d
      msgbox("The Filter result 3: " & z)
End Sub

When you execute the above function, it produces the following output.

The Filter result 1: Blue
The Filter result 2: Red
The Filter result 2: Blue
The Filter result 2: Yellow
The Filter result 3: Yellow