VBA Regex: Match not showing any value

Question

The following code is what ive come up with so far. I have also set the reference for regular expression 5.5.

However the match value does not show at all, am I missing something here??

Public Sub check()

Dim wb As Workbook
Set wb = ThisWorkbook

'Prepare a regular expression object
Dim myRegExp As New VBScript_RegExp_55.RegExp
Dim myMatches As MatchCollection
Dim myMatch As match
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = "^\d{6,8}-[SFTG]\d{7}[A-Z]-([^-]+)$"
Set myRegExp = CreateObject("vbscript.regexp")

cellValue = CStr(wb.Worksheets(1).Cells(2, 4).Value) 
'123456-S1234567F-Scholarship Form 

If myRegExp.Test(cellValue) Then


Set myMatches = myRegExp.Execute(cellValue)


For Each myMatch In myMatches
  MsgBox (myMatch.Value)

Next

Else
End If

End Sub

Can you please provide values in wb.Worksheets(1).Cells(2, 4).Value which you want to parse. — Santosh
– Santosh, Commented Sep 26, 2013 at 4:58
Thank you for the reminder, i forgot to include: 123456-S1234567F-Scholarship Form — user1043870
– user1043870, Commented Sep 26, 2013 at 5:03
What do you want from 123456-S1234567F-Scholarship Form string ? — Santosh
– Santosh, Commented Sep 26, 2013 at 5:08

sam092 · Accepted Answer · 2013-09-26 06:03:51Z

1

Removing the following line

Set myRegExp = CreateObject("vbscript.regexp")

seems to solve the issue.

I guess that's because all your initialization statements are gone when you assign myRegExp to a new object

edited Sep 26, 2013 at 6:03

answered Sep 26, 2013 at 5:53

sam092

1,3251 gold badge8 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

VBA Regex: Match not showing any value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related