RegEx specific numeric pattern in Excel VBS

user_13015398 0
Viewed 59

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
vba excel regex
2 Answers

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
user_70080440

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.

user_97581940
Related