Excel VBA Question – How can I optimize this code?

I’m trying to create a function in Excel, where the user will be able to extract some data using regular expression. I’ve manage to create a nice function. However, there is a performance issue. When the user copy my function over 50 000 lines, Excel takes about 1.5 minutes to update all the values. Here is the function :
Function RegExpFind(FindIn, FindWhat As String, Optional Index As Integer, _
        Optional IgnoreCase As Boolean = True)
    Dim i As Long

    Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
    Set RE = New RegExp

    RE.pattern = FindWhat
    RE.IgnoreCase = True
    RE.Global = True
    Set allMatches = RE.Execute(FindIn)

    ReDim rslt(0 To allMatches.Count - 1)

    For i = 0 To allMatches.Count - 1
        rslt(i) = allMatches(i).Value
    Next i

    RegExpFind = rslt
End Function
You can leave your advice in the comment section below. Nick

Publié

dans

par

Commentaires

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.