The Expected: list separator or ) error message tells you that the compiler was expecting to find either a list separator (such as the comma that separates arguments in a function) or a closing parenthesis in the statement. In most cases, it highlights where the problem began. For example, the following statement, when compiled, generates an Expected: list separator or ) error message with the word World highlighted:
Answer = MsgBox(Hello World, vbInformation,"Test")
The problem with the preceding line is that the words Hello World are supposed to be a string literal enclosed in quotation marks, but we forgot the quotation marks. The blank space between the words Hello and World has sent the compiler into a tizzy because it was expecting something else there. To correct the problem, put the quotation marks around the string literal:
Answer = MsgBox("Hello World", vbInformation,"Test")
With the quotation marks in place, the compiler can see that the entire string of text "Hello World" is the first argument, vblnformation is the second argument, and "Test" is the third argument.
Sometimes the Expected: list separator or ) error message points out a missing parenthesis in a statement. For example, the following statement generates such an error message when compiled:
PCase = "Mc" & UCase(Mid(PCase, 3, 1) & Mid(PCase, 4)
It's rarely easy to see where a parenthesis needs to be added to a statement, especially if the statement contains lots of them. One fact is always true, though: Any statement that uses open parentheses must also use an equal number of closed parentheses.
Here's a little trick that programmers use to see whether they have the right number of parentheses. You start with the number 0 in mind. Then you read from left to right. Each time you encounter an open parenthesis, add 1 to that 0. Each time you come to a closed parenthesis, subtract 1 from that number. By the time you get to the end of the line, you should be back to 0. If you end up at any other number, you have a problem.
As an example, Figure 12-7 shows the preceding troublesome line after counting open and closed parentheses. After you add 1 for each open parenthesis and subtract 1 for each closing parenthesis, you end up with 1. That number shows that you either have one too many open parentheses or you're lacking one closed parenthesis.
Needless to say, you can't just stick an extra closing parenthesis into the statement at random. Rather, you need to understand the syntax rules of the various functions used in the expression. The example in Figure 12-7 uses two functions named UCase() and Mid(). Each function needs its own, complete pair of parentheses.
Counting open and closed parentheses in a statement.
PCase - "Mc" & Ucase(Mid(PCase, 3, 1) & Mid(PCase, 4)_
The Mid(PCase, 4) function at the end of the statement is fine because the Mid() function requires exactly one open and one closed parenthesis. The larger Mid() function, Mid(PCase, 3, 1), is also okay because it has one open and one closed parenthesis.
The problem occurs with the UCase() function. That larger Mid(PCase, 3, 1) function is the argument for the UCase() function, and there's no closing parenthesis for UCase(). That needs to be added right after the closing parenthesis for Mid(). Each of the Mid() functions also has a pair of open and closed parentheses. If you count the parentheses in the modified statement shown in Figure 12-8, the count ends up at 0, which is exactly what you want.
Regardless of which compile error message you get, you have to fix the problem before you can even run the procedure. Don't expect the compile error message to pinpoint the solution for you. The message in a compile error is often too vague and too general for that. In most cases, your only recourse is to look up the correct syntax in Help (or through the Object Browser) and apply it to whatever you're trying to accomplish.
Was this article helpful?