Return RegEx MatchCollection from function in VBA - error 450

Question

I have been struggling with this issue for a couple of days and have basically been unable to find a solution

I have written a function in VBA that should return the result from a RegEx.Execute statement but every time the 'End Function' statement is reached, I get a Runtime Error 450 and I simply cannot figure where things go wrong since my assigning of object variables does not throw an error.

I am new to VBA and programming altogether so there is probably just something I have misunderstood in the way objects/MatchCollection/Collections behave.

Here is my code:

Function FindDateAndTimePatternRegEx(s As String) As Object 
On Error GoTo erro
Dim objRex As RegExp
Dim arrMatch As Object


's = "   Afgår 01-01-2017 00:24:00, anvendt log 01-01-2017 10:53:24 er 10:29:24 EFTER dette"

Set objRex = New RegExp

With objRex

    .IgnoreCase = True
    .Global = True ' the global parameter makes sure that all pattern matches in the string are found and returned in the return collection
    .Pattern = "([0-9]{2}\-[0-9]{2}\-[0-9]{4})" & " " & "([0-9]{2}:[0-9]{2}:[0-9]{2})" 

End With

Set arrMatch = objRex.Execute(s) 

Set FindDateAndTimePatternRegEx = arrMatch

'Debug.Print FindDateAndTimePatternRegEx.Item(0).FirstIndex
'Debug.Print FindDateAndTimePatternRegEx.Item(1)   


Set objRex = Nothing

Exit Function

erro:
MsgBox Err.Description

End Function

Everytime I try this out in the Immediate window calling the function and using the string s

? ?FindDateAndTimePatternRegEx("   Afgår 01-01-2017 00:24:00, anvendt log 01-01-2017 10:53:24 er 10:29:24 EFTER dette")

the debug.print returns the expected values but when either Exit Function or End Function is reached, I get the runtime error 450

Could anybody explain what goes wrong?

Thanks in advance

Best regards

Wiktor Stribiżew · Accepted Answer · 2017-03-07 08:43:52Z

1

Your regex related code is alright, but the way you are calling the function is wrong: you need to use Set:

Dim regexResults As Object
Set regexResults = FindDateAndTimePatternRegEx("   Afgar 01-01-2017 00:24:00, anvendt log 01-01-2017 10:53:24 er 10:29:24 EFTER dette")

answered Mar 7, 2017 at 8:43

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

1 Comment

Anders Andersen Over a year ago

Sorry, of course - I have now accepted the answer. Still learning how to navigate the forum

FunThomas · Accepted Answer · 2017-03-07 08:36:52Z

0

You have to check if the Execute could find something.

Dim i As Integer
If arrMatch.Count = 0 Then
    Debug.Print "No match"
Else
    For i = 0 To FindDateAndTimePatternRegEx.Count - 1
        Debug.Print i; FindDateAndTimePatternRegEx.Item(i).FirstIndex
    Next i
End If

answered Mar 7, 2017 at 8:36

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

Return RegEx MatchCollection from function in VBA - error 450

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related