VBA Filter Function

When dealing with arrays in VBA (Visual Basic for Applications), there are times when you’ll want to sift through the data to find specific elements. That’s where the Filter function steps in. It provides a streamlined way to search through an array and filter out elements based on a given criteria. This post will take a deep dive into the Filter function, uncovering its syntax, parameters, and some practical examples.

VBA Filter Function – Purpose, Syntax and Arguments

Uses

The Filter function in VBA is a powerful tool used for quickly filtering the elements of an array based on a specified string. You can use it to include or exclude elements that match the specified string.

Syntax

Filter(sourcearray, match, [include=True], [compare=0])

Arguments

• sourcearray: The array you want to filter. This is a one-dimensional array.
• match: The string you’re looking for. Filter will search for this string within each element of the source array.
• include (Optional): A Boolean value where True means you’ll get the elements that contain the match string and False means you’ll get the elements that do not contain the match string. The default is True.
• compare (Optional): Specifies the type of string comparison. The options are:
  • vbBinaryCompare (0): Performs a binary comparison.
  • vbTextCompare (1): Performs a textual comparison.

Use Cases in Real-World Applications:

• Data Cleaning: If you’re importing or processing large sets of data in Excel through VBA, the Filter function can help in cleaning up and standardizing your data.
• Dynamic Searches: For dynamic search implementations in Excel-based applications, where users can get real-time results as they type, the Filter function can prove to be quite handy.
• Categorization: If you’re categorizing lists based on specific keywords or tags, the Filter function can help in segregating data into respective categories quickly.

Understanding VBA Filter Function with Examples

1. Basic Array Filtering

This example demonstrates how to filter an array of names to retrieve only those that contain the substring “John”.

Dim names() As String
Dim filteredNames() As String

names = Split("John Doe,Jane Smith,Johnny Bravo,Andrew Johnson,Paul Adams", ",")
filteredNames = Filter(names, "John")

• Dim names() As String: Declares a dynamic array named ‘names’.
• Dim filteredNames() As String: Declares a dynamic array named ‘filteredNames’.
• names = Split(…): The ‘Split’ function breaks a comma-delimited string into an array.
• filteredNames = Filter(names, “John”): Uses the ‘Filter’ function to retrieve array elements containing the substring “John”.

2. Excluding Array Elements

This example filters out any names from the array that contain the substring “John”.

Dim names() As String
Dim withoutJohn() As String

names = Split("John Doe,Jane Smith,Johnny Bravo,Andrew Johnson,Paul Adams", ",")
withoutJohn = Filter(names, "John", False)

• Dim withoutJohn() As String: Declares a dynamic array named ‘withoutJohn’.
• withoutJohn = Filter(names, “John”, False): Uses the ‘Filter’ function to exclude array elements containing the substring “John”.

3. Case-Insensitive Filtering

Demonstrates how to perform a case-insensitive filter on an array of mixed-case names to find the name “JOHN”.

Dim mixedNames() As String
Dim caseInsensitiveFilter() As String

mixedNames = Split("JOHN Doe,jOhN Smith,Johnny Bravo,Andrew Johnson,Paul Adams", ",")
caseInsensitiveFilter = Filter(mixedNames, "JOHN", True, vbTextCompare)

• Dim mixedNames() As String: Declares a dynamic array named ‘mixedNames’.
• caseInsensitiveFilter = Filter(mixedNames, “JOHN”, True, vbTextCompare): Uses the ‘Filter’ function with ‘vbTextCompare’ to perform a case-insensitive search.

4. Filtering Numeric Values

Converts an array of numbers to strings and filters to get numbers containing “5”.

Dim numbers() As Variant
Dim strNumbers() As String
Dim filteredNumbers() As String
Dim i As Integer

numbers = Array(123, 456, 789, 505, 675)
ReDim strNumbers(UBound(numbers))

For i = LBound(numbers) To UBound(numbers)
    strNumbers(i) = CStr(numbers(i))
Next i

filteredNumbers = Filter(strNumbers, "5")

• Dim numbers() As Variant: Declares a variant array named ‘numbers’.
• ReDim strNumbers(UBound(numbers)): Resizes the ‘strNumbers’ array based on the size of ‘numbers’.
• For i = LBound(numbers) To UBound(numbers) … Next i: Loop to convert each number to a string.
• filteredNumbers = Filter(strNumbers, “5”): Filters to get strings containing the number “5”.

5. Filtering Dates

Convert an array of dates to strings and filter to retrieve only those from the year “2022”.

Dim dates() As Variant
Dim strDates() As String
Dim filteredDates() As String
Dim i As Integer

dates = Array("01/01/2022", "15/03/2021", "23/06/2022", "11/11/2019")
ReDim strDates(UBound(dates))

For i = LBound(dates) To UBound(dates)
    strDates(i) = CStr(dates(i))
Next i

filteredDates = Filter(strDates, "2022")

• Dim dates() As Variant: Declares a variant array named ‘dates’.
• ReDim strDates(UBound(dates)): Resizes the ‘strDates’ array based on the size of ‘dates’.
• For i = LBound(dates) To UBound(dates) … Next i: Loop to convert each date to a string.
• filteredDates = Filter(strDates, “2022”): Filters to get strings containing the year “2022”.

Conclusion

VBA’s Filter function stands as a testament to the language’s capability to manage and manipulate data arrays with finesse and efficiency. Through the variety of use cases presented above, it’s evident that this function is not only versatile but also an indispensable tool for any developer working with arrays in VBA. Whether you’re dealing with simple string arrays or more complex data structures, the Filter function offers a quick and straightforward approach to hone in on the data points that matter the most.

For both beginners and seasoned VBA developers, mastering the Filter function can significantly streamline coding tasks, making data processing more efficient and the overall coding experience more rewarding. As always, the key is practice. Try to integrate the Filter function in your future projects, experiment with its parameters, and see firsthand how it can elevate your VBA programming game. Until next time, happy coding!