VBA Regex substitution codes

Question

any experience with vba regex substition codes? I've tried the followings, which are working both on regex101.com and on regexr.com.

$&
\0

They are unfortunately not working in my VBA code. Any similar experience?

Example: https://regex101.com/r/5Fb0EV/1

VBA code:

    Dim MsgTxt As String
    ...

    strPattern = "(Metodo di pagamento).*\r\x07?.*"  
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
        MsgTxt = regEx.Replace(MsgTxt, "\0#END")
    End With

Input string:

Metodo di pagamento selezionato: 
Mastercard

Expected ouput:

Metodo di pagamento selezionato: 
Mastercard #END

https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — QHarr, Nov 24 '19 at 15:06
can't display here but each line has a chr(7) at the beginning — Andrew, Nov 24 '19 at 15:13

score 2 · Accepted Answer · answered Nov 24 '19 at 21:47

Try the below code:

Sub test()

    Dim MsgTxt As String

    MsgTxt = Chr(7) & "Metodo di pagamento selezionato:" & vbCr & Chr(7) & "Mastercard "
    With New RegExp
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "(Metodo di pagamento.*\r\x07?.*)"
        MsgTxt = .Replace(MsgTxt, "$1#END")
    End With
    Debug.Print MsgTxt

End Sub

Input

Metodo di pagamento selezionato:
Mastercard

Output

Metodo di pagamento selezionato:
Mastercard #END

VBA Regex substitution codes

1 Answers1