How to simulate lookbehind in VBA regex?

Question

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][-_].

In expression " a1" there are 2 or more characters, and the first two are not [Bb][-_], but you want .Test(" a1") to return False. Following your definition it should be True. Please explain am I missing something? — omegastripes
– omegastripes, Commented Mar 23, 2015 at 21:39
@omegastripes, the string should only have word characters, which is why I used \w in the regex pattern. — sigil
– sigil, Commented Mar 23, 2015 at 21:41
@femtoRgon, yes, my current workaround for this involves two regexes. I'm hoping to find a single-regex solution, as that will help with the extensibility of the code. — sigil
– sigil, Commented Mar 23, 2015 at 21:46

Colin · Accepted Answer · 2015-03-23 22:23:17Z

3

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.

edited Mar 23, 2015 at 22:23

answered Mar 23, 2015 at 21:48

Colin

4,16524 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sigil Over a year ago

While this will exclude "B_xxy", it will also exclude "B2xxy", which should pass. The only prefixes that should fail are B_,B-,b_,b-. I'm sorry if I didn't make that clear in the original question.

Colin Over a year ago

I adjusted the expression, though it's easiest to invert the way you think about the result such that "success" is a failure.

omegastripes · Accepted Answer · 2015-03-23 22:02:19Z

0

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

answered Mar 23, 2015 at 22:02

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Comments

Wiktor Stribiżew · Accepted Answer · 2015-03-23 22:31:13Z

0

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

answered Mar 23, 2015 at 22:31

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Collectives™ on Stack Overflow

How to simulate lookbehind in VBA regex?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related