XL 2013 Résultat de formule sans la calculer

benabou

XLDnaute Nouveau
Bonsoir, après plusieurs heures d'essai et de recherche sur le net sans résultat, je met ma question entre vos mains en espérant avoir une réponse pertinente.
Bon voilà, je cherche à avoir le résultat d'une formule sans le calcul comme suit:
A1=12 ; A2= 23 ; A3=A1+A2=35 et A4=A3*20%
C'est à dire avoir en A4=35*20% sans exécuter le calcul, comme ça on montre sur l'imprimé la manière dont est calculée la valeur dans A4.
Merci.
 

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour benabou, David, Patrick, le forum,

En fait la limitation de la plage a ne me plaisait pas.

Voici une bien meilleure solution pour éliminer les cellules vides des antécédents :

Code:
On Error Resume Next 's'il n'y a pas d'antécédents
Set dp = Cells.DirectPrecedents 'antécédents
Set a = dp.SpecialCells(xlCellTypeConstants)
Set b = dp.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If a Is Nothing And b Is Nothing Then Exit Sub
Set dp = Union(IIf(a Is Nothing, b, a), IIf(b Is Nothing, a, b))
Set a = dp(1): For Each c In dp.Areas: Set a = Range(a, c): Next
Ci-joint les 2 fichiers modifiés.

Bonne journée.
 

Pièces jointes

  • Formule Antécédents(4 ter).xlsm
    26.6 KB · Affichages: 27
  • Formule Antécédents(5 ter).xlsm
    36.6 KB · Affichages: 33
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Re,

Bien que benabou ne semble pas du tout branché sur les macros je continue :rolleyes:

Avec une autre méthode qui consiste a rechercher dans les formules les séparateurs des références.

C'est à dire (=+-*/^& ; version française ou (=+-*/^& , version anglaise.

Les références situées dans une autre feuille ou dans un autre classeur (ouvert) sont aussi converties *.

* Edit : s'il n'y a pas l'un des séparateurs dans le nom de la feuille ou du classeur.

Fichiers joints.

A+
 

Pièces jointes

  • Formule Séparateurs(1).xlsm
    28.9 KB · Affichages: 32
  • Formule Séparateurs(2).xlsm
    36.3 KB · Affichages: 37
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour le fil, le forum,

Pour compléter l'Edit de mon post précédent, on peut récupérer les références d'une autre feuille ou d'un classeur ouvert quels que soient leurs noms.

Il faut introduire le caractère "!" dans les séparateurs et ajouter ce code :

Code:
'------
              If Mid(t, j, 1) = "!" Then
                For k = j - 1 To 2 Step -1
                  If Mid(t, k, 5) = "#REF!" Or Mid(t, k, 1) = "\" Then _
                    i = k: GoTo 1 'si feuille supprimée ou autre classeur fermé
                  x = Mid(t, k, i - k + 1)
                  If TypeName(Evaluate(x)) = "Range" Then j = k - 1: Exit For
                Next
              End If
Edit : ajouté le cas où la feuille est supprimée.

Fichiers joints.

Bonne journée.
 

Pièces jointes

  • Séparateurs(1).xlsm
    30.9 KB · Affichages: 32
  • Séparateurs(2).xlsm
    40.2 KB · Affichages: 33
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour le forum,

Pour les pinailleurs (comme moi) il vaut mieux ces fichiers bis avec :

Code:
'-----
              If Mid(t, j, 1) = "!" Then
                If Mid(t, j - 4, 5) = "#REF!" Then i = j - 4: GoTo 1 'si feuille supprimée
                x = ""
                For k = j - 1 To 2 Step -1
                  If Mid(t, k, 1) = "\" And x = "" Then i = k: GoTo 1 'si autre classeur fermé
                  xtemp = Mid(t, k, i - k + 1)
                  If TypeName(Evaluate(xtemp)) = "Range" Then x = xtemp: j = k - 1
                Next k
              End If
Bonne journée.
 

Pièces jointes

  • Séparateurs(2 bis).xlsm
    36.2 KB · Affichages: 26
  • Séparateurs(1 bis).xlsm
    32.6 KB · Affichages: 31

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Re,

Pour finir (?) on peut récupérer la référence d'un classeur fermé avec ExecuteExcel4Macro :

