The VBA FILTER function is listed under the array category of VBA functions. When you use it in a VBA code, it can return strings from an array of strings based on the string you have specified as a subset. In simple words, it can specify a string and it will look for all those values where that string is a part of the main string.
- SourceArray: The array with strings that want to filter.
- Match: The string you want to filter in the SourceArray.
- [Include]: This is a Boolean to define if the weather to filter value which includes the Match, or doesn’t include [This is an optional argument and if omitted VBA takes TRUE by default].
- [Compare]: A string value to define the comparison to make while filtering the array [This is an optional argument and if omitted VBA takes vbBinaryCompare by default].
- vbBinaryCompare: For binary comparison.
- vbTextCompare: For text comparison.
- vbDatabaseCompare: For Database Comparison.
To practically understand how to use the VBA FILTER function, you need to go through the below example where we have written a vba code by using it:
Sub example_FILTER() Dim nameAry As Variant Dim myAry(0 To 4) As String myAry(0) = Range("A1").Value myAry(1) = Range("A2").Value myAry(2) = Range("A3").Value myAry(3) = Range("A4").Value myAry(4) = Range("A5").Value nameAry = Filter(myAry, "Sh") End Sub
In the above code, we have used FILTER to get the value from the array (myAry has values from the cells we have defined) that includes “Sh” in it, and now, “nameAry” includes the “Jay Sh” and “Peter Sh” as both have “Sh” in it.
- If the source array which you have supplied is NULL then VBA will return an error.
- The array returned by the filter will always be a one-dimensional and zero-based array.