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

excel, regex, vba

Solution

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

Problem

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 ```

Original source