Issue with VBA array Filter function: only first result is returned

Question

I am running into some issues while using VBA its 'Filter' function. I am trying to use multiple subsequent filters to step by step reduce the array down the contain the value I need. It however seems that only the first element that matches the 'Match' value is returned and not the subsequent ones?

To given an example, when running the code below, the second 'debug call' return '1' but it should return '2'

Function FilterAnArray()

Dim names As Variant
names = Array("Ann Smith", "Barry Jones", "John Smith", "Stephen Brown", "Wilfred Cross")
Debug.Print UBound(names)

Dim smithNames As Variant
smithNames = Filter(names, "Smith")
Debug.Print UBound(smithNames)

End Function

Version information e.t.c.

I am running Excel 2016, version 16.0.8730.2046 - 64-bit. Any help with this issue is much appreciated!

Answer 1

It is working quite ok. It is returning 1, because you have two Smith's:

And the Upper bound of smithNames is 1, because it arrays are 0-based. If you want the count of the array elements, and you do not like the UBound+1, you may use a worksheet function:

Debug.Print WorksheetFunction.CountA(smithNames)

---

Strongly not adviseable option:

You may consider writing Option Base 1 on the top of the module. Then the arrays will be 1-based and will be the way you expect it. In your example you see why Option Base 1 is not advisable. If you have it, Names will be 1 based and smithNames will be 0 based. This is because of the different way the arrays are assigned.