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

user_17020120 0
Viewed 212

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"
vba excel regex
1 Answers

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 -.

So, all you need is

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!
------
user_38329700
Related