RegEx pattern selects the proper substring but throws error when running macro vba

Question

I am trying to remove everything after the comma , preceded by a [ (an open bracket) and ? (a question mark) in both strings with a regular expression.

I have input like:

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - How strongly do you value your relationship with [Field-2]?

and

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - During the Clinical Scholars Program, with [Field-2], have you partnered new project(s) other than your team's Wicked Problem Impact Project?

So I want to remove the ? in the first string and the following in the second string

, have you partnered new project(s) other than your team's Wicked Problem Impact Project?

I want to end up with

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - How strongly do you value your relationship with [Field-2]

and

Together, Let's End Their War: Promoting a Culture of Health among Veterans on the Gulf - During the Clinical Scholars Program, with [Field-2]

I have (?<=]),\s*([^,])+|\?

The pattern seems to be capturing what I want

but when I run my macro I get Method 'Replace' of object 'IRegEep2' failed

https://regex101.com/r/c9lDYD/1

I have run many other regex patterns with my macro with no issue so not sure what the problem is.

Sub findReplace()
Dim outArray As Variant
Dim regEx As New RegExp
Dim ws As Worksheet
Dim i As Long

  Dim strPattern As String: strPattern = "(?<=]),\s*([^,])+|\?"
  Dim strReplace As String: strReplace = ""
        
  With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
 End With
    
 Set ws = ThisWorkbook.Sheets("Helper_1Filted")

 With ws.Range("K1:K50")
    
      outArray = .value
      For i = 1 To UBound(outArray, 1)
          outArray(i, 1) = regEx.Replace(outArray(i, 1), strReplace)
      Next i
        
      .value = outArray
        
  End With
    
End Sub

Answer 1

I think the lookbehind is not supported in vba, but if the question mark should come after matching a comma and a part between square brackets you can use a capture group without an alternation |.

When using an alternation | the question mark will be matched anywhere in the string.

You might use a capture group and a negated character class [^

In the replacement use group 1 $1

(,[^\]\[,]*\[[^\]\[]*])[^?]*\?

• ( Capture group 1
  • , Match a comma
  • [^\]\[,]* Match 0+ times any char except a comma or [ or ]
  • \[[^\]\[]*] Match from [...]
• ) Close group 1
• [^?]* Match 0+ times any char except a question mark
• \? Match the question mark

Regex demo

Or a shorter version with an optional capture group:

(\])(?:,\s*[^,]+)?\?

Regex demo