RegEx specific numeric pattern in Excel VBS

Question

I do not have much RegEx experience and need advice to create a specific Pattern in Excel VBA.

The Pattern I want to match on to validate a Userform field is: nnnnnn.nnn.nn where n is a 0-9 digit.

My code looks like this but Reg.Test always returns false.

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Pattern = "/d/d/d/d/d/d\./d/d/d\./d/d"
    End With
    If RegEx.Test(txtProjectNumber.Value) = False Then
        txtProjectNumber.SetFocus
        bolAllDataOK = False
    End If

Did you succeed Steve? If so, please accept/and or upvote any of the answers below that solved the issue. — JvdV
– JvdV, Commented Apr 28, 2020 at 6:23

SJR · Accepted Answer · 2020-03-12 21:02:58Z

3

Try this. You need to match the whole contents of the textbox (I assume) so use anchors (^ and $).

Your slashes were the wrong way round. Also you can use quantifiers to simplify the pattern.

Private Sub CommandButton1_Click()
  Dim RegEx As Object, bolAllDataOK As Boolean
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Pattern = "^\d{6}\.\d{3}\.\d{2}$"
    End With
    If Not RegEx.Test(txtProjectNumber.Value) Then
        txtProjectNumber.SetFocus
        bolAllDataOK = False
    End If
End Sub

answered Mar 12, 2020 at 21:02

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

Comments

JvdV · Accepted Answer · 2020-03-12 21:56:46Z

3

VBA got it's own build-in alternative called Like operator. So besides the fact you made an error with forward slashes instead of backslashes (as @SJR rightfully mentioned), you should have a look at something like:

If txtProjectNumber.Value Like "######.###.##" Then

Where # stands for any single digit (0–9). Though not as versatile as using regular expressions, it seems to do the trick for you. That way you won't have to use any external reference nor extra object.

edited Mar 12, 2020 at 21:56

answered Mar 12, 2020 at 21:05

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

3 Comments

SJR Over a year ago

Yep, much simpler.

Steve Montague Over a year ago

Thanks for the Help!

JvdV Over a year ago

@stevemontague. You are welcome. Please consider to choose an answer to accept by clicking the checkmark to the left of the answer. Either one is fine, but that way you'll be closing the question.

Collectives™ on Stack Overflow

RegEx specific numeric pattern in Excel VBS

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related