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

Tenorrhaphy

I assume you're using the RegExp object? – Sluice

Answer 1

VBA offers lookahead (both positive and negative) 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:

• It skips any words starting with a or A (case-insensitivity).
• For all words not starting with a/A it returns everything from the second character on. This is achieved by using a submatch (the pattern inside ( and ) is the first submatch).

To understand the complete pattern, you may refer to the explanation on regex101.

    Sub TestString()
       MsgBox ReducedText("cfat dcat")
       ' results in:        fat  cat
       MsgBox ReducedText("Sat all over the hat again")
       ' results in:        at      ver  he  at
    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

Ustulation

Answer 2

How about putting the ^A in a non-captured group and using the SubMatches property of the Match object to get your matched value?

Amortization

That sounds interesting. How does one use submatches? – Tenorrhaphy