Find and replace all instances of a pattern in a string using Regular Expressions

Question

Using Excel VBA, I'm trying to replace all instances of a simple pattern that looks like this:

{some text}

with some other constant string. So I want to find all the text that is enclosed in curly braces and replace is (with the curly braces) with another string.

I use the following code:

Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\{.*?\}"
qtext = regEx.Replace(qtext, html_input)

where qtext and html_input are some strings. But this only replaces the first instance of the pattern.

For example:

qtext = "yadda yadda {1:NM:=12.000:0.120} omtty doom {1:NM:=6/6} loppy loop"
html_input = "I am HTML"

And the result should be:

"yadda yadda I am HTML omtty doom I am HTML loppy loop"

But what I get is:

"yadda yadda I am HTML omtty doom {1:NM:=6/6} loppy loop"

What am I missing?

@SJR you should post it as answer, so future users can find the answer easily — Shai Rado, Jul 04 '17 at 12:45

score 12 · Accepted Answer · answered Jul 04 '17 at 13:17

As @SJR said in their comment, you need to set the Global property of the regex object to True. The property is described on MSDN:

Global - A Boolean property that indicates if the regular expression should be tested against all possible matches in a string. By default, Global is set to False.

So in your code becomes:

Option Explicit

Sub ReplaceText()

    Dim regEx As Object
    Dim qtext As String
    Dim html_input As String

    ' set up regex
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "\{.*?\}"
    regEx.Global = True '<-- set flag to true to replace all occurences of match

    ' input and replacement text
    qtext = "yadda yadda {1:NM:=12.000:0.120} omtty doom {1:NM:=6/6} loppy loop"
    html_input = "I am HTML"

    ' do replace
    qtext = regEx.Replace(qtext, html_input)

    ' test output
    MsgBox qtext

End Sub

Find and replace all instances of a pattern in a string using Regular Expressions

1 Answers1