Yesterday, Dick showed how to limit a ListBox by using the Change event of a TextBox.
Well, there’s another way to do the same thing, using the Filter function, available in VB6 (meaning from Excel 2000 and above).
So, using the same userform, I put all the Northwind customers in column A. Then, I put the following code in the userform module:
Private Sub tbxFind_Change()
Dim vList As Variant
‘Read the original list
vList = Range(“A2”, Cells(Rows.Count, 1).End(xlUp)).Value
‘Convert it to a 1D array
vList = Application.Transpose(vList)
‘Filter it
vList = Filter(SourceArray:=vList, _
Match:=tbxFind.Value, _
Include:=True, _
Compare:=vbTextCompare)
‘Send it to the listbox
lbxCustomers.List = vList
End Sub
Private Sub UserForm_Initialize()
‘Read the original list
lbxCustomers.List = Range(“A2”, Cells(Rows.Count, 1).End(xlUp)).Value
End Sub
The Filter function works like this:
Filter(sourcesrray, match[, include[, compare]])
It requires a sourcearray, which is a one dimensional array (which is the only issue with the function, you can’t filter more “complex” arrays), and a string, ‘match’. It will search all the items in the array and return those that include that string.
Note that it doesn’t work the same way as the ‘Like’ operator, because it doesn’t use any wildcards.
You can make it *exclude* the items that have the match string by changing the ‘Include’ parameter (it assumes a value of True by default).
The last argument, ‘Compare’, enables you to do a binary comparison or text comparison of the texts, or use the value set in the Option Compare statement. In this case, I force it to text comparison, so ‘AAA’ equals ‘aaa’.
This method should work much faster than comparing each item in the array one by one.
good tip; the filter function can be VERY handy. Quick question — is there a way to cause filter to match whole values? for example, if i have
aryA = {the, then}
aryF = Filter(aryA, “the”),
then
aryF = {“the”, “then”}.
I want aryF to return only the first match.
thanks.ben.
This example might be of some use if you want to do the filtering without VBA or array formulae: a spreadsheet up on my site at http://www.j-paine.org/excelsior/repository/remove_non_matches/remove_non_matches.xls.
Column A contains a yellow table of strings; only those starting with “X” are to be put into the yellow table in column C.
Gaps are closed up, so that, for example, the second string placed in column C is one cell below the first.
There’s an intermediate table in column B, in which I calculate the positions in column A of the strings to be placed in column C. This “index table” uses INDEX and OFFSET. Essentially, its first element is the position of the first string we want to keep. Its second element is the position of the first string beyond that. And so on. The calculation is complicated by the need to handle non-matches and boundary conditions, but the basic idea is fairly simple.
I generated the spreadsheet using Excelsior. It’s explained, with code, in http://www.j-paine.org/excelsior/repository/remove_non_matches/index.html. The key is to think in terms of recursing over tables, as though they were functions in a “normal” programming language.
Regards
Jocelyn Paine