dimanche 25 juin 2017

Regular Expression VBA : find particular set of data

I've encountered an issue with my macro. I'm trying to find in a string, elements beginning with FP 608 or TM 608.

The string I try to extract always begins with FP or TM. The elements after are numbers, 3 or 4 numbers (100 or 1000). they can also have 2 decimals (100.10 or 1000.10).

For instance it should extract :

  • ABCDF FP 573,83 ABDFEG HIJ KLM 0124" : Extract : 573,83
  • ABCFED ERD 536,98 [...] = no extraction
  • TM 123,12 ABCDD EFGHIJ KLM : Extract TM 123,12
  • FP 100 : Extract : FP 100

This seems to work but it's inefficient :

Function GetRealCost(MyRange As Variant)

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")
    Dim strPattern As String
    Dim strInput As String
    Dim strOutput As Object

    strPattern = "\b(TM )([0-9]{3,4})(,[0-9]{1,2}\b)"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

        strInput = MyRange

        If regEx.test(strInput) Then
            regEx.Pattern = "(\b[0-9]{3,4})(,[0-9]{1,2}\b)"
            Set strOutput = regEx.Execute(MyRange)
            GetRealCost = regEx.Execute(strInput)(0)
        Else
            strPattern = "\b(TM )([0-9]{3,4}\b)"
            regEx.Pattern = strPattern
                If regEx.test(strInput) Then
                    regEx.Pattern = "(\b[0-9]{3,4}\b)"
                    Set strOutput = regEx.Execute(MyRange)
                    GetRealCost = regEx.Execute(strInput)(0)
                        Else
                            strPattern = "\b(FP )([0-9]{3,4})(,[0-9]{1,2}\b)"
                            regEx.Pattern = strPattern
                                If regEx.test(strInput) Then
                                regEx.Pattern = "(\b[0-9]{3,4})(,[0-9]{1,2}\b)"
                                Set strOutput = regEx.Execute(MyRange)
                                GetRealCost = regEx.Execute(strInput)(0)
                                        Else
                                strPattern = "\b(FP )([0-9]{3,4}\b)"
                                regEx.Pattern = strPattern
                                    If regEx.test(strInput) Then
                                        regEx.Pattern = "(\b[0-9]{3,4}\b)"
                                        Set strOutput = regEx.Execute(MyRange)
                                        GetRealCost = regEx.Execute(strInput)(0)
                                            Else
                                        GetRealCost = ""
                                    End If
                    End If
                End If
        End If

Application.ScreenUpdating = True

End Function

Thank you !

Aucun commentaire:

Enregistrer un commentaire