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

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonsoir à toutes et à tous,

Merci à tous d'apporter votre contribution !

Je viens de prendre connaissance des codes que vous avez postés et je les ai testés avec une formule complexe.... malheureusement ca ne marche pas (la macro bloque)

Je vous ai mis un exemple de formule que je traiterai avec cette fameuse macro mystere...
Ca se passe dans la feuille "resultat" et la msgbox doit renvoyer exactement le resultat suivant :
(56+78)-EXP(11)*12+11^12/(17--320*-226/EXP(-320))+16-105+(1+0,02)
avec toutes les parentheses et signes.

Bonne nuit et à demain avec une heure de plus pour dormir

Merci à tous

Malka

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonsoir mapomme,

Ton code est presque bon ! il me renvoie le resultat suivant :
(56+78)-EXP(11)*12+(11)^12/(17--320*-226/EXP(B14))+16-SOMME(15:C25)+(1+0,02)

alors que j'attends :
(56+78)-EXP(11)*12+11^12/(17--320*-226/EXP(-320))+16-105+(1+0,02)

Voila, merci beaucoup mapomme. Allez je vais me coucher et je vous dis à demain !

Bonne nuit

Malka

 

[Dranreb]

Re : Afficher une formule excel en formule numerique

Bonsoir.
Cette fonction donne le texte en tenant compte des nouvelles exceptions, à un petit détail près qui pourrait s'arranger si c'est vraiment nécessaires: un résultat d'évaluation de SUM est restitué entre parenthèses. En revanche je ne vois pas pourquoi ni comment enlever les parenthèses en trop de la formule initiale à "(EXPERIENCE!C16)^EXPERIENCE!C17". Il est logique que ça aboutisse à "(11)^12"

Function ExprsFml(R As Range) As String
Dim Z As String, P As Long, C As String * 1, ÀÉvaluer As String, Évalué As String, Zr As String
Z = Mid$(R.Formula, 2)
For P = 1 To Len(Z)
   C = Mid$(Z, P, 1)
   If InStr("()+-*/^", C) > 0 Then
      GoSub Évaluer
      Zr = Zr & C
   Else
      ÀÉvaluer = ÀÉvaluer & C
      End If
   Next P
GoSub Évaluer
ExprsFml = Zr
Exit Function

Évaluer:
If ÀÉvaluer = "" Then Return
If ÀÉvaluer = "EXP" Then Zr = Zr & "EXP": ÀÉvaluer = "": Return
If ÀÉvaluer = "SUM" Then ÀÉvaluer = "SUM(": Return
If ÀÉvaluer Like "SUM(*" Then ÀÉvaluer = ÀÉvaluer & ")"
On Error Resume Next
Évalué = Application.Evaluate(ÀÉvaluer)
If Err Then Évalué = """" & ÀÉvaluer & """?"
On Error GoTo 0
Zr = Zr & Évalué
ÀÉvaluer = ""
Return
End Function

En E10:

=ExprsFml(E5)

affiche "(56+78)-EXP(11)*12+(11)^12/(17--320*-226/EXP(-320))+16-(105)+(1+0,02)"

Cordialement.

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonjour,
testé sur ton seul exemple fourni, se placer en E5 et lancer la macro :

Sub TexteFormule()
Dim MaFormule As String, i As Byte
Dim oRegExp As Object, matches As Object
If ActiveCell.HasFormula = True Then
    With ActiveCell
        MaFormule = ActiveCell.Formula
        MsgBox "Ma formule Excel :" & vbCrLf & MaFormule
    End With
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True
        .Pattern = "((?:(?:[A-Z]+!)?[A-Z]{1,3}\d{1,7})+|" _
        & "[A-Z]+\((?:[A-Z]+!)?[A-Z]{1,3}\d{1,7}:[A-Z]{1,3}\d{1,7}\))"

        If .test(MaFormule) = True Then
            Set matches = .Execute(MaFormule)
            For i = 0 To matches.Count - 1
                MaFormule = Replace(MaFormule, matches(i), Evaluate(CStr(matches(i))), , 1)
            Next i
        End If
    End With
    MsgBox "Ma formule numérique :" & vbCrLf & MaFormule
    Set oRegExp = Nothing
    Set matches = Nothing
Else
    MsgBox "La cellule ne contient pas de formule"
End If
End Sub

Après, il te faut tester sur d'autres cas.
De plus, j'ai l'impression qu'une fonction personnalisée serait plus adaptée qu'une Sub mais est-ce le cas ?
Sur le principe, cette Sub peut facilement être transformée en fonction.
A+

 

[david84]

Re : Afficher une formule excel en formule numerique

Re
même proposition avec une fonction personnalisée :

