Excel VBA RegEx Replace Function Substituting a Literal $1

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?

Answer 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