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
   
    Me.lbxSheets.Clear
   
    For Each sh In ThisWorkbook.Sheets
        If UCase(sh.Name) Like UCase(" * " & sFilter & " * ") Then
            Me.lbxSheets.AddItem sh.Name
        End If
    Next sh
   
ErrExit:
    Exit Sub
   
ErrHandler:
    Me.lbxSheets.Clear
    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 Comments

  1. Tushar Mehta says:

    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. Rick Rothstein (MVP - Excel) says:

    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 or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: