The VBA Filter Function

Description

The VBA Filter function returns a subset of a supplied string array, based on supplied criteria.

The syntax of the function is:

Filter( SourceArray, Match, [Include], [Compare] )

Where the function arguments are:

SourceArray-The original array of Strings, that you want to filter.
Match-The string that you want to search for, within each element of the supplied SourceArray.
[Include]-

An option boolean argument that specifies whether the returns array should consist of elements that include or do not include the supplied Match String.

This can have the value True or False, meaning:

True-Only return elements that include the Match String
False-Only return elements that do not include the Match String

If the [Include] argument is omitted, it takes on the default value True.

[Compare]-

An optional argument, specifying the type of String comparison to make.

This can be any of the following values:

vbBinaryCompare-performs a binary comparison
vbTextCompare-performs a text comparison
vbDatabaseCompare-performs a database comparison
If omitted, the [Compare] argument takes on the default value vbBinaryCompare.


VBA Filter Function Examples

Example 1

The following VBA code filters a supplied array of names, extracting only those that contain the substring "Smith".

' Filter an array of names for entries that contain "Smith".
' First create the original array of names.
Dim names As Variant
names = Array( "Ann Smith", "Barry Jones", "John Smith", "Stephen Brown", "Wilfred Cross" )
' Use the Filter function to extract names containing "Smith".
Dim smithNames As Variant
smithNames = Filter( names, "Smith" )
' The array smithNames now has length 2, and contains the Strings "Ann Smith" and "John Smith".

The above call to the VBA Filter function returns a one-dimensional array, of length 2, starting at index 0. The new array has the following elements:

smithNames(0) = "Ann Smith"
smithNames(1) = "John Smith"

Note that in the above example:


Example 2

The following VBA code filters a supplied array of names, extracting only those that do not contain "Smith".

' Filter an array of names for entries that do not contain "Smith".
' First create the original array of names.
Dim names As Variant
names = Array( "Ann Smith", "Barry Jones", "John Smith", "Stephen Brown", "Wilfred Cross" )
' Use the filter function to extract names that do not contain "Smith".
Dim otherNames As Variant
otherNames = Filter( names, "Smith", False )
' The array otherNames now has length 3, and contains the Strings
' "Barry Jones", "Stephen Brown" and "Wilfred Cross".

The above call to the VBA Filter function returns a one-dimensional array, of length 3, containing the following elements:

otherNames(0) = "Barry Jones"
otherNames(1) = "Stephen Brown"
otherNames(2) = "Wilfred Cross"