VBA regexp to check special symbols

Question

I have tried to use what I've learnt in this post, and now I want to compose a RegExp which checks whether a string contains digits and commas. For example, "1,2,55,2" should be ok, whereas "a,2,55,2" or "1.2,55,2" should fail test. My code:

Private Function testRegExp(str, pattern) As Boolean
    Dim regEx As New RegExp

    If pattern <> "" Then
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = pattern
        End With

        If regEx.Test(str) Then
            testRegExp = True
        Else
            testRegExp = False
        End If
    Else
        testRegExp = True
    End If
End Function

Public Sub foo()
    MsgBox testRegExp("2.d", "[0-9]+")
End Sub

MsgBox yields true instead of false. What's the problem ?

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

Your regex matches a partial string, it matches a digit in all 55,2, a,2,55,2, 1.2,55,2 input strings.

Use anchors ^ and $ to enforce a full string match and add a comma to the character class as you say you want to match strings that only contain digits and commas:

MsgBox testRegExp("2.d", "^[0-9,]*$")
                          ^    ^  ^

I also suggest using * quantifier to match 0 or more occurrences, rather than + (1 or more occurrences), but it is something you need to decide for yourself (whether you want to allow an empty string match or not).

Here is the regex demo. Note it is for PCRE regex flavor, but this regex will perform similarly in VBA.

Yes, as @Chaz suggests, if you do not need to match the string/line itself, the alternative is to match an inverse character class:

MsgBox testRegExp("2.d", "[^0-9,]")

This way, the negated character class [^0-9,] will match any character but a comma / digit, invalidating the string. If the result is True, it will mean the string contains some characters other than digits and a comma.

edited May 23 '17 at 11:59

Community

1
1

answered Nov 20 '15 at 15:07

Wiktor Stribiżew

607,720
39
448
563

Or, alternatively, use the inverse of a partial match of the occurrence of any character that is not a digit or comma, i.e: [^0-9,]. You'll need to reverse the return value. – Chaz Nov 20 '15 at 15:12
@Chaz: I assumed OP needs to match the strings, but looking at the code, it is clear OP is interested in Match/No match result. – Wiktor Stribiżew Nov 20 '15 at 15:15
`[0-9,]*$` can match at the end of the strng. Thus, it will find a match in `dew%#$%#9,` string. You wrote you need to match strings that only contain digits and commas, right? `^[0-9,]*$` is the right regex for it. If you want to just check if a string has anything else, just check with `[^0-9,]`. Or else, precise what you need to obtain given some specific string. – Wiktor Stribiżew Nov 20 '15 at 15:43
It was all about ^ and $, as you explained. – Edgar Navasardyan Nov 20 '15 at 15:49

score 1 · Answer 2 · answered Nov 20 '15 at 15:37

1

You can use the limited built in pattern matching for that:

function isOk(str) As boolean
    for i = 1 To len(str)
        if Mid$(str, i, 1) Like "[!0-9,]" then exit function
    next
    g = True and Len(str) > 0
end function

answered Nov 20 '15 at 15:37

Alex K.

171,639
30
264
288

VBA regexp to check special symbols

2 Answers2