Function FormuleNum(chaine As Variant) As String
Dim oRegExp As Object, matches As Object, i As Long
If chaine.HasFormula = True Then
    chaine = chaine.Formula
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True
        .Pattern = "((?:(?:[A-Z]+!)?[A-Z]{1,3}\d{1,7})+|" _
        & "[A-Z]+\((?:[A-Z]+!)?[A-Z]{1,3}\d{1,7}:[A-Z]{1,3}\d{1,7}\))"

        If .test(chaine) = True Then
            Set matches = .Execute(chaine)
            For i = 0 To matches.Count - 1
                chaine = Replace(chaine, matches(i), Evaluate(CStr(matches(i))), , 1)
            Next i
        End If
    End With
    FormuleNum = chaine
    Set oRegExp = Nothing
    Set matches = Nothing
End If
End Function

=FormuleNum(E5)

renvoie

=(56+78)-EXP(11)*12+(11)^12/(17--320*-226/EXP(-320))+16-105+(1+0,02)

A+

 

[mapomme]

Re : Afficher une formule excel en formule numerique

Bonjour Malka, david84, à tous,

Dans la continuité, une modif de mon précédent fichier qui prend en compte le fait qu'une même référence peut apparaître plusieurs fois dans la formule et aussi un tri des références suivant la taille pour bien gérer le remplacement des références débutant par la même séquence (ex: remplacer A11 avant A1)

Pour les références de type matrice (ex: A1:B2), j'ai retenu de les remplacer par l'ensemble des valeurs placées entre {} et séparées par des points-virgules ce qui devrait permettre le calcul par la formule finale (si A1:B2 contient 1,2,3,4 alors somme(A1:B2) donne somme({1;2;3;4}) ).

Il n'y a donc pas de traitement spécial de la fonction SOMME.

-> Malka: Pour ne pas mourir ignare, pourriez-vous préciser dans quel but vous faites une telle demande (passage à une formule numérique) ? (ce n'est bien sûr pas une obligation - c'est juste de la curiosité )

