Cannot find why my VBA simple regex command isn't working

Question

When using this line in Excel VBA:

Cells(a, 20).Value = regexProjet.Execute(Cells(a, 1).Value)(0)

I get a warning saying that either the parameter or the command is invalid.

I have use this line in many places in my code and it worked fine, the format of the cells are all standard (they're strings...).

Anyone could give me some hints of what to look for?

FYI this is how I declared the regex:

Dim regexProjet As Object
Set regexProjet = CreateObject("VBScript.RegExp")
regexProjet.IgnoreCase = True
regexProjet.Pattern = "^   ([a-z]+)(-)([0-9]+)" 'conserve seulement la clé du projet

If it worked before and you copied the line, then have you set the reference to the RegEx in the application - Tools >> References? — Tak, Aug 15 '14 at 13:40
Refer to [How to use regular expression](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). The link will show answer with steps — Tak, Aug 15 '14 at 14:01
The line does not look right so maybe you can try `Cells(a, 20).Value = regexProjet.Execute(Cells(a, 1).Value)`. — Tak, Aug 15 '14 at 14:18
You should not need to enable the reference if you're using late binding (you **are** using late binding). — David Zemens, Aug 15 '14 at 15:09
@user3779627 You are getting the warning because your regex does not match. Ignore the comments about setting references since you are using late binding. Using the technique you are showing in your code, you need to "test" the regex first, and do something else if it doesn't match. See my answer below. — Ron Rosenfeld, Aug 16 '14 at 01:02

Ron Rosenfeld · Accepted Answer · 2014-08-16T01:14:30.007

You will get that response if your regex does not match your data. To avoid it, using the technique you are using, first do a test to see if your regex matches your string.

e.g:

If regexProjet.test(Cells(a,1).value) then
    Cells(a, 20).Value = regexProjet.Execute(Cells(a, 1).Value)(0)
Else
   ... your error routine
End If

Also, you should note that if you are just trying to match the overall pattern, there is no need for the capturing groups (and they will add execution time, making the regex less efficient).

score 0 · Answer 2 · answered Aug 15 '14 at 15:00

Here is sample code I got to run in Excel 2013 but it's slightly different than what you have. I got the code from [How to regular expressions]: http://support.microsoft.com/kb/818802

Sub testreg()

   Dim regexProjet As New RegExp
Dim objMatch As Match
Dim colMatches   As MatchCollection
Dim RetStr As String

RetStr = ""

regexProjet.IgnoreCase = True
regexProjet.Pattern = "^   ([a-z]+)(-)([0-9]+)"


Set colMatches = regexProjet.Execute(Cells(1, 1).Value)

For Each objMatch In colMatches   ' Iterate Matches collection.
  RetStr = RetStr & " " & objMatch.Value
Next

Cells(1, 20).Value = RetStr

End Sub

Cannot find why my VBA simple regex command isn't working

2 Answers2