VBA Regular Expression to match a time range like "1:30pm - 12:00am"

Question

I am trying to use a VBA regular expression to validate a time range of the form: #0:00xm - #0:00xm where x is a or p. So the string literal could be "1:30pm - 12:00am". I want to match cells that have this pattern.

When I use the regular express in this online tool: http://public.kvalley.com/regex/regex.asp and check my expression, it matches correctly.

However, when I use the same expression in VBA, it does not match.

Dim rRange As Range
Dim rCell As Range

Set rRange = Range("A2", "A4") '"G225")

For Each rCell In rRange.Cells
MsgBox (rCell.Value)
    If rCell.Value Like "^([0-9]{1,2}[:][0-9]{2}[apm]{2}[ ][-][ ][0-9]{1,2}[:][0-9]{2}[apm]{2})$" Then
    MsgBox ("YES")
        'rCell.Interior.Color = RGB(0, 250, 0)
    Else
    MsgBox ("NO")
        'rCell.Interior.Color = RGB(250, 0, 0)
    End If
Next rCell

Answer 1

To anyone who cares, this is my fixed, working version with special thanks to dda for his simpler RegEx ^^:

Dim rRange As Range
Dim rCell As Range

Dim re As Object
Set re = CreateObject("vbscript.regexp")
With re
  .Pattern = "^\d\d?:\d\d[aApP][mM] - \d\d?:\d\d[aApP][mM]$"
  .Global = False
  .IgnoreCase = False
End With

Set rRange = Range("A2", "G225")

For Each rCell In rRange.Cells
    If re.Test(rCell) Then
        rCell.Interior.Color = RGB(0, 250, 0)
    Else
        rCell.Interior.Color = RGB(250, 0, 0)
    End If
Next rCell

Answer 2

Let's clean up and improve your regex:

^\d\d?:\d\d[aApP][mM] - \d\d?:\d\d[aApP][mM]$

This will only match if the whole cell is a date formatted like you want it, and nothing else (the ^____$ conditions). I added both a and A, and p and P to be sure there's no case issues.

I don't have VBA/Excel on this machine, so can't try your code with my regex, but the regex per se works.