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.

Syntax

Filter(inputstrings,value[,include[,compare]]) 

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

Example

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)
   Next
  
   For each y in c
      msgbox("The Filter result 2: " & y)
   Next
  
   For each z in d
      msgbox("The Filter result 3: " & z)
   Next
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