Dans le fichier, double-cliquer sur une cellule contenant une formule (edit v2: ajout d'un bouton pour copier la formule)


rem: il va bien falloir que je m'y mette, aux expressions régulières!

 

[david84]

Re : Afficher une formule excel en formule numerique

Re
Une autre version de la fonction acceptant les fonctions d'Excel de type SOMME.SI, NB.SI, NBVAL, etc.
Motif revu et simplifié mais traitement sur les corrrespondances trouvées un peu plus complexe.

Function FormuleNum(chaine As Variant) As String
Dim oRegExp As Object, matches As Object, i As Long
If chaine.HasFormula = True Then
    chaine = chaine.Formula
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True
        .Pattern = "(?:[A-Z]+\((.+?)\)|(?:(?:[A-Z]+!)?[A-Z]{1,3}\d{1,7})+)"
        If .test(chaine) = True Then
            Set matches = .Execute(chaine)
            For i = 0 To matches.Count - 1
                If matches(i) Like "*:*" Then
                    chaine = Replace(chaine, matches(i), Evaluate(CStr(matches(i))), , 1)
                Else
                    If matches(i).submatches(0) <> "" Then
                        chaine = Replace(chaine, matches(i).submatches(0), Evaluate(CStr(matches(i).submatches(0))), , 1)
                    Else
                        chaine = Replace(chaine, matches(i), Evaluate(CStr(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

@mapomme :

il va bien falloir que je m'y mette, aux expressions régulières!

Si tu as besoin, je suis à ta disposition.
A+

 

[mapomme]

Re : Afficher une formule excel en formule numerique

Bonsoir à tous,

Une dernière version avec une verrue pour traiter la somme mais alors d'autres expressions (sommes complexes avec fonction) ne sont plus justes (ligne 25)

Si tu as besoin, je suis à ta disposition. A+

Je n'y manquerai pas le cas échéant. Merci.

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonsoir à tous et à toutes

Alors la je tire mon chapeau à toute l'équipe : david84 (magnifique !); mapomme (excellentissime !); Dranreb (alors grandiose !)

C'est exactement ce que je voulais ! Vous avez réussi à trouver 3 solutions différentes à mon problème.

Vraiment je vous remercie pour votre aide si précieuse. J’ai été carrément bluffée par le RegExp… je ne connaissais pas. Il faudra que je me penche sérieusement dessus pour décrypter la commande.

Pour les parenthèses mises un peu bizarrement, ce n'est pas grave car il est possible que les formules aient ce genre de « non sens algébrique » mais cela permet d'isoler certains arguments qui peuvent avoir un sens "opérationnel / économique" on va dire....

Alors mapomme tu te demandes pourquoi j'ai besoin d'avoir la formule numérique... En fait, je travaille actuellement sur un simulateur de business plan pour ma boite. Pour faire simple je simule des recettes et des charges (une centaine) pour plusieurs business units (20). Pour chacun des cas, les estimations de recettes ou de charges sont représentées par une formule mathématique unique qui peut être très complexe dans son expression et parfois très importantes en nombre d’argument. Or dans ces formules mathématiques, les arguments utilisés sont variables lors de la simulation. Ils prennent une valeur dont j’ai besoin de « capter / photographier » pour en garder une trace. Le résultat du calcul qui en résulte dans la cellule (un chiffre ou un nombre) est intéressant mais j’ai besoin de connaitre les valeurs prises par les arguments. Il faut bien expliquer comment est construit le résultat et les hypothèses prises dans les arguments de la formule. De plus cela me permet de vérifier en un coup d’œil si résultat du calcul a bien intégré correctement une modification de valeur d’un des arguments…
Les arguments utilisés sont multiples : Evolution du PIB, Variation USD/EURO, Evolution tarifaire, cours du baril de pétrole, etc.. J’en ai presque un millier.
Mapomme, dans mon simulateur, j’ai presque la même chose que toi, cad 2 fenêtres : la première me donne la formule mathématique avec le nom réel des arguments et la deuxième la formule numérique. En fonction de la version du business plan simulé, je garde dans une autre feuille les deux expressions de la formule. Il m’arrive parfois de générer des centaines de versions pour ne garder que la plus pertinente et la plus probable.

Voila je suis SUPER heureuse !!

Longue vie au forum et à ses experts !

Bonne soirée à tous

Malka

 

[mapomme]

Re : Afficher une formule excel en formule numerique

Bonsoir Malka,

Merci du retour et d'avoir accepté de répondre à ma curiosité

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonsoir,
si les solutions te conviennent, c'est le principal mais saches qu'il reste encore des cas non traités correctement.
Si joint une version couvrant d'autres types de cas :

Function FormuleNum(chaine As Variant) As String
Dim oRegExp As Object, matches As Object, i As Long
If chaine.HasFormula = True Then
    chaine = chaine.Formula
    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(chaine) = True Then
            Set matches = .Execute(chaine)
            For i = 0 To matches.Count - 1
                If matches(i) Like "*[:,]*" Then
                    chaine = Replace(chaine, matches(i), Evaluate(CStr(matches(i))), , 1)
                Else
                    If matches(i).submatches(1) <> "" Then
                        chaine = Replace(chaine, matches(i).submatches(1), Evaluate(CStr(matches(i).submatches(1))), , 1)
                    Else
                        chaine = Replace(chaine, matches(i), Evaluate(CStr(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

Je regarderai si j'ai le temps comment améliorer le motif ou si un autre motif ne permettrait pas de traiter le problème d'une autre manière.
A+

 

[mapomme]

Re : Afficher une formule excel en formule numerique

Bonjour à tous,

Une autre dernière version ayant corrigé certains bugs ainsi qu"avec une amélioration de la verrue SOMME qui maintenant est grosse comme un champignon

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonjour à toutes et à tous,

Merci mapomme et david84 pour votre nouvelle mise à jour de votre code. Je suis persuadée que votre code servira à d'autres.

Bon apres-midi

Merci

Malka

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonjour,
@Malka : si tu me le permets, il y a quelque chose qui ne me paraît pas cohérent dans ta demande mais peut-être vas-tu me l'expliquer. Cela concerne le traitement des fonctions présentes dans ta formule : si tu prends ton exemple

=(B6+78)-EXP(EXPERIENCE!C16)*EXPERIENCE!C17+(EXPERIENCE!C16)^EXPERIENCE!C17/(EXPERIENCE!C22-B14*B12/EXP(B14))+16-SOMME(EXPERIENCE!C20:C25)+(1+EXPERIENCE!C7)

Pourquoi demandes-tu à ce que EXP(EXPERIENCE!C16) soit traité différemment de SOMME(EXPERIENCE!C20:C25) ?

Dans le 1er cas, tu demandes à garder le nom de la fonction et de remplacer l'adresse de la cellule par sa valeur, donc tu ne veux pas ramener le résultat de ce calcul.
Par contre, dans le 2ème cas, tu veux ramener le résultat du calcul...pourquoi ne pas traiter toutes les fonctions de la même manière ?

Si tu veux obtenir la formule en numérique, il me parait plus cohérent de calculer les différentes parties de ta formule afin de remplacer chaque partie par sa valeur numérique (le résultat de son calcul). C'est ce qui se passe quand tu cherches à évaluer la formule (onglet formule>audit de formule>Evaluer les formules) sauf que là on veut conserver le résultat de l'évaluation des différentes parties de la formule et non simplement le résultat final de la formule évaluée comme tu l'obtiens avec cette boîte de dialogue.

Par contre, si le but recherché est non pas de récupérer le résultat de chaque partie de ta formule mais d'afficher la construction de ta formule (et donc notamment les différentes fonctions utilisées et les plages de cellules sélectionnées), alors là, le résultat obtenu doit correspondre à ce que tu obtiens quand tu affiches la formule (onglet formule>audit de formule>Afficher les formules).

Il me semble que dans ta formulation il y a 2 logiques qui se télescopent un peu.
Qu'en pense-tu ? Qu'en pensez-vous ?
A+

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonsoir,
Malka est peut être en congés, d'où l'absence de réponse (si c'est le cas, bonne vacances) !
En attendant, et malgré mes interrogations toujours d'actualité quant à la logique de la demande, ci-joint une version plus aboutie traitant des formules comportant des fonctions plus complexes, mais peu testée (une vingtaine de formules dans le fichier ci-joint).
Pour ceux et celles que le sujet intéresse, à tester de votre côté et communiquer les bugs relevés.

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

A+