Afficher une formule excel en formule numerique

[Malka]

Bonjour à tous,

Je suis actuellement coincée dans mon travail à cause d'une macro que je n'arrive pas à faire et j'aurai besoin de votre savoir faire en programmation VBA.

Le probleme :
J'essaie de d'afficher une formule excel en formule numerique cad :

Exemple:
En cellule A1 j'ai le resultat suivant : -71,64 qui provient de la formule excel suivante : =Formules!H19 * (1 + Data!B8)

Ce que j'aimerai c'est afficher dans une msgbox la formule numerique en format texte cad :
"-71,14*(1+0,02)"

-71,14 est la valeur contenue dans "Formules!H19" et 0,02 est la valeur contenue dans "Data!B8"

En esperant avoir été claire dans mes explications.

Merci beaucoup

Bon we

Malka

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonjour,
version gérant le cas des fonctions INDIRECT et ALEA() (exemples D9 et D18 du fichier du message #30) :

Function FormuleNum(chaine As Variant) As String
Dim oRegExp As Object
Dim matches As Object
Dim sChaine As String
Dim i As Byte

Application.Volatile
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

A+

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonjour,
ci-joint le code actualisé.

L'ancien bloquait sur certaines formules telles que

=SOMMEPROD((A1:A9="a")*(B1:B9)*ALEA()*10)+(B1-10)

alors que

=SOMMEPROD((A1:A9="a")*(B1:B9)*(ALEA()*10))+(B1-10)

fonctionnait.

Cette nouvelle version prend également en compte les noms d'onglet comportant des espaces ou _.

A noter que même les formules incohérentes pour Excel telles que

=INDEX('  EXPERIENCE_test '!C7:C112;EQUIV(Feuil1!B2;Feuil1!B1:B9;0))+DECALER(A1;3;)

ramènent le résultat (logique mais peut-être intéressant à savoir).

J'ai déclaré l'objet RegExp en utilisant sa bibliothèque afin de permettre à ceux qui veulent tester les motifs de bénéficier de la visualisation des propriétés et méthodes de cet objet.

Ce code demanderait plus de tests pour pouvoir être validé : à vous, si le coeur vous en dit, de tester des formules de votre côté et de me signaler les formules testées et les bugs éventuels.
A+

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonjour,
Grosse boulette (personne ne l'avait remarquée ?) ! En modifiant mon mode opératoire, j'avais simplement oublié de traiter le cas de formules ne comportant aucune parenthèse du type=B3 par exemple...de même, les $ n'étaient pas pris en compte.
Cette actualisation tient compte de ces cas, avec en prime la prise en compte des cellules et plages nommées :

Function FormuleNum(Chaine As Variant) As String
Dim sCopChaine As String, sFonction As String
Dim oRegExp As VBScript_RegExp_55.RegExp
Dim oRegExp2 As VBScript_RegExp_55.RegExp
Dim oMatches As VBScript_RegExp_55.MatchCollection
Dim oMatches2 As VBScript_RegExp_55.MatchCollection
Dim i As Byte, j As Byte
Dim NomDef As Names, LeNom As String
    
Application.Volatile 'pour les fonctions volatiles
If Chaine.HasFormula = True Then
    Chaine = Chaine.Formula
    
    'Remplacement des noms définis par leur référence de plage
    Set NomDef = ActiveWorkbook.Names
    If Not NomDef Is Nothing Then
        For j = 1 To NomDef.Count
            Chaine = Replace(Chaine, NomDef.Item(j).Name, Right(NomDef.Item(j).RefersTo, Len(NomDef.Item(j).RefersTo) - 1))
        Next j
    End If
    j = 0
    
    sCopChaine = Chaine
    
    Set oRegExp = New VBScript_RegExp_55.RegExp
    Set oRegExp2 = New VBScript_RegExp_55.RegExp
    
    oRegExp.Global = True
    oRegExp2.Global = True
    
    'Motif traitant les nombres non placés entre parenthèses
    oRegExp.Pattern = "(\=|\+|-|\*|/|^)(\d+)(\+|-|\*|/|^)"
    If oRegExp.test(sCopChaine) = True Then sCopChaine = oRegExp.Replace(sCopChaine, "$3")
    
    'Motif traitant les caractères placées entre parenthèses
    oRegExp.Pattern = "(?:=|\+|-|\*|/|^|,|[& ""]+)?(.*?\)+)"
    
    'Motif traitant les références aux cellules (style de référence A1)
    oRegExp2.Pattern = "(?:(?:'\s*)?\w+(?:\s*')?!)?\$?[A-Z]{1,3}\$?\d{1,7}"
    
    If oRegExp.test(sCopChaine) = True Then
        Set oMatches = oRegExp.Execute(sCopChaine)
        For i = 0 To oMatches.Count - 1
            If oMatches(i) Like "*[:,""]*" Or oMatches(i) Like "*()*" Then
fonction:
                If j = 0 Then sFonction = sFonction & oMatches(i).submatches(0) Else sFonction = sFonction & oMatches(i)
                On Error Resume Next
                Chaine = Replace(Chaine, sFonction, Evaluate(sFonction), , 1)
                If Err.Number <> 0 Then
                    On Error GoTo 0
                    j = j + 1: i = i + 1
                    GoTo fonction
                Else
                    sCopChaine = Replace(sCopChaine, sFonction, "", , 1): sFonction = "": j = 0
                End If
            Else
                If oRegExp2.test(oMatches(i)) = True Then
                    Set oMatches2 = oRegExp2.Execute(oMatches(i))
                    For j = 0 To oMatches2.Count - 1
                        Chaine = Replace(Chaine, oMatches2(j), Evaluate(CStr(oMatches2(j))), , 1)
                    Next j
                    sCopChaine = Replace(sCopChaine, oMatches(i), "", , 1): j = 0
                End If
            End If
        Next i
    Else
        If oRegExp2.test(sCopChaine) = True Then
            Set oMatches2 = oRegExp2.Execute(sCopChaine)
            For j = 0 To oMatches2.Count - 1
                Chaine = Replace(Chaine, oMatches2(j), Evaluate(CStr(oMatches2(j))), , 1)
            Next j
        End If
    End If
    FormuleNum = Chaine
    Set oRegExp = Nothing: Set oRegExp2 = Nothing
    Set oMatches = Nothing: Set oMatches2 = Nothing
End If
End Function

Pas de bug détecté de votre côté ?
A+

 

[CISCO]

Re : Afficher une formule excel en formule numerique

Bonjour à tous, bonjour David84


Bien sûr que j'avais repété le bug (vu mon niveau en VBA !!!). Plus sérieusement, dès que j'aurai un peu plus de temps, je testerai ta macro plus sérieusement, et j'essayerai de comprendre la macro... Merci.

@ plus

 

[david84]

Re : Afficher une formule excel en formule numerique

Re

Bien sûr que j'avais repété le bug (vu mon niveau en VBA !!!)

Pas besoin de comprendre le VBA, il te faudra simplement tester le code sur toute sorte de formules, et =B3, il n'y a pas plus simple ! Si tu veux des explications sur le code, n'hésite pas.
A+

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonjour à tous !

Waouh ! je suis surprise que ce post ait vecu aussi longtemps.
Merci beaucoup du travail accompli DAVID84 ! Je suis certaine que cela servira à d'autres !
Pour mon besoin, tu avais repondu à ma question il y a bien longtemps... j'ai fini ma travail grace à vous !

Bonne journée

Malka

 

[CISCO]

Re : Afficher une formule excel en formule numerique

Bonjour à tous, bonjour David

Re
Pas besoin de comprendre le VBA,
A+

Je plaisantai.

@ plus