RegExpFind VBA does not return submatch (capture group), but entire match-CodePudding

In Excel, I have the VBA RegExpFind looking for this pattern:

Fields\("(\w ?)"\)\.Value =

Target: Fields("lmlEmployeeID").Value = oRs.Fields("lmpEmployeeID").Value

Formula: =RegExpFind([@Code], [@pat], 1, FALSE, 0 )

Result: Fields("lmlEmployeeID").Value =

Expected result: lmlEmployeeID

Though the pattern involves double-quotes and parentheses, as the pattern is a literal in a cell, I avoided excel character escaping problems - so I thought. Anyway those are outside of the capture group.

As the pattern succeeds, I don't know why the first grouping is not honored.

It may appear that I succeeded in matching the Function name shown, but that is a simple case not using RegExpFind.

I have already looked at these related questions:

EDIT2: The Add-In I have is different. It's probably out of date:

CodePudding user response：

You can use lookbehind and lookahead, which allow you to specify text before and after your desired match, without including them in the match.

New Pattern - using Lookbehind and Lookahead
```
(?<=Fields\(")(\w ?)(?="\)\.Value =)
```
Test string
```
Fields("lmlEmployeeID").Value = oRs.Fields("lmpEmployeeID").Value
```
Match
```
lmlEmployeeID
```
Try it out!

Important Note:

When setting the pattern in VBA, you'll need to escape the quotation marks otherwise it thinks you're closing and opening a string.
```
RegExpObject.Pattern = "(?<=Fields\("")(\w ?)(?=""\)\.Value =)"
```
CodePudding user response：

The problem was really the "wrong" Reg Exp Find function. Many out there have the same name with superficial differences in function name case. I uninstalled the one I had and installed the SEO Tools for Excel (the 15 day trial license). It works (with and without lookback/ahead). I would have thought that Microsoft would have native Reg Exp support for Excel by now.