In this tutorial, we’ll take a look at the VBA Array Filter function, which lets you find a subset of elements in an array based on a filtered search criteria. The Array Filter function is somewhat like the pure VBA equivalent of the AutoFilter function. AutoFilter filters lists in the spreadsheet while the Filter function filters directly on array objects in VBA.
While we could use mathematical expressions with AutoFilter, the plain Filter function is meant to work seamlessly with arrays of strings. You can use it to find numbers, but it is not the most robust way to do it and the results may not be what you expected. Bottom line is the filter function is excellent at filtering string arrays, and not so good at filtering anything else.
- VBA Array Filter Parameters
- Required Parameters Discussion
- Optional Parameters Discussion
VBA Array Filter Parameters
Let’s start off by looking at the parameters that the
Filter function accepts. There are two required arguments and two optional ones.
|SourceArray||The Array to be searched|
|Match||The String you want to find|
|Include||A binary parameter to search for the presence or absence of the matching string|
|Compare||Comparison type (binary or textual)|
The Required Parameters
The first two arguments are both required. They are the meat of the VBA Filter function and are almost always the only arguments you’ll need.
The SourceArray Argument
Our first input should be an array of strings. It can technically also be a variant array. If it’s a Variant array, the Array Filter function will convert each entry to a string before comparing. Then, when the output array is created, the entry will be typed as a string, as we can see in the Locals window here:
The Integer-typed 375 appears as a String variable in the Output Array
(Open the Locals Window by going to
View > Locals Window or
Alt+V > S by keyboard shortcut).
If you’re interested, the code below is a reproduction of the picture above and it demonstrates the basic array filtering syntax:
Sub filtering_for_numbers_as_strings() Dim langs(5) As Variant langs(0) = "English" langs(1) = 375 langs(2) = "Spanish" langs(3) = 442 langs(4) = "Chinese" langs(5) = 1299.5 output_arr = filter(langs, 375) End Sub
Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
This basic array filtering example is nice, but to become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.
The Match String
The second required argument is the
Match as String argument, which takes a string as its input and matches it to some entry in the original array. The purpose is to use this argument to return a subset of elements in your array containing that string.
In other words, the Filter function returns a second array with each matching entry in the original array having its own “slot” in the resultant array. It’s important to note that the output items will contain the entire item in the original array. Take this, for example. The following macro…
Sub partial_input_filter() Dim langs(5) As Variant langs(0) = "English" langs(1) = 375 langs(2) = "Spanish" langs(3) = 442 langs(4) = "Chinese" langs(5) = 1299.5 output_arr = filter(langs, "ish") End Sub
…will produce an array with two elements:
output_arr(0) = "English" output_arr(1) = "Spanish"
The filter window searches for substrings in a string
Each element of the
SourceArray is searched until the
Match string is found. The
Match string can be anywhere in your array elements.
This means that if we store both given and family names together in the same entry, we can expect to get back both the given and family names in our output.
Consider a code block like this:
Sub finding_james() Dim names(4) As String names(0) = "George Washington" names(1) = "John Adams" names(2) = "Thomas Jefferson" names(3) = "James Madison" names(4) = "James Monroe" james_is_popular = filter(names, "James") End Sub
This will output two people,
names(0) = "George" names(1) = "Washington" names(2) = "John" names(3) = "Adams" names(4) = "Thomas" names(5) = "Jefferson" names(6) = "James" names(7) = "Madison" names(8) = "James" names(9) = "Monroe"
In some instances, this array is better suited for post-processing, since each entry is a single word. Furthermore, the structure indicates the given name is always associated with an even number and the family name is always associated with an odd number. This doesn’t really have anything to do with VBA array filtering, but it’s a little tip you might find useful one day.
A Picky Match
This kind of “picky” matching makes the arrays a good candidates for string manipulation functions. You might need to cut off leading or trailing white space from values extracted from the spreadsheet before you try filtering your arrays. As any programmer knows, users type the darndest things!
The Optional Parameters
The Include Boolean
The first optional parameter is
[Include], a Boolean argument that defaults to TRUE. When Include is set to TRUE, the Match string must be found somewhere in the entry to be included in the output. Conversely, if Include is FALSE, the Match string must not appear in the output.
In other words, you use the
Include argument to tell the Filter function if you want to find all the values containing your string or find all the values not containing your string. The ability to find array elements that don’t contain a substring is actually a really powerful feature and it’s one I find myself using quite often.
With our extremely short example, we could use this rather silly code for finding the number of L1 (native) speakers of these languages:
Sub dubious_find_number_of_speakers() Dim langs(5) As Variant langs(0) = "English" langs(1) = 375 langs(2) = "Spanish" langs(3) = 442 langs(4) = "Chinese" langs(5) = 1299.5 output_arr = filter(langs, "n", False) End Sub
Notice the word
False in the third argument to the
Filter function. Since all three languages contain the letter
If we added German, Korean, French, Italian, Russian and Hindi, our output array would still only contain numbers!
But don’t get too arrogant. Let this be a lesson in deeply-thought-out, logical coding and not change-and-run-until-it-works coding. Once we include Portuguese, our
Side note: designing a logical structure, like language and number of speakers in alternating fashion, is invaluable for proper and clever coding. A simple piece of code that takes advantage of the alternating structure would be
For i = 0 To (UBound(langs) - 1) / 2 number_of_speakers(i) = langs(i * 2 + 1) Next i
this would yield the number of speakers every time, and in their Variant/Integer or Variant/Double-typed forms, as well. A similar logic could be used on the first and last names of the Presidents in the example above.
The Comparison Type
The last parameter, also optional, is
[Compare] as VbCompareMethod. You have three choices here:
The vbDatabaseCompare option is really only useful for Access, so we’ll focus on the first two.
The most important difference here is that vbBinaryCompare compares in a case-sensitive manner and it’s the default option. The vbTextCompare option only checks that the letters are the same (e.g. A = a). Thus
output_arr = filter(langs, "N", True, vbBinaryCompare) will give us nothing unless the language starts with a capital N, like Norwegian.
However, using vbTextCompare will give us English, Spanish, and Chinese, based on our original array, since vbTextCompare doesn’t care about the letter cases. It performs a case-insensitive comparison.
Numbers, of course, have no case, so if you are trying to use the Filter function to find certain numbers, it doesn’t matter. However, using the Filter function for finding numbers is not the best approach, since there is little flexibility in the function and it’s designed to operate on strings, not numbers.
The vbBinaryCompare option is an efficient way to check if a value is in an array, especially if you have an array of strings. It’s a pretty good alternative to our Is In Array VBA Function.
The Filter function is a nice way to filter an array of strings based on whether or not the elements of your array contain some substring. There’s not a lot of variability or flexibility for the VBA Filter function, and it doesn’t even allow you to do mathematical operations, but when you just need to get some matching strings, it is a quick, intuitive function to get the job done.
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.