Using the VBA Array Filter Function

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
  • The SourceArray Argument
  • The Match String
• Optional Parameters Discussion
  • The Include Boolean
  • The Comparison Type
• Conclusion

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.

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.

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, James Madison and James Monroe. On many occasions, though, we only want first or only last names. You can either attempt some head-spinning string manipulation or RegEx testing, or you can simply split all the names up in your data structure from the beginning, like this:

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

In our ish example earlier, if “ish” were used as the matching string, we got the two languages with “ish” in their names. However, if we added a space before the word, like “ ish”, we would end up with no matches.

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 n, they are excluded from our output array and we will only get the three numbers back from the Filter function.

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 output_arr contains the word “Portuguese” along with the numbers we were hoping to extract. This is because the string Portuguese does not contain the letter n. I suppose if we used “Brazilian Portuguese” we would be fine…

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:

1. vbBinaryCompare
2. vbTextCompare
3. vbDatabaseCompare.

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.

Conclusion

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.

You can’t use the VBA Array Filter function to return the position of an element in an array, but that’s okay. We have our own function for that.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.