CODEWITHSUNDEEP
arraysregexexcelvba
JvdV
JvdV

Reputation: 75850

Case-insensitive Regular Expression - VBA

Background:

Just now, I was answering a question and was playing around with RegEx within VBA. The goal is to create a list of names that exist within a string. RegEx was the go-to solution since we want to prevent VBA to stumble over punctuation marks and substrings that look similar e.g.: Jack or Jacky.

Sample Data:

Let me give a simple sample. Imagine we have a string like:

Dim str As String: str = "Jack's turn, Becky's or Frank?"

we want to know which names in a certain array are mentioned within the string, for example:

Dim arr As Variant: arr = Array("Jack", "Frank")

Sample Code:

To prevent an iteration over the array, I went with the following code:

Sub Test()

Dim str As String: str = "Jack's turn, Becky's or Frank?"
Dim arr As Variant: arr = Array("Jack", "Frank", "Beth")
Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")

regex.Pattern = "\b(" & Join(arr, "|") & ")\b"
regex.Global = True

Set hits = regex.Execute(str)
For Each hit In hits
    Debug.Print hit
Next hit

End Sub

Problem:

Whereas the above would neatly return the two hits, it would not work case-insensitive. For example, changing the following line will only return Jack:

Dim str As String: str = "Jack's turn, Becky's or frank?"

I thought I could counter that by turning off case-sensitivity using (?i):

regex.Pattern = "(?i)\b(" & Join(arr, "|") & ")\b"

But the problem is that this would work perfectly for most languages (test here), however VBA seems to have a problem with it and generates an Error 5017 upon execution.

Question:

Does anybody know why? Is this not supported within VBA or is my syntax wrong? If not supported, what is the alternative to get hits case-insensitive while retaining the possibility to Join the array of names?

Bonus-Question:

Ultimately I would like to Join the Hits together through a delimiter, for example like:

Debug.Print Join(regex.Execute(str),", ")

However, I realized execution returns a collection and needs iteration first which I would like to avoid.

Upvotes: 7

Views: 4422

Answers (1)

QHarr
QHarr

Reputation: 84465

Set the property on the RegExp object i.e.

regex.ignorecase = True

RegExp object

The RegExp object has three properties that affect how regular expressions are applied:

IgnoreCase: I trust this is self-explanatory

Global: Determines whether or not to find all possible matches in the input string. If Global is set to false, only the first match will be found or replaced, as applicable.

MultiLine: Determines whether matches can span accross line breaks.

See also: https://learn.microsoft.com/en-us/previous-versions//1400241x%28v%3dvs.85%29

And from https://www.regular-expressions.info/vbscript.html regarding VBScript’s Regular Expression Support as we are using Microsoft VBScript Regular Expressions 5.5

No mode modifiers to set matching options within the regular expression.

Upvotes: 14

Related Questions