Expected list separator or vba что это
Ошибка компиляции: ожидаемый разделитель списка или) Excel VBA
У меня есть эта командная строка в VBA:
Когда я удаляю цитаты, в VBA появляется ошибка:
Когда я добавляю цитаты, в VBA появляется ошибка:
Как я могу решить ошибку?
3 ответа
Наконец, позвольте мне порекомендовать заключить значение DPath в кавычки (буквально):
. в зависимости от различных значений ваших объединенных переменных
Пожалуйста, узнайте, как сделать вашу жизнь проще. То, что вы можете представлять «в строке VBA с помощью« », не означает, что вы должны это делать, за исключением самого тривиального случая.
Вот родной метод VBA для упрощения жизни.
Несмотря на то, что вышеупомянутое намного более многословно, оно, по крайней мере, позволяет вам использовать один шаг через F8, чтобы на каждом этапе проверять, правильно ли вы строите последнюю командную строку.
Как вы можете видеть выше, это немного многословно, поэтому многие другие языки программирования имеют возможность вставлять переменные и маркеры форматирования в строку. Это также можно сделать в VBA, используя модуль, чтобы скрыть соответствующий код. Пожалуйста, посмотрите на метод Fmt в моем модуле макета для VBA. Его можно найти в GitHub здесь. Это очень простой библиотечный модуль, позволяющий встраивать переменные поля и маркеры форматирования в строки
Это позволит вам написать
В гораздо более сжатой форме, которая также дружелюбна для человеческого глаза.
Заранее извиняюсь, если мне все-таки не удалось получить
Compile error: expected list separator or ) excel VBA
I have got this command line in VBA:
When I remove the quotations the error appears in VBA:
When I add the quotations the error appears in VBA:
How can I solve the error?
3 Answers 3
The quotation is wrong, you must put another pair of quotes around the javaw.exe path, because in VBA, a literal » must be specified as «» :
Finally, let me recommend to enclose the DPath value within (literal) quotation marks as well:
Please learn how to make your life easy. Just because you can represent a » in a VBA string using «»» doesn’t mean you should except in the most trivial instance.
Here is a native VBA method for making life simpler.
Whilst the above is much more verbose it at least allows you to use single stepping via F8 to check at each stage that you are constructing the final command string correctly.
As you can see the above is a bit verbose which is why many other programming languages have the ability to embed variable and formatting markers in a string. This can also be done in VBA using a Module to hide the relevant code. Please have a look at the Fmt method in my Layout Module for VBA. Its available in GitHub from here. Its a very simple library module to allow the embedding of variable fields and formatting markers in strings
This will allow you to write
In a much more concise way, which is also friendly to the human eye.
Apologies in advance if I’ve still managed to get the
VBA compile error if Instr function used with named parameter and return value assigned to variable
Background : In VBA, ‘InStrRev‘ function can be called without or with named parameters.
Concern : In VBA, the compiler returns «Expected: list separator» error if ‘InStr‘ function :
Its return value is assigned to a variable
Reference : VBA editor screenshot for ‘InstrRev‘ and ‘Instr‘ functions tool tips. Differences are highlighted in red.
Remark : ‘String1‘ & ‘String2‘ are optional arguments for ‘InStr‘ function according to above screenshot tooltip square brackets. However, they are required, as mentioned in below answer and in Visual Basic language reference : https://msdn.microsoft.com/EN-US/library/office/gg264811.aspx
2 Answers 2
InStr is overloaded by way of Variant parameters
In the following 4 examples, x is always assigned the value 4
The Function signature behaves as it is defined:
Function InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])
The Function signature behaves as though it was defined like:
Using Named Parameters
But as you’ve discovered, the InStr function suffers from Syntax and/or Compilation errors when using named parameters:
Syntax Error: Expected List Separator
When all of the parameters are named:
Syntax Error: Expected List Separator
Compile error: Object doesn’t support named arguments
When some of the parameters are named:
Compile error: Object doesn’t support named arguments
Same errors with StrComp function
The StrComp function doesn’t appear to have any overloaded-type functionality, but it has the same problems with Syntax and Compilation errors:
So, what do InStr and StrComp have in common that is different to InStrRev and seemingly all other VBA functions?
Well, InStr and StrComp both share these features:
I can’t find any other functions in the VBA library that share those characteristics, so I suspect there’s a compilation bug related to those characteristics.
Qualifying the function fixes the problem.
Both InStrRev and StrComp can be used with all/some named parameters, if the function is qualified by the library name or module name:
Specifically its odd because VBA disallows this; the rule there is that non-optional arguments cannot follow optional arguments, which makes sense as there would be cases when the compiler could not match up the arguments to what the function expected. This also forces all of its arguments to be variants.
Curiously its fully qualified name
does parse, but produces an error at runtime unless Start is provided:
which works as expected.
One reason the Call form may appear to work is thats its a no-op and may be optimised away.
VBA.X() vs X()
This is perfectly fine:
This generates a parse time Expected Expression error:
Expected list separator or
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.
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.
Equal number of open and closed parentheses.
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.
Expected list separator or
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 will highlight 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 I 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, as follows:
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 parentheses. But one fact is always true: 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 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 troublesome line above after counting open and closed parentheses. After you add 1 for each open parenthesis and subtract one for each closing parenthesis, you end up with 1. That shows that you either have one too many open parentheses or you’re lacking one closed parenthesis.
Counting open/closed parentheses in a statement.
PCase = «Mc» & Ucase(Mid(PCase, 3, 1) & Mid(PCase, 4)_
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.
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 is with the UCase() function. That larger Mid(PCase, 3, 1) function is actually 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.
Equal number of open/closed parentheses.
Equal number of open/closed parentheses.
Regardless of what 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 learn the correct syntax for whatever you’re trying to accomplish.