Since posting about filtering a listbox, I’ve been thinking a lot about VBA’s Like operator. I realized I could type in filters such as ‘[4-7]#‘ which matches two-digit numbers between 40 and 79. I raced to my computer to try it out, typed the opening bracket and was halted in my tracks:

It makes sense that a “[” without a “]” is untenable in a Like pattern. It needs the closing bracket to mark the end of the character set. On the other hand, a single “]” is legal and just stands for itself. You can match a left bracket by putting brackets around it, so the following expression in the Immediate Window yields True:

I set to creating a function that would detect the invalid left bracket. I tried testing whether there were more left than right brackets, but since you can have two left ones in a row, and as many right ones as you want, that wouldn’t fly. To avoid a complicated set of tests, I turned to the VBA standby of attempting something inside of On Error statements, something whose failure yields specific and helpful information, like “hey that’s an invalid pattern.”

Here was the first attempt, which seemed to do the trick:

You pass it a pattern and it tests it against the simplest possible string – “A”. My assumption was the test would either pass, if there was no left bracket, or fail if there was. I was wrong on two counts.

The first is that Like actually has three possible outcomes: True, False or Null. It returns Null if either the string being compared or the pattern is an empty string.

Much more important to my function, Like is lazy. It only tests as far as needed. So, while this returns an “Invalid pattern string” message:

This doesn’t:

It just returns False. I guess Like compares the first characters, sees they match and checks whether the next one is an asterisk, the only character that could match at this point. Since it’s not, Like says “whatever” and quits, ignoring the left bracket’s tenuous status.

I could fix the function – kind of – by using a really long string in place of “A” and force Like to look harder. Instead I surrounded LikePattern in asterisks:

This says there could be an infinite number of characters at the beginning of the pattern, so Like needs to be thorough and compare all the way through. I suppose this slows the code. I also guess I don’t need the “*” at the end. At any rate, I can now type a left bracket into the filter textbox without an error:

I updated the sample workbook from the ListBox Filter With WildCards post. It now calls the ValidLikePattern function at the beginning of the main loop. If the pattern isn’t valid, the loop is exited and the filtering stays the same until the next change in the filter textbox. Here’s the updated download.

Help Me Test Table Viewer

As I mentioned at the end of that post, I’m incorporating this logic and a bunch of features into a Table Viewer addin. I think it’s even more “freakin’ awesome” than MenuRighter. I’ve been using it every day. I just added the ability to specify exact matches, “contains”, “begins with”, etc. If you’d like to help me test it, please leave a comment or use the contact form to let me know. Or wait for my next post.