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:

The [Include] argument is omitted from the function call. This argument therefore uses the default value True (i.e. return elements that do contain the [Match] string);
The [Compare] argument is also omitted from the function call. This argument therefore uses the default value vbBinaryCompare.
Note that the vbBinaryCompare option is case-sensitive and so would not, for example, match the strings "Smith" and "SMITH".

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"

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page