CODEWITHSUNDEEP
regexexcelvba
Mike
Mike

Reputation: 1261

Excel VBA regular expression error

Hello I'm having some issue tring to get the regular expression code below to work. I'm getting a application-undefine or Object-Undefine error

the text box is on user form.

the error is occurring on the line "Set allMatches = regEx.Execute(TextBox1.Text)" not sure what I missed.

Dim regEx As Object

Dim allMatches As Object

Set regEx = CreateObject("VBScript.RegExp")
With regEx
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "\d{2,2}/\d{2,2}/\d{4,4} \d{2,2}:\d{2,2)[AM|PM]"
        .Global = True
End With

Set allMatches = regEx.Execute(TextBox1.Text)


If allMatches.Count <> 0 Then
    result = allMatches.Item(0).submatches.Item(0)
End If

Upvotes: 2

Views: 5394

Answers (3)

Raekwon
Raekwon

Reputation: 1

Assuming you are accessing the textbox correctly, the [AM|PM] poses a problem in my mind.
It seems to me this would match one character: A, M or P, or M.

If it were me I would use [A|P]M- then the first letter could be A or P and the second letter must be M.
That would also assume it is only looking for capital letters.
To include lowercase, ([Aa]|[Pp])[Mm] OR maybe better [AaPp][Mm].

Upvotes: 0

Mike
Mike

Reputation: 1261

Ok after some googling and looking, I found the problem: its with the pattern:

 .pattern = "\d{2,2}/\d{2,2}/\d{4,4} \d{2,2}:\d{2,2**)** [AM|PM]"

it turns out you will get the 5017 error if the pattern is not valid.

by changing the ")" to the proper closing "}" the error was solved.

 .pattern = "\d{2,2}/\d{2,2}/\d{4,4} \d{2,2}:\d{2,2**}** [AM|PM]"

I would thought that if the pattern did not match then you would get a false return, not so..

Upvotes: 2

Sorceri
Sorceri

Reputation: 8033

So it is possible that you are accessing the Textbox incorrectly. Without knowing how you setup the textbox I am going to guess that its held in the shapes collection. You could look for the textbox and then set the allMatches such as below

Dim shp As Shape
'loop through the shapes on the sheet - assuming you are working with sheet 1
For Each shp In ThisWorkbook.Sheets(1).Shapes
    If shp.Name = "TextBox1" Then
         Set allMatches = regEx.Execute(shp.TextFrame2.TextRange.Text)
    End If
Next

Upvotes: 0

Related Questions