Excel VBA RegEx Replace Function Substituting a Literal $1 [duplicate]

Question

I often rely on the blunt ease of the Replace function in VBA to do simple string replacements, but I have long been attracted to the magical allure of regular expressions to perform more sophisticated string manipulations. But in my experimenting, I am simply stuck that my replacement value, "$1", is being returned as a literal part of the output string instead of as the text matched by the RegEx pattern. I assume whatever I am doing wrong is something ugly simple, but I can't see it. Can anyone provide some guidance?

I have included the Microsoft VBScript Regular Expressions 5.5 library as a reference in my VBA project. Here is a simplified snippet of my code:

Dim regEx As RegExp
Dim strInput As String
Dim strPattern As String
Dim strReplace As String ' I've tried type Variant also

strPattern = "/[a-z]" ' Find strings with a forward slash followed by a lowercase letter; this works
strReplace = "$1" ' I've also tried using this value directly in the Replace function without first assigning it to a string value.

With regEx
   .Global = True
   .MultiLine = True
   .IgnoreCase = False
   .Pattern = strPattern
End With

If regEx.Test(strInput) Then
   strInput = regEx.Replace(strInput, strPattern)
End If

If my input string is something like, "High/low Value", the result will be "High$1ow Value" when what I'm after is "High/Low Value". I'm stumped. Any thoughts?

The GridLock, using this appreoach, is there a straightforward way to make the replacement value uppercase? — Aylarja
– Aylarja, Commented Mar 17, 2020 at 19:06
I understand what you want. Do you have a link to show it in .net or another language? — Dang D. Khanh
– Dang D. Khanh, Commented Mar 17, 2020 at 19:15

SJR · Accepted Answer · 2020-03-17 17:55:05Z

1

Use "$1" if you are using a capture group in your pattern, which you are not.

This should work given the info provided, and will convert more than one instance of the pattern being matched.

Sub x()

Dim regEx As RegExp
Dim strInput As String
Dim strPattern As String
Dim strReplace As String ' I've tried type Variant also
Dim i As Long, f, s As String

Set regEx = New RegExp

strPattern = "/[a-z]" ' Find strings with a forward slash followed by a lowercase letter; this works
strInput = "High/low and Low/high"

With regEx
   .Global = True
   .MultiLine = True
   .IgnoreCase = False
   .Pattern = strPattern
    If .Test(strInput) Then
        s = strInput
        For i = 0 To .Execute(strInput).Count - 1
           f = .Execute(strInput)(i).FirstIndex
           s = Left(s, f) & UCase(.Execute(strInput)(i)) & Right(s, Len(s) - f - 2)
        Next i
        strInput = s
        MsgBox strInput
    End If
End With

End Sub

answered Mar 17, 2020 at 17:55

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

2 Comments

Aylarja Over a year ago

Thanks, SJR. I appreciate the guidance on capture groups, which was one part of my confusion. I had used parentheses earlier, but was simply getting an output string identical to my input string, unable to UCase the result. Your code works, which is very helpful and much appreciated. In the interest of learning more, Is there an alternate approach that would use capture groups and still produce the same result without requiring parsing the string? I understand some implementations of regex have a shorthand way of making the replace value uppercase, but VBA does not.

Ben Over a year ago

The only answer I needed was the $1 since I was using \1 which doesn't work in the VBA Regex engine.

Collectives™ on Stack Overflow

Excel VBA RegEx Replace Function Substituting a Literal $1 [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related