Excel VBA regular expression error

Question

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

Just a crazy thought. Is TextBox1.Text valid? Can you successfully do a MsgBox on it? — asantaballa
– asantaballa, Commented Aug 20, 2013 at 22:02
What version of Excel and what textbox control are you using? IE ActiveX — Sorceri
– Sorceri, Commented Aug 20, 2013 at 22:05
the textbox is just on a form and yes I can access with a MsgBox, and I'm using excel 2010 with XP — Mike
– Mike, Commented Aug 20, 2013 at 23:39

Mike · Accepted Answer · 2013-08-21 01:19:14Z

2

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

answered Aug 21, 2013 at 1:19

Mike

1,2734 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Zemens Over a year ago

There's probably a difference between a non-matching pattern, and a syntactically invalid pattern. It sounds like you had the latter, but good job for solving your own question!

Sorceri · Accepted Answer · 2013-08-20 22:08:42Z

0

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

answered Aug 20, 2013 at 22:08

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

1 Comment

Mike Over a year ago

Hi Sorceni, the text box is place right on the form it does not nest inside any frame or or shape..

Valentin Briand · Accepted Answer · 2021-03-14 01:16:53Z

0

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].

edited Mar 14, 2021 at 1:16

Valentin Briand

3,7432 gold badges10 silver badges18 bronze badges

answered Mar 12, 2021 at 17:45

Raekwon

13 bronze badges

Collectives™ on Stack Overflow

Excel VBA regular expression error

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related