Compile error in RegExp pattern with double quotes

Question

I want to search a string picked from .txt file for numbers enclosed in double quotes. I am doing all this with Excel macros. Example data:

"08134789316498"
"022"

My code:

Set oRegex1 = CreateObject("VBScript.RegExp")
oRegex1.Pattern = "(\"[0-9]+\"])"

But the above line is giving error:

"Compile Error: Expected end of statement"

Note: I have already added references to "Microsoft VBScript Regular Expressions 5.5" and "Microsoft VBScript Regular Expressions 1.0"

This isn't a question about the correct RegEx pattern so much as [how to use a double-quote within a quoted string in VBA](http://stackoverflow.com/questions/216616/how-to-create-strings-containing-double-quotes-in-excel-formulas/36169987#36169987). However, there is some ambiguity over whether the **+** is supposed to act as a string concatenation symbol or a string literal and whether the closing square bracket should be there at all. — , Apr 02 '17 at 16:42

MC ND · Answer 1 · 2017-04-02T15:27:48.143

5

You don't need to escape the double quotes from the regexp point of view, but from the vbscript/vba point of view.

oRegex1.Pattern = "(""[0-9]+""\])"
                    ^^      ^^   quotes escaped inside string by doubling them

edited Apr 02 '17 at 15:27

answered Apr 02 '17 at 14:51

MC ND

Should that trailing square bracket be included from the OP's sample? It doesn't seem to fit the sample data from the narrative. – Apr 02 '17 at 16:45
@Jeeped, The text in the question points to a *"no, it should not be present"*, or at least, as it is in the original sample code *"maybe it should be out of the parenthesis"*, but both assumptions deal with a different problem, *"regular expression does not match my data"*, not *"Compile error: Expected end of statement"*, so I decided to leave it as it was present in OP code. But I think that further information will probably lead to a different pattern. – MC ND Apr 02 '17 at 17:03

score 0 · Answer 2 · answered Apr 02 '17 at 16:38

0

It should be like this.

oRegex1.Pattern = "(""[0-9]+"")"

answered Apr 02 '17 at 16:38

2 Answers2