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

 

[Dranreb]

Re : Afficher une formule excel en formule numerique

Bonjour.
S'il s'agit d'analyser une formule quelconque et d'y remplacer dans le résultat souhaité les références aux cellules par leurs valeurs, c'est un problème assez coton !
Sinon une expression du style [Formules!H19].Value & "*(1+" & [Data!B8].Value & ")" peut faire l'affaire.
À +

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonsoir Dranreb,

Merci d'avoir répondu à mon post.
C'est exactement ce que tu as dit et la problematique semble assez difficile apparement !
Il s'agit bien d'analyser une formule quelconque et d'y remplacer dans le résultat souhaité les références aux cellules par leurs valeurs et d'envoyer la formule mathematique avec des nombres dans un msgbox.

L'idée est de "lire" les données telle que :
=Formules!H19 * (1 + Data!B8) --> "-71,14*(1+0,02)"

J'en ai malheureusement besoin pour avancer dans mon travail et il faut absolument que je trouve la solution
Si quelqu'un a une inspiration ou un debut d'idée je suis preneuse

Bonne soirée

Merci
Malka

 

[Gelinotte]

Re : Afficher une formule excel en formule numerique

Bonsoir,

Est-ce qu'il faut comprendre que tu veux sélectionner une cellule contenant une formule quelconque et avoir dans un msgbox la composition telle que démontrée au post #1 ?

Si c'est cela, la composition de la formule est-elle toujour identique (x*(1+y)) ou peut-elle variée ((x-z)/(y+w*2)) ?


G

 

[Dranreb]

Re : Afficher une formule excel en formule numerique

Est-ce qu'au moins la formule ne comportera que les 4 opérations et des parenthèses ?
S'il faut aussi accepter des appels de fonctions c'est vraiment corsé !
Sinon on devrait pouvoir explorer la formule par des Mid$ pour déceler ces 6 caractères et obtenir les valeurs de tout ce qu'il y a entre à l'aide de Application.Evaluate.
À +

 

[dmc]

Re : Afficher une formule excel en formule numerique

Bonjour à tous.
Sauf erreur de ma part, en VB
maformule=cells(x,y).formula
cela renverra le texte de la formule dans maformule, non ?
amicalement

 

[Malka]

Re : Afficher une formule excel en formule numerique

Bonjour à toutes et à tous,

Merci DMC pour ta réponse. Je l'ai essayée mais ca me donne la formule sous forme de renvoie de reference de type : "=Formules!H19 * (1 + Data!B8)". A moins que je n'ai pas mis un parametre particulier pour me donner le resultat voulu, cette methode ne semble pas marcher.

Merci aussi à Dranreb et Gelinotte pour vos posts. La formule en question peut contenir : les 4 operations classiques (+-*/) et en plus la fonction exponentielle (exp()) et la fonction puissance (^).
--> donc 6 operateurs mathematiques. Il y aura aussi beaucoup de parantheses, de chiffres/nombres donc constantes. La formule peut atteindre parfois une trentaine d'argument.......

Vous l'aurez compris, la formule est variable et n'est jamais identique d'une cellule à l'autre.
Bref je sens que je suis dans la m...rde !

Si vous avez un debut de code pour decomposer cette fameuse formule, je prends volontier.

Merci beaucoup

Malka

 

[pierrejean]

Re : Afficher une formule excel en formule numerique

Bonjour à tous

Peu de temps immediatement mais un debut d'idée :
A partir de cellule.formula , exclure tous les signes () +*-^ , ; etc .Il ne devrait plus rester qu'un string a partir duquel un split donnerait les expressions a evaluer et transformer en valeurs , valeurs a reintergrer dans le cellule.formula
Bien entendu un maximum de formules test est souhaitable !!!

 

[dmc]

Re : Afficher une formule excel en formule numerique

re_bonjour Malka , Dranreb et Gelinotte
Ma formule a 2 avantages : donner "en clair" la formule contenue par une cellule, et permettre de décomposer ensuite cette zone en identifiant les parenthèses, et les opérateurs
pour ma part, je tenterais :
de remonter caractère par caractère "maformule" pour détecter que l'on se trouve :
soit sur un opérateur ou une parenthèse,
soit sur une valeur (chiffre ou nom de champ ou chaine de caractères ou signe "=")
Il me semble réalisable de reconstituer un "formule" du type :
tant que l'on est sur des opérateurs, on les accumule dans une chaine "operateurs"
sinon on accumule dans une autre chaine "troncon_isolé" les autres caractères trouvés, jusqu'à rencontrer soit la fin de "maformule" soit un operateur, c'est à ce moment que l'on réassemble dans une chaine du type :
resultat = resultat & operateurs & evaluate("=" & tronçon_isolé)
et on refait un tour jusqu'à la fin de la chaine
je n'ai pas le temps de te faire la boucle, je suis malgré tout au travail aujourd'hui !
Bon courage et dis-moi si je me suis planté dans l'interprétation de ta question.

 

[Malka]

Re : Afficher une formule excel en formule numerique

Re_Bonjour à toutes et à tous,

