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
– Tak, Commented Aug 15, 2014 at 13:40
Refer to How to use regular expression. The link will show answer with steps — Tak
– Tak, Commented Aug 15, 2014 at 14:01
You should not need to enable the reference if you're using late binding (you are using late binding). — David Zemens
– David Zemens, Commented Aug 15, 2014 at 15:09

Ron Rosenfeld · Accepted Answer · 2014-08-16 01:14:30Z

2

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).

edited Aug 16, 2014 at 1:14

answered Aug 16, 2014 at 1:00

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tak · Accepted Answer · 2014-08-15 15:00:23Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related