Regular Expression - traps for young players

[ChrisGreaves]

I got caught on this one, thought I'd share it with you.

The function as a whole might not be worth analyzing.
I reproduce it in full to introduce you to the line "lngStart = rng.Start + oMatches(0).FirstIndex",
which used to read "lngStart = oMatches(0).FirstIndex".

Hah!

I stoledeveloped a neat-oh regular expression to locate US state codes in a chunk of text.
The RegExp worked prfectly in a little test-mode device.
It worked fine in a little TEST macro within my module. It located the state of OHio every time!
When I dropped the RegExp into the function (below) it didn't highlight OH at all, at all.

Duh!

The .FirstIndex is, of course, the start-point within the supplied text.
I needed to add to it the .Start point of the supplied text, relative to the active document.

Code: Select all

Function rngLocateEmail(rng As Range, strRegExp As String) As Range
    Dim oMatches As MatchCollection
    Dim oMatch As Match
    On Error GoTo Catch
    Dim objRegExp As New RegExp
    objRegExp.Global = True
    objRegExp.Pattern = strRegExp
    Set oMatches = objRegExp.Execute(rng.Text)
    If oMatches.Count <> 0 Then
        Dim lngStart As Long
        lngStart = rng.Start + oMatches(0).FirstIndex
        Dim lngLength As Long
        lngLength = oMatches(0).Length
        Dim strValue As String
        strValue = oMatches(0).Value
    Else
    End If
    Set rngLocateEmail = rng
    rngLocateEmail.Start = lngStart
      rngLocateEmail.End = rngLocateEmail.Start + lngLength
    Exit Function
Catch:
    MsgBox "ValidateEmailAddress function" & vbCrLf & vbCrLf _
        & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function