CODEWITHSUNDEEP
regexvbaexcel
sigil
sigil

Reputation: 9546

How to simulate lookbehind in VBA regex?

I'm trying to build a regex pattern that will return False if a string starts with certain characters or contains non-word characters, but because VBA's RegExp object doesn't support lookbehind, I am finding this difficult. The only word character prefixes that should fail are B_, B-, b_, b-.

This is my test code:

Sub testregex()

Dim re As New RegExp

re.pattern = "^[^Bb][^_-]\w+$"

Debug.Print re.Test("a24")
Debug.Print re.Test("a")
Debug.Print re.Test("B_")
Debug.Print re.Test(" a1")


End Sub

I want this to return:

True
True
False
False

but instead it returns

True
False
False
True

The problem is that the pattern looks for a character that's not in [Bb], followed by a character that's not in [-_], followed by a sequence of word characters, but what I want is just one or more word characters, such that if there are 2 or more characters then the first two are not [Bb][-_].

Upvotes: 1

Views: 1098

Answers (3)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

You can get your matches with

regEx.Pattern = "^[^ bB][^_ -]*\w*$"
regEx.MultiLine = True
Debug.Print regEx.Test("a24")
Debug.Print regEx.Test("a")
Debug.Print regEx.Test("B_")
Debug.Print regEx.Test(" a1")

Output:

True
True
False
False

Upvotes: 0

Colin
Colin

Reputation: 4135

Try matching this expression:

^([Bb][\-_]\w*)|(\w*[^\w]+\w*)$

...which will match "B_", "b_", "B-" and "b-" or anything that's not a word character. Consider a successful match a "failure" and only allow non-matches to be valid.

Upvotes: 3

omegastripes
omegastripes

Reputation: 12602

re.Pattern = "^(?:[^ Bb][^ _-]\w+|[^ Bb][^ _-]|[^ Bb])$"

Upvotes: 0

Related Questions