VBA regular expression to match a time range, such as "13:30 - 12:00",

Question

I am trying to use a VBA regular expression to check the time range of a form: #0:00xm - #0:00xm where x is a or p . Thus, the string literal can be "1:30pm - 12:00am" . I want to map 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

For everyone who cares, this is my fixed working version, with special thanks to dda for its 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 me clear 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 formatted date, like it, and nothing more (conditions < ____ $)). I added both a and A, and p and P to make sure there are no problems.

I do not have VBA / Excel on this computer, so I can not try my code with my regular expression, but the regular expression works.

Answer 3

Inside Excel, go to Cell Format> Number> Category> Custom. You will see many time formats.

What you are probably looking for is h: mm AM / PM

Good. I looked at it more and realized my short-sightedness.

Is this more than what you need? I did a basic test of formatting a time value with AM / PM and cells that are not formatted that way.

 Dim rRange As Range Dim rCell As Range Set rRange = Range("A2", "A4") For Each rCell In rRange If InStr(1, UCase(rCell.Value), " AM") > 0 Or InStr(1, UCase(rCell.Value), " PM") > 0 Then If InStr(1, rCell.Value, ":") <> 0 Then Debug.Print rCell.Value End If Next