Invalid Pattern String

Posted on November 30, 2010March 11, 2018 by Dick Kusleika

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

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 thoughts on “Invalid Pattern String”

Tushar Mehta says:

November 30, 2010 at 6:38 pm

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

November 30, 2010 at 10:21 pm

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.