Merci beaucoup à ceux qui s'interessent à ce post dont je n'imaginais pas la complexité du probleme... à vrai dire je pensais betement qu'il existerait une fonctionnalité sur excel permettant cette conversion.

Que dire... je pense que la problematique vous l'avez bien cernée et je vois la trajectoire à suivre pour arriver à UNE solution à ce probleme.
Encore faut-il que j'ai la competence technique pour y arriver... C'est la que j'ai besoin de vous les amis.

Le decoupage de la formule en reperant les operateurs, les constantes autres que celles contenues dans les references aux cellules, identifier les operateurs mathematiques tel que l'operateur mathematique exponentielle (EXP) dans un argment de type EXP('EXPERIENCE INDUSTRIELLE'!EXP$6), etc.... et reconstruire la formule sous forme numerique dans le bon ordre me parait insurmontable pour les connaissances que j'ai en programmation VBA . C'est un sacré challenge !!

Je n'ai pas le choix, il faut absolument que je trouve la solution, meme si je dois y passer mon we entier.

Merci beaucoup à tous ceux qui apporteront leurs contributions.... je pense que le resultat servira certainement à d'autres

Malka

 

[mapomme]

Re : Afficher une formule excel en formule numerique

Bonjour Malka, Dranreb, Gelinotte, dmc, pierrejean et à tous,

Un essai dans le fichier joint par recherche des antécédents. L'essai se limite aux références situées sur les feuilles du même classeur.

Il fonctionne avec des formules ou fonctions simples du type:
=PUISSANCE(A5;A13) ou
='Avec espaces'!A1 + RACINE(-SansEspace!B2)*10 + 1/Feuil1!A14

Il reste des anomalies avec des valeurs Texte. Il ne traite que les références simples du type A1;A2;A3 et non pas A1:A3
( somme( A1;A2;A3) -> OK, somme( A1:A3) -> KO )

Pour le classeur joint,

• Sélectionner une cellule de la colonne B (cellule verte)
• Puis cliquer sur le bouton 'FormuleNumeric'
• Le résultat s'affiche en colonne C
• Les cellules avec fond rouge retournent un résultat erroné.

nb Malka: l'essai fonctionne avec la formule: =EXP('EXPERIENCE INDUSTRIELLE'!EXP$6) et 'EXPERIENCE INDUSTRIELLE'!EXP$6 qui vaut 2. Voir v2, ligne 19 qui donne =EXP(2)

il semble aussi fonctionner avec l'opérateur ^

rem : j'ai considéré que les noms des formules étaient à conserver. Il n'y avait pas d'indication sur ce point.

 

[david84]

Re : Afficher une formule excel en formule numerique

Bonjour à tous,
Un fichier exemple s'avère nécessaire pour comprendre ton attente.
Un essai avec ce que j'en ai compris (il y a sûrement plus simple mais pas évident de savoir ce que tu veux avec seulement tes explications) : se placer en A4 et lancer le code dans l'éditeur VBE (ALT+F11 pour l'ouvrir)

Sub TexteFormule()
Dim MaFormule As String, i As Byte
Dim oRegExp As Object, matches As Object

With ActiveCell
    ActiveWindow.DisplayFormulas = True
    MaFormule = ActiveCell.Text
    ActiveWindow.DisplayFormulas = False
    MsgBox MaFormule
End With
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
    .Global = True
    .Pattern = "[^\*-\+/=]+"
    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 MaFormule
Set oRegExp = Nothing
Set matches = Nothing
End Sub

A+

 

[dmc]

Re : Afficher une formule excel en formule numerique

Bonjour David84 et Mapomme, rebonjour aux autres
David : c'est fabuleux, si simple !
Par ta faute, je vais probablement passer mon WE à décortiquer le createobject
merci (quand même)
David (bis ou David17 )

 

[CISCO]

Re : Afficher une formule excel en formule numerique

Bonjour

Je suis très intéressé par le sujet, mais comme je suis nul en VBA, je ne pourrai pas vous aider. Grrrrrr.

Ceci dit bravo à Mapomme et à David84.

Au plaisir de vous relire.

@ plus

Cisco

 

[david84]

Re : Afficher une formule excel en formule numerique

Re
@dmc

Par ta faute, je vais probablement passer mon WE à décortiquer le createobject

C'est du RegExp (expression rationnelle). Tu as un fil qui y est consacré ici.
Si tu as des questions pose-les sur ce fil ou sur le fil indiqué.
Sinon, la même macro un peu modifié.
Elle ne règle pas tous les cas bien entendu (loin de là) mais le principe de traitement est là.
J'attends un fichier exemple plus détaillé pour examiner les cas restant à traiter.

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 MaFormule
    End With
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True
        .Pattern = "[^\^\*-\+/=]+"
        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 MaFormule
    Set oRegExp = Nothing
    Set matches = Nothing
Else
    MsgBox "La cellule ne contient pas de formule"
End If
End Sub

A noter que l'on peut construire une fonction personnalisée au lieu d'une Sub sur le même principe (peut-être qu'une fonction sera plus adaptée mais je n'ai pas les éléments de réponse pour l'instant).
A+