Code:
'-----
              If Mid(t, j, 1) = "!" Then
                If Mid(t, j - 4, 5) = "#REF!" Then i = j - 4: GoTo 1 'si feuille supprimée
                If Mid(t, j - 5, 6) = "#REF'!" Then i = j - 5: GoTo 1 'd° autre classeur
                x = ""
                For k = j - 1 To 2 Step -1
                  If Mid(t, k, 1) = "\" And x = "" Then 'autre classeur fermé
                    While Mid(t, k, 1) <> "'": k = k - 1: Wend
                    x = Mid(t, k, i - k + 1): j = k - 1
                    y = ExecuteExcel4Macro(Application.ConvertFormula(x, xlA1, xlR1C1))
                    z = CStr(y)
                    If IsNumeric(y) Then z = UCase(z) 'pour les valeurs logiques
                    If IsError(y) Then z = "<" & z & ">"
                    GoTo 2
                  Else
                    xtemp = Mid(t, k, i - k + 1)
                    If TypeName(Evaluate(xtemp)) = "Range" Then x = xtemp: j = k - 1
                  End If
                Next k
              End If
              y = Evaluate(x): z = Evaluate(x).Text
2             z = IIf(IsNumeric(y) Or IsError(y), z, """" & z & """")
Edit : ajouté le cas de suppression de feuille sur autre classeur (#REF'!).

Fichiers joints, à vous de tester avec un autre classeur.

A+
 

Pièces jointes

  • Séparateurs(1 ter).xlsm
    33.8 KB · Affichages: 34
  • Séparateurs(2 ter).xlsm
    35.8 KB · Affichages: 41
Dernière édition:

david84

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour Gérard, le forum,

comme promis dans mon message #22, j'ai regardé (uniquement) ta dernière proposition.

Ci-joint quelques propositions (bien fondé à vérifier bien entendu de ton côté) :
Recherche du séparateur local : il me semble bien que Application.International(xlListSeparator) te le ramène (à vérifier quand même).
Si c'est le cas, plus besoin de celaux :
Code:
sep = "()=+-*/^& !" & Application.International(xlListSeparator)

Actuellement les noms définis tels que "cel" par exemple ne sont pas traités car la condition
Code:
If Not Flag And IsNumeric(Mid(T, i, 1)) Then
les écarte.
Si tu modifies la condition comme cela
Code:
If Not Flag Then
et que tu ajoutes dans les séparateurs la parenthèse sortante, tu traites le nom défini "cel" et tu continues de traiter ESTNUM(A1).

A noter que cette modification permet également à la macro Excel 4 de traiter ces noms définis dans un classeur fermé.
Cela entraîne donc des erreurs si le nom défini correspond à une zone et non à une cellule et l'erreur est ramené dans t.
Pour éviter cela tu peux remplacer
Code:
If IsError(y) Then z = "<" & z & ">"
par
Code:
If IsError(y) Then GoTo 1

Actuellement le traitement des plages nommées fait planter la macro à
Code:
y = Evaluate(x): z = Evaluate(x).Text
(teste en enlevant l'apostrophe devant la formule placée en A11 du fichier Séparateur_test_Job).
Tu peux éventuellement utiliser VarType pour éviter cela (j'ai utilisé la constante vbArray mais c'est juste pour voir) :
Code:
y = Evaluate(x) ': Debug.Print VarType(y)              
              If VarType(y) < vbArray Then
                z = Evaluate(x).Text
2               z = IIf(IsNumeric(y) Or IsError(y), z, """" & z & """")
                T = Left(T, j) & z & Mid(T, i + 1)
              End If

Ci-joint le fichier test (ton code en colonne B et le code modifié en colonne D) et un fichier "Test_nom_defini" permettant de tester sur un classeur ouvert ou fermé des cellules ou plages définies (notamment).

Ce ne sont que des propositions à tester de ton côté et bien sûr à ignorer/modifier/améliorer le cas échéant.

A+
 

Pièces jointes

  • Test_nom_defini.xls
    29.5 KB · Affichages: 36
  • Séparateur_test_Job_v2.xlsm
    40.9 KB · Affichages: 31
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonsoir David,

Application.International(xlListSeparator) est en effet la solution à adopter, elle fonctionne sur toute version.

Je n'ai pas du tout cherché à traiter les noms définis, ta solution complète donc la mienne.

Merci et bonne fin de soirée.
 

david84

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour Gérard,

Effectivement, ta procédure n'est pas faite pour le traitement des noms définis car sa structure ne le permet pas en l'état.
Même les modifications que je t'ai proposées ne traitent pas tous les cas (noms et plage définis du classeur actif, d'un autre classeur ouvert ou fermé).

J'ai donc cherché le moyen de séparer à l'aide d'une expression rationnelle les parties texte et les parties séparées par un opérateur ou séparateur de liste.

Il est ensuite plus facile de traiter individuellement chaque portion de la chaîne :
- les zones de texte et les plages de cellules sont ignorées
- les cellules et noms définis du classeur actif ou d'un autre classeur ouvert ou fermé sont traités
- le cas des feuilles manquantes est traité sans spécification particulière
- les cellules renvoyant du texte sont traitées différemment selon le fait que le texte est inscrit en dur dans la formule ou que la formule fasse appel à une zone de texte).
- le cas d'une cellule vide est traité.

Il doit y avoir des cas de figure non traités car c'est un test pour voir mais l'idée est là : à vérifier si elle est viable.

Il y a cependant une chose que je ne comprends pas : dans le classeur "Test_nom_defini.xls" placé dans mon précédent message figurent notamment deux plages définies nommées zone et zone_3.

Si le classeur est ouvert pas de problème.

S'il est fermé cette partie est traitée via ExecuteExcel4Macro.
Ce que je ne comprends pas c'est que cette méthode me renvoie une erreur pour "zone" (donc le chemin entier est renvoyé) alors qu'elle me renvoie le contenu de la première cellule de "zone_3".

As-tu une idée concernant cette différence de traitement ?

Si l'on veut tester : ouvrir d'abord le classeur "Test_nom_defini.xls", puis le classeur "Antécédents_reg", valider d'abord une des cellules de la colonne A avec le classeur "Test_nom_defini.xls" ouvert, puis revalider après avoir fermé ce classeur (les 2 cellules sont en orange).

A+

Edit : fichier retiré car lorsque je l'ouvre à partir du forum les liaisons vers le classeur test ne sont pas à jour...
 
Dernière édition:

david84

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Ci-joint le fichier épuré de toutes les liaisons vers un classeur fermé.
Je n'ai laissé les chemins que dans les deux formules dont j'ai parlé en plaçant une apostrophe devant.

A+
 

Pièces jointes

  • Antécédents _reg_v1.xlsm
    41.2 KB · Affichages: 37

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour David, le forum,

J'ai tenu compte de tes remarques.

Ce code fonctionne bien avec des noms définis dans le classeur, dans les feuilles ou dans un autre classeur :

Code:
Private Sub Worksheet_Calculate()
If Not CommandButton1.Caption Like "Annuler*" Then Exit Sub
Dim sep$, c As Range, t, i%, flag As Boolean, x$, j%, xtemp$, k%
Dim cf$, nblig&, nbcol%, y As Variant, z$
sep = "=()+-*/^&!" & Application.International(xlListSeparator)
Application.ScreenUpdating = False
Cells.ClearComments 'RAZ
For Each c In Me.UsedRange
  If c.HasFormula Then
    t = c.FormulaLocal
    For i = Len(t) To 2 Step -1
      If Not flag Then If Mid(t, i, 1) = """" Then flag = True: i = i - 1
      If flag Then If Mid(t, i, 1) = """" Then flag = False
      If Not flag And InStr(sep, Mid(t, i + 1, 1)) Then
        x = ""
        For j = i To 1 Step -1
          If InStr(sep, Mid(t, j, 1)) Then
            xtemp = Mid(t, j + 1, i - j)
            If TypeName(Evaluate(xtemp)) = "Range" Then _
              If Evaluate(xtemp).Count = 1 Then x = xtemp
            If Mid(t, j, 1) = "!" Then
              If Mid(t, j - 4, 5) = "#REF!" Then i = j - 4: GoTo 1 'si feuille supprimée
              If Mid(t, j - 5, 6) = "#REF'!" Then i = j - 5: GoTo 1 'd° autre classeur
              x = "" 'efface le x précédent
              For k = j - 1 To 2 Step -1
                If Mid(t, k, 1) = "\" Then 'autre classeur fermé
                  While Mid(t, k, 1) <> "'": k = k - 1: Wend
                  x = Mid(t, k, i - k + 1): j = k - 1
                  cf = Application.ConvertFormula(x, xlA1, xlR1C1)
                  nblig = 1: nbcol = 1
                  On Error Resume Next 'si la référence est un nom qui a été supprimé
                  nblig = ExecuteExcel4Macro("ROWS(" & cf & ")")
                  nbcol = ExecuteExcel4Macro("COLUMNS(" & cf & ")")
                  On Error GoTo 0
                  If nblig * nbcol > 1 Then i = j: GoTo 1
                  y = ExecuteExcel4Macro(cf)
                  z = CStr(y)
                  If IsNumeric(y) Then z = UCase(z) 'pour les valeurs logiques
                  If IsError(y) Then z = "<" & z & ">"
                  GoTo 2
                Else
                  xtemp = Mid(t, k, i - k + 1)
                  If TypeName(Evaluate(xtemp)) = "Range" Then _
                    If Evaluate(xtemp).Count = 1 Then x = xtemp: j = k - 1
                End If
              Next k
            End If
            If x <> "" Then
              y = Evaluate(x): z = Evaluate(x).Text
2             z = IIf(IsNumeric(y) Or IsError(y), z, """" & z & """")
              t = Left(t, j) & z & Mid(t, i + 1)
            End If
            i = j
            Exit For
          End If
        Next j
      End If
1   Next i
    Me.Activate
    c.AddComment t: c.Comment.Shape.TextFrame.AutoSize = True: c.Comment.Visible = True
  End If
Next c
End Sub
Les plages de plus d'une cellule sont évitées :

- dans les classeurs ouverts par le test Evaluate(xtemp).Count = 1

- dans un autre classeur fermé par les variables nblig et nbcol.

Edit : ajouté le cas où le nom est supprimé dans le classeur fermé.

Fichiers joints.

Bonne soirée.
 

Pièces jointes

  • Séparateurs(1-4°).xlsm
    36.6 KB · Affichages: 30
  • Séparateurs(2-4°).xlsm
    39.6 KB · Affichages: 33
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour David, le forum,

Au lieu de If IsError(y) Then z = "<" & z & ">" on peut expliciter l'erreur :

Code:
'----
                  If IsError(y) Then
                    Set celaux = Cells(1, Columns.Count).End(xlToLeft)(1, 2)
                    Application.EnableEvents = False
                    celaux = y: z = celaux.Text: celaux = ""
                    Application.EnableEvents = True
                  End If
David tu trouveras peut-être une méthode plus simple...

Fichiers joints.

Bonne journée.
 

Pièces jointes

  • Séparateurs(2-5°).xlsm
    38.8 KB · Affichages: 36
  • Séparateurs(2-5°).xlsm
    38.8 KB · Affichages: 37
  • Séparateurs(1-5°).xlsm
    36.9 KB · Affichages: 37

david84

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Bonjour Gérard,
un petit retour en coup de vent pendant la pause déjeuner (je regarderai plus attentivement cet après-midi) :
pour le test permettant de vérifier si le nom défini dans le classeur fermé correspond à une cellule ou une plage, c'est une excellente idée (c'est là-dessus que je bloquais).
Concernant le traitement d'un nom défini supprimé : si c'est le cas nblig et nbcol doivent ramener 0 non ? Donc si nblig=0 le nom défini a obligatoirement été supprimé normalement.

Si c'est le cas tu peux tester cela de ton côté pour voir :
Code:
cf = Application.ConvertFormula(x, xlA1, xlR1C1)                  
nblig = 0: nbcol = 0 'on repasse nblig et nbcol à 0
On Error Resume Next 'si la référence est un nom qui a été supprimé
nblig = ExecuteExcel4Macro("ROWS(" & cf & ")")
If nblig = 0 Then z = "#NOM?": GoTo 2 'on traite le nom supprimé
nbcol = ExecuteExcel4Macro("COLUMNS(" & cf & ")")
On Error GoTo 0
If nblig * nbcol > 1 Then i = j: GoTo 1
y = ExecuteExcel4Macro(cf)
z = CStr(y)
If IsNumeric(y) Then z = UCase(z) 'pour les valeurs logiques
GoTo 2
Pour le reste je regarde plus tard.

A+

Edit : effectivement, un nom défini supprimé peut afficher autre chose que "#NOM?"...
peut-être alors utiliser la valeur d'erreur affichée dans la cellule :
Code:
If nblig = 0 Then z = c.Text: GoTo 2
 
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Re David,

nlig et ncol sont déclarées Long et Integer.

Pour afficher la valeur d'erreur il faut les réinitialiser à 1 comme je l'ai fait.

Si l'on ne veut pas traiter l'erreur il faut les réinitialiser à 2.

A+
 

david84

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Re Gérard,
Pour afficher la valeur d'erreur il faut les réinitialiser à 1 comme je l'ai fait.

Si l'on ne veut pas traiter l'erreur il faut les réinitialiser à 2.
Je ne comprends pas trop...si nblig ramène 0 c'est le signe qu'il y a une erreur. Il me semble que le fait de les réinitialiser à 0 ne change rien puisque si le coup suivant tu traites un nom défini qui existe nblig et nbcol sont obligatoirement supérieurs à 0 non ?
En tout cas c'est comme cela que je l'ai utilisé : regarde le code et vois par toi-même si cela est ok ou pas.

Sinon j'ai placé dans le fichier joint les modifications qui pouvaient éventuellement être utiles mais bien entendu ne garde que ce qui te semble intéressant...et qui fonctionne (je n'ai pas non plus tester à fond) :
- traitement d'une cellule vide : une formule ramène 0 donc je pense qu'il faut en tenir compte, que ce soit dans le traitement simple d'une simple d'une cellule vide ou dans le cadre d'une formule contenant notamment une cellule vide (=SOMME(Cel;F1;A1) te ramenait =SOMME(100.50;;12.00) et non =SOMME(100.50;0;12.00)
- prise en compte du retour à la ligne lorsque tu traites des formules qui en contiennent (j'en vois qui les utilisent dans leurs formules)
- différenciation d'une formule contenant du texte d'une autre faisant appel à une cellule qui en contient (c'est une idée car lorsque tu affiches les formules dans une feuille, si tu as en A1 aaa, que tu tapes en A2 "aaa" et en A3 =A1 cela t'affiche en A2 ="aaa" et en A3 =A1 (or A1 contient aaa et non "aaa"). Mais bon à voir (c'est une idée, sans plus).
- prise en compte du signe "=" lorsqu'il est évalué :
Code:
=SOMME(SI(Page!C3:C5=Page!C4;Page!D3:D5))
ramenait
Code:
=SOMME(SI(Page!C3:C5"y";Page!D3:D5))
(le signe "=" n'était pas retourné).
Il y a sûrement une autre façon de régler cela (je suis allé au plus facile).

Peut-être que certaines idées te serviront.
J'essayerai de tester d'autres formules pour voir.

A+
 

Pièces jointes

  • Séparateurs(2-5°).xlsm
    34.8 KB · Affichages: 38
  • Séparateurs(2-5°).xlsm
    34.8 KB · Affichages: 39
Dernière édition:

job75

XLDnaute Barbatruc
Re : Résultat de formule sans la calculer

Re,

1) Pour corriger =SOMME(SI(Page!C3:C5"y";Page!D3: D5)) j'ai juste complété le test :

Code:
If TypeName(Evaluate(xtemp)) = "Range" And Left(xtemp, 1) <> "=" Then _
En effet Evaluate("=xxx") évalue "xxx".

Heureusement si un nom de feuille commence par "=" (ou le contient) Excel met le nom entre quotes.

2) Pour les cellules vides j'ai ajouté If z = "" Then z = "<vide>"

3) Pour tes aaa ce n'est pas logique, un texte doit toujours être renvoyé entre guillemets autrement cela indiquerait un nom défini.

Edit : en plus j'avais oublié "<" et ">" dans sep...

Fichiers corrigés joints.

Bonne nuit.
 

Pièces jointes

  • Séparateurs(1-6°).xlsm
    37.3 KB · Affichages: 36
  • Séparateurs(2-6°).xlsm
    41.7 KB · Affichages: 35
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 913
Membres
101 837
dernier inscrit
Ugo