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:

Private Sub LoadSheets(Optional sFilter As String)

Dim sh As Worksheet

On Error GoTo ErrHandler


For Each sh In ThisWorkbook.Sheets
If UCase(sh.Name) Like UCase(" * " & sFilter & " * ") Then
Me.lbxSheets.AddItem sh.Name
End If
Next sh

Exit Sub

Me.lbxSheets.AddItem "Invalid Pattern"
Resume ErrExit

End Sub

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

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see