0

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.

screenshot of Excel using RegExpFind VBA

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:

  1. RegExp VBA : reluctant quantifier does not return the submatch value

  2. match doesn't return capturing group

  3. Regex doesn't omit quotes when matching text in quotes

  4. VBA: Submatching regex

  5. How to match, but not capture, part of a regex?

EDIT: when applying @toddlson's suggestion, there is a #VALUE! error: "a value of the formula is of the wrong datatype"

#VALUE! error

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

Joseph Shirk
  • 45
  • 5
  • Are you using https://seotoolsforexcel.com/regexpfind/? If yes, it is said the third argument is the group id, and if you pass `1`, it should work. – Wiktor Stribiżew Dec 07 '21 at 16:01
  • @WiktorStribiżew : You may have touched on the problem. There are two functions I imported, different in case only. When I begin entering the function, autocomplete offers only RegExpFind. When I open that in VBA, it says in the comments 'written by Patrick G. Matthews' However, if I'm not mistaken, I have tried in the past using regexpfind (in lower case). Now I have doubts about which is being called. – Joseph Shirk Dec 07 '21 at 16:14
  • I think I will have to remove the module (Regular-Expressions-Examples.xlam) and add the seotools version. Looking at their demo video, the autocomplete shows "RegexpFind" - which is different from my RegExpFind. – Joseph Shirk Dec 07 '21 at 16:50

2 Answers2

0

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 =)"
Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • When I try this pattern without modifying the formula, I get #VALUE! error. The evaluation steps show that the first parameter is of the wrong datatype. I can't put a screenshot here, so I will modify the question to include it. – Joseph Shirk Dec 07 '21 at 16:27
  • @JosephShirk I added a link to a Regex testing tool, that shows that the pattern works. – Toddleson Dec 07 '21 at 16:31
  • It looks like I am using a different RegExp function by Patrick G. Matthews; its interface is Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _ Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _ Optional MultiLine As Boolean = False) In the code I don't see a mention of lookbehind or lookahead, but the object it references is RegX, so maybe it is supported. Why the LookIn string is evaluated as the wrong datatype is a mystery to me. All I changed was the pattern as you suggested. Weird. – Joseph Shirk Dec 07 '21 at 16:45
  • With that function, you would insert my pattern as an argument like `RegExpFind( yourString, "(?<=Fields\("")(\w+?)(?=""\)\.Value =)")`. I don't see why it wouldn't work. Lookbehind and Lookahead have been part of Regex for many versions now. – Toddleson Dec 07 '21 at 16:59
0

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.

Joseph Shirk
  • 45
  • 5
  • Excel does have access to the VBScript.RegExp object. You can add a reference to Microsoft VBScript Regular Expressions 5.5 in your project or you can late-bind `CreateObject("VBScript.RegExp")`. The object works well, and has been a part of many of my personal projects. – Toddleson Dec 07 '21 at 17:19
  • @Toddleson I would probably love to have native, and included support, but by mentioning 'project' and 'late-bind,' it sounds like I have to write a function, or find a well-written one. I suppose if Excel users had to write code to use VLOOKUP, far fewer people would be using it to its potential. Is there a site you would indicate for me to learn more? – Joseph Shirk Dec 07 '21 at 18:28
  • I actually got all the way thru this https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops [84 by SAm] - including the Personal.xlsb - but in the end, I got the dreaded #NAME? If I can't do it, how many others can't? – Joseph Shirk Dec 07 '21 at 19:14