我正在尝试使用VBA正则表达式来验证表单的时间范围:#0:00xm – #0:00xm其中x是a或p.所以字符串文字可能是“下午1:30 – 凌晨12:00”.我想匹配具有此模式的单元格.
当我在这个在线工具中使用常规表达时:http://public.kvalley.com/regex/regex.asp并检查我的表达式,它匹配正确.
但是,当我在VBA中使用相同的表达式时,它不匹配.
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) End If Next rCell
对于任何关心的人来说,这是我固定的工作版本,特别感谢dda更简单的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,0) Else rCell.Interior.Color = RGB(250,0) End If Next rCell