Contents
In VBA for Microsoft Excel, if you want to filter an array of strings based on a particular substring, you can use the Filter function. The Filter function returns a zero-based array containing all the elements of an existing array that match a specified filter string.
Basic syntax
VBA
Filter(SourceArray, Match, Include, [Compare])
Parameters
- SourceArray: The existing one-dimensional array of strings that you want to filter.
- Match: The substring you want to match in the array elements.
- Include: A Boolean value indicating whether to include (True) or exclude (False) the array elements that match the filter string.
- Compare: Optional. A numeric value specifying the type of comparison to use when comparing array elements with the filter string. 0 for a binary comparison (case-sensitive) or 1 for a textual comparison (case-insensitive).
Example Usage
Here’s a simple example showing how to use the Filter function in VBA:
VB
Sub FilterExample()
Dim arr() As String
Dim result() As String
' An array of strings
arr = Array("apple", "banana", "cherry", "date", "fig", "grape")
' Using the Filter function to find elements that contain "ap"
result = Filter(arr, "ap", True) ' Include elements that contain "ap"
' Print the results to the Immediate Window (Ctrl+G to view)
Dim i As Integer
For i = LBound(result) To UBound(result)
Debug.Print result(i)
Next i
End Sub
After running this code, the Immediate Window would display the following output:
- apple
- grape
This output includes the elements from the array arr where the substring “ap” appears.
Note: The Filter function is not designed for filtering tables or ranges in Excel worksheets. To filter data in tables or ranges, you would need to use Excel’s AutoFilter method or Advanced Filter in conjunction with VBA.*