Function FormuleNum(Chaine As Variant) As String
Dim oRegExp As Object
Dim matches As Object
Dim sChaine As String
Dim i As Byte
If Chaine.HasFormula = True Then
Chaine = Chaine.Formula
sChaine = Chaine
Traitement:
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
.Global = True
.Pattern = "(?:[A-Z]+\({2,}[""]?(.+?)[""]?\){2,}|[A-Z]+\([""]?(.+?)[""]?\)+|(?:(?:[A-Z]+!)?[A-Z]{1,3}\d{1,7})+)"
If .test(sChaine) = True Then
Set matches = .Execute(sChaine)
For i = 0 To matches.Count - 1
If matches(i) Like "*[:,]*" Then
On Error Resume Next
Chaine = Replace(Chaine, matches(i), Evaluate(CStr(matches(i))), , 1)
If Err.Number > 0 Then
.Pattern = "(?:[A-Z])+\(+(.+?)\){2,}"
If .test(sChaine) = True Then
Set matches = .Execute(sChaine)
Chaine = Replace(Chaine, matches(0), Evaluate(CStr(matches(0))), , 1)
sChaine = Replace(sChaine, matches(0), "", , 1)
Set oRegExp = Nothing: On Error GoTo 0: GoTo Traitement
End If
Else
sChaine = Replace(sChaine, matches(0), "", , 1)
End If
Else
If matches(i).submatches(1) <> "" Then
Chaine = Replace(Chaine, matches(i).submatches(1), Evaluate(CStr(matches(i).submatches(1))), , 1)
sChaine = Replace(sChaine, matches(i), "", , 1)
Else
Chaine = Replace(Chaine, matches(i), Evaluate(CStr(matches(i))), , 1)
sChaine = Replace(sChaine, matches(i), "", , 1)
End If
End If
Next i
End If
End With
FormuleNum = Chaine
Set oRegExp = Nothing: Set matches = Nothing
End If
End Function