Regex Function

[Robert Jones]

Hi People

I have searched a _lot_ for this solution, but I do not know how to do it in VBA. I want to be able to call a function in the following way:

MsgBox RegExp("abc123def456", "a(.+?)def(.+?)6", "$1Hello$2")

And for that to return: bc123Hello45

I have found many regex functions, but they very often either use fewer parameters, return $1 etc. literal in the result or appear at first to be what I am looking for but fail to work.

I would be really grateful if you could help.

Many thanks.

 

[Juan Pablo González]

Try this

Option Explicit

Function RegexReplace(ByVal text As String, ByVal pattern As String, ByVal replacement As String) As String
    Dim regex As Object     'VBScript_RegExp_55.RegExp
    
    Set regex = CreateObject("VBScript.RegExp")   'New VBScript_RegExp_55.RegExp
    
    With regex
        .pattern = pattern
        RegexReplace = .Replace(text, replacement)
    End With
    
    Set regex = Nothing
End Function

Sub TestIt()
    MsgBox RegexReplace("abc123def456", "a(.+?)def(.+?)6", "$1Hello$2")
End Sub

 

[Scott Huish]

A function for this is also included in the free add-on MoreFunc called REGEX.SUBSTITUTE available here: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

The help pages are here:
http://xcell05.free.fr/morefunc/english/