VBA Compile error: Expected end of statement

Question

I am trying to code a combo box in a form such that it automatically filters the options as the user types. I have a code but I keep getting a compile error. Here is my code:

Private Sub UTIL_FCLTY_ID_Change()
    If Nz(Me.UTIL_FCLTY_ID.Text) = “” Then
        Me.Form.Filter = “”
        Me.FilterOn = False
    ElseIf Me.UTIL_FCLTY_ID.ListIndex <> -1 Then
        Me.Form.Filter = “[UTILITY FACILITY TYPE NAME:] = ‘” & _
                 Replace(Me.UTIL_FCLTY_ID.Text, “‘”, “””) & “‘”
        Me.FilterOn = True
    Else
        Me.Form.Filter = “[UTILITY FACILITY TYPE NAME:] Like ‘*” & _
                 Replace(Me.UTIL_FCLTY_ID.Text, “‘”, “””) & “*'”
        Me.FilterOn = True

    End If

    Me.UTIL_FCLTY_ID.SetFocus
    Me.UTIL_FCLTY_ID.SelStart = Len(Me.UTIL_FCLTY_ID.Text)

End Sub

That code sample has typesetting quotes (“”) instead of plain quotes (""). If your real code also includes typesetting quotes, you should change them to plain quotes. — HansUp
– HansUp, Commented Jan 3, 2015 at 23:53
Which line of the code triggers the compile error? Is it the line which ends with & “*'”? That looks like a quote problem ... notice how it affects the syntax coloring at that point. — HansUp
– HansUp, Commented Jan 3, 2015 at 23:54
There are two specific errors now. 1. In the Else if the error is Expected: list separator or ) --> This one highlights the "'" at the end of the statement. 2. In the Else if the error is Expected: expression --> This one highlights the apostrophe in "*'" — user4403907
– user4403907, Commented Jan 4, 2015 at 0:04

HansUp · Accepted Answer · 2015-01-04 19:01:41Z

As @HansUp mentioned, you use non-standard quotes and non-standard apostrophe. Replace them with correct one. Second issue is with this statement: """. VBA compiler sees it as "" " (empty string and unclosed quote). You need to use double quote inside quote to workaround it: """""

Finally your procedure should look like:

Private Sub UTIL_FCLTY_ID_Change()
    If Nz(Me.UTIL_FCLTY_ID.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
        Exit Sub 'there's nothing to proceed ;)
    End If

    If Me.UTIL_FCLTY_ID.ListIndex <> -1 Then
        Me.Form.Filter = "[UTILITY FACILITY TYPE NAME:] = '" & _
                 Replace(Me.UTIL_FCLTY_ID.Text, "'", """") & "'"
        Me.FilterOn = True
    Else
        Me.Form.Filter = "[UTILITY FACILITY TYPE NAME:] Like '*" & _
                 Replace(Me.UTIL_FCLTY_ID.Text, "'", """") & "*'"
        Me.FilterOn = True
    End If

    Me.UTIL_FCLTY_ID.SetFocus
    Me.UTIL_FCLTY_ID.SelStart = Len(Me.UTIL_FCLTY_ID.Text)

End Sub

Collectives™ on Stack Overflow

VBA Compile error: Expected end of statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related