CODEWITHSUNDEEP
excelregexvba
denisse
denisse

Reputation: 23

VBA Regex - use \b and include "-" at the end of word boundary

I am not familiar with regex in VBA but have to debug a code written by someone else.

I understand that \b matches a position based on word boundaries and excludes non-word characters like -.

However, I need the code to match the - that are in some strings.

For example,

Match cats and dogs in the search_criteria: i have cats and dogs with the code:

"\b" & search_criteria & "\b"

works.

However, this doesn't come up with a match:

dogs - in the search_criteria: dogs - sleeping
doesn't work.

The search_criteria is a list that contains strings that don't end with - and strings that end with -.

Like this:

dogs -  
cats -   
cats and dogs  
houses  
cars  
work -

I basically have to change the \b at the end of the code but need help with this:

"\b" & search_criteria & "\b"

Upvotes: 2

Views: 979

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626870

It is a case when you need adaptive word boundaries (the term I prefer using). As all terms you have start with a word char, you can go on using \b as your left-hand boundary.

The right-hand boundary is either \b (if the last char is a word char) or no boundary check at all since you do not care if there is a word or a non-word char after -.

You can use a regex escape function like

Function RegexEscape(text As String) As String
Dim rx As New regExp
With rx
    .pattern = "[-\\^$*+?.()|[\]{}]"
    .Global = True
    .MultiLine = False
End With
RegexEscape = rx.Replace(text, "\$&")
End Function

and then use this with the adaptive word boundaries like

.pattern = "(?!\B\w)" & RegexEscape(search_criteria)  & "(?!\B\w)"

Legacy answer

You can also build the boundaries "manually":

Function BuildBoundaryPattern(pattern As String) As String
BuildBoundaryPattern = "\b" & pattern
If Right(pattern, 1) Like "[a-zA-Z0-9_]" Then
    BuildBoundaryPattern = BuildBoundaryPattern & "\b"
End If
End Function

This will build the right pattern for each check.

See a test sub:

Sub TestBBP()
Dim vbRegX As Object, vbRegXMatch As Object, pattern As String
Dim arr() As String: arr = Split("dogs -,cats -,cats and dogs,houses,cars,work -", ",")

For x = 0 To UBound(arr)
  Set vbRegX = New regExp
  vbRegX.pattern = BuildBoundaryPattern(arr(x))
  If vbRegX.Test(arr(x) & " sleeping") Then
    Debug.Print ("'" & arr(x) & "' in '" & arr(x) & " sleeping' matched!")
  End If
  If vbRegX.Test(arr(x) & "Sleeping") Then
    Debug.Print ("'" & arr(x) & "' in '" & arr(x) & " sleeping' matched!")
  End If
  Debug.Print ("------")
Next x
End Sub

Output:

'dogs -' in 'dogs - sleeping' matched!
'dogs -' in 'dogs - sleeping' matched!
------
'cats -' in 'cats - sleeping' matched!
'cats -' in 'cats - sleeping' matched!
------
'cats and dogs' in 'cats and dogs sleeping' matched!
------
'houses' in 'houses sleeping' matched!
------
'cars' in 'cars sleeping' matched!
------
'work -' in 'work - sleeping' matched!
'work -' in 'work - sleeping' matched!
------

Upvotes: 1

Related Questions