Invalid Pattern String

I just came across an “Invalid Pattern String” error while use the Like keyword to fill a listbox. I thought Like was more robust than that. Here’s what msdn has to say about it.

I changed my code to this:

Instead of loading the matching sheets, I load “Invalid Pattern” when an error is encountered. I hope I don’t get any other errors.

I stumbled on this when I mistyped an open bracket ([) for a filter. It doesn’t like open brackets without its closing cousin. I did note, however, that "" Like "*[*", that is an empty string for the left hand side, does not produce an error, just False.

2 thoughts on “Invalid Pattern String

  1. I’m at a bit of a loss. What do you expect VB(A) to do when the pattern is *[*? The documentation requires that [, *, and ? be enclosed in square brackets. See http://msdn.microsoft.com/en-us/library/swf8kaxw.aspx

    As far as the empty string on the LHS goes, it would appear the Like code behaves differently if the LHS is a zero-length string.

    Should it? I’d say no. It should evaluate the pattern in a consistent manner. So, I’d consider this behavior a bug in Like. But, will Microsoft agree?

  2. The Empty String is an oddball character which does not behave like other characters. So when you did this…

    ? “” Like “*[*”

    I think VB reacted to the string being the Empty String and first looked to see if the pattern string contained any character constants… since it did, then the Empty String could never match it (a string of zero length will never match a string that is at least one character long) and so I think the underlying code that makes up the Like Operator probably failed the comparison right then and there without ever executing any of its “heavy-duty” underlying code. I would note, for comparison, that this returns True…

    ? “” Like “*”

    I believe you got the error that you did because you were trying to compare a string with length to the pattern “*[*” which probably required the code to begin executing its underlying “heavy-duty” code which noticed that you broke the syntax rules for the Like Operator by supplying an “Invalid Pattern” for it to use and, so, it raised an error. Going back to my opening statement… just to show you how odd the Empty String can be… it is found at every position in any text string. For example…

    InStr(1, “ABCDEFG”, “”) returns 1
    InStr(2, “ABCDEFG”, “”) returns 2
    InStr(3, “ABCDEFG”, “”) returns 3
    InStr(4, “ABCDEFG”, “”) returns 4
    etc.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.