Lookbehind on regex for VBA?

Question

Is there a way to do negative and positive lookbehind in VBA regex?

I want to not match if the string starts with "A", so I am currently doing ^A at the start of the pattern, then removing the first character of match(0). Obviously not the best method!

I am using the regExp object.

Answer 1

VBA offers positive and negative lookaheads but rather inconsistently not lookbehind.

The best example of using Regex with VBA that I have seen is this article by Patrick Matthews

[Updated example using Execute rather than Replace]

While I am not completely clear on your usage you could use a function like this with

• skips any words starting with A

• for all words not starting with a it returns everything from the second character on (using a submatch - the pattern inside ( and ) is submatch 1 -

  Sub TestString()
  MsgBox ReducedText("cfat dcat")
  MsgBox ReducedText("Sat all over the hat again")
  End Sub
  
  
  Function ReducedText(strIn As String) As String
  Dim objRegex As Object
  Dim objRegMC As Object
  Dim objRegM As Object
  Dim strOut As String
  Set objRegex = CreateObject("vbscript.regexp")
  With objRegex
    .IgnoreCase = True
    'not needed if matching the whole string
    .Global = True
    .Pattern = "\b[^a\s]([a-z]+)"
    If .test(strIn) Then
        Set objRegMC = .Execute(strIn)
        For Each objRegM In objRegMC
          strOut = strOut & objRegM.submatches(0) & vbNewLine
        Next
        ReducedText = strOut
    Else
      ReducedText = "Starts with A"
    End If
  End With
  End Function