Les mystères (?) de mon EXCEL 2002

[Fo_rum]

Salut,

Je sens bien, qu'en ce moment, je suis fatigué mais quand même !

Que dois-je modifier dans ma version EXCEL 2002 pour que cette macro, qui fonctionne avec des nombres entiers, fonctionne aussi avec des nombres à virgule ?

Private Sub Somme_Click()
  [C5] = ""
  If [A5] = [A3] + [A4] Then
    [C5] = "'=" & [A3] & " + " & [A4]
  End If
End Sub

Merci.

remarque : les expressions du genre [A5].value, Range("A5") avec .Value ou pas, Cells(5,1) avec .Value ou non ne me permettent pas de régler le problème !

 

[kjin]

Re : Les mystères (?) de mon EXCEL 2002

Bonjour,

Private Sub Somme_Click()
  [C5] = ""
  If Evaluate([A3] + [A4]) = [A5] Then
    '[C5].Formula = "=A3+A4"
    'ou
    [C5] = Evaluate([A3] + [A4])
  End If
End Sub

A+
kjin

 

[CB60]

Re : Les mystères (?) de mon EXCEL 2002

Bonjour

Sub Somme_Click()
  [C5] = ""
  If Evaluate([A3] + [A4]) = [A5] Then
    [C5] = "=TEXT(""=A3+A4"",)"
  End If
End Sub

si j'ai bien compris??

 

[Fo_rum]

Re : Les mystères (?) de mon EXCEL 2002

Re,

Merci pour vos réponses mais, ce n’est pas une correction de la macro que je désire !
Pourquoi avec [A5]=15,3, [A3]=9,6 et [A4] = 5,7

ou avec [A5]=9.978, [A3]=9,465 et [A4] = 0,513
​

If [A5] = [A3] + [A4] Then … est bien pris en compte mais pas

avec [A5]=10,19, [A3]=9,55 et [A4] = 0,64 ?
​

Dans ce cas,
If Round([A5], 2) = Round([A3] + [A4], 2) Then … convient, comme Evaluate !

J'ai donc différentes possibilités pour surmonter ce problème.

Mais problème : pourquoi If [A5] = [A3] + [A4] Then … coince certaines fois ?

 

[nolich]

Re : Les mystères (?) de mon EXCEL 2002

Bonsoir Fo_rum, kjin et CB60, bonsoir à toutes et à tous 🙂

Pourquoi avec [A5]=15,3, [A3]=9,6 et [A4] = 5,7

ou avec [A5]=9.978, [A3]=9,465 et [A4] = 0,513
​

If [A5] = [A3] + [A4] Then … est bien pris en compte mais pas

avec [A5]=10,19, [A3]=9,55 et [A4] = 0,64 ?
​

Dans ce cas,
If Round([A5], 2) = Round([A3] + [A4], 2) Then … convient, comme Evaluate !

J'ai donc différentes possibilités pour surmonter ce problème.

Mais problème : pourquoi If [A5] = [A3] + [A4] Then … coince certaines fois ?

J'ai le même résultat avec XL 2003 😕

Il y a un souci de type de données ou de conversion car cela fonctionne lorsque je mets les cellules A3 à A5 en format monétaire avec le symbole € (le premier de la liste seulement, semble-t-il) et en format comptabilité avec également le premier symbole € de la liste.

Si on laisse en format standard ou même nombre, peu importe le nombre de décimales), cela ne donne plus le bon résultat, sauf à écrire :

If CSng([A5]) = [A3] + [A4] Then...

... et cela donne le bon résultat dans les exemples que tu proposes. Il semblerait que [A5] soit converti en nombre entier 😕 😕

Surprenant !!! Et je n'ai pas encore trouvé d'explication dans l'aide.

@+

 

[eriiic]

Re : Les mystères (?) de mon EXCEL 2002

Bonjour,

Parce qu'en vba [A5]-[A3]-[A4] n'est pas égal à 0 mais à -1.38E-17

Comme tu n'utilises que 2 chiffres après la virgule tu peux utiliser currency qui est prévu justement pour éviter ces erreurs qui ne serait pas acceptable dans des comptes.
En interne currency travaille sur des entiers (en entrée les nombres sont *10000, puis /10000 en sortie, et affichés sur 2 décimales)

Private Sub Somme_Click()
    Dim r As Currency
    [C5] = ""
    r = [A5] - [A3] - [A4]
    If r = 0 Then
        [C5] = "'=" & [A3] & " + " & [A4]
    End If
End Sub

eric

 

[Fo_rum]

Re : Les mystères (?) de mon EXCEL 2002

Re,

...
Parce qu'en vba [A5]-[A3]-[A4] n'est pas égal à 0 ...
...

pas toujours justement !
Il est bien question de précision mais dans quels cas 🙁 ?

Ta solution demande une transformation du code avec l'intégration d'une variable.
Avec la remarque de nolich on peut aussi passer par une modification ainsi

Private Sub Somme_Click()
  [C5] = ""
  If [A5] = [A3] + [A4] + [A2] Then
    [C5] = "'=" & [A3] & " + " & [A4]
  End If
End Sub

à condition d'avoir [A2]=0 et en format "Monétaire (€)" !

Currently, I'm disapointed, Bill !

 

[eriiic]

Re : Les mystères (?) de mon EXCEL 2002

pas toujours justement !
Il est bien question de précision mais dans quels cas 🙁 ?

Quand tu travailles en flottant il y toujours des pertes, insignifiantes mais suffisantes pour échouer parfois à un test... Donc soit tu arrondis, soit tu travailles en entier ou en currency.
eric

 

[nolich]

Re : Les mystères (?) de mon EXCEL 2002

Re, bonsoir ou bonjour Eric, bonjour à toutes et à tous 🙂

Quand tu travailles en flottant il y a toujours des pertes, insignifiantes mais suffisantes pour échouer parfois à un test... Donc soit tu arrondis, soit tu travailles en entier ou en currency.

pas toujours justement !

Eric, j'ai souligné le "toujours" qui est un peu exagéré après recherche sur le site du support de MS à ce sujet :

L'arithmétique en virgule flottante peut fournir des résultats incorrects dans Excel

Il semble que Fo_rum soit justement tombé sur un des cas particuliers des nombres à virgule flottante. Une partie de cet article nous explique la précision de ces nombres particuliers. En voici quelques passages :

Microsoft Excel a été conçu sur la base de la spécification IEEE 754 en ce qui concerne le stockage et le calcul de nombres à virgule flottante

[...]

Précision
Un nombre à virgule flottante est stocké au format binaire en trois parties dans une plage de 65 bits : le signe, l'exposant et la mantisse.

1 bit de signe | 11 bits d'exposant | 1 bit impliqué | 52 bits de mantisse

Le signe stocke le signe du nombre (positif ou négatif), l'exposant stocke la puissance de 2 à laquelle le nombre est élevé (la puissance maximale de 2 est +1 023 et la puissance minimale de 2 est –1 022) et la mantisse stocke le nombre lui-même. La zone de stockage finie de la mantisse limite le degré de proximité de deux nombres à virgule flottante adjacents (c'est-à-dire la précision).

La mantisse et l'exposant peuvent tous deux être stockés en tant que composants séparés. Par conséquent, le degré de précision possible peut varier selon la taille du nombre (de la mantisse) à manipuler. Dans le cas d'Excel, bien qu'Excel puisse stocker des nombres de 1,79769313486232E308 à 2,2250738585072E-308, il ne peut le faire qu'avec une précision de 15 chiffres. Cette limitation est le résultat direct d'un strict respect de la spécification IEEE 754 et non une limitation d'Excel. Ce degré de précision est le même que celui trouvé dans d'autres tableurs.

[...]

Nombres binaires répétitifs et calculs dont les résultats sont proches de zéro
Un autre problème lié au stockage des nombres à virgule flottante au format binaire est que certains nombres, qui sont finis et non répétitifs en base décimale, deviennent des nombres infinis et répétitifs en base binaire. L'exemple le plus connu de ce problème est la valeur 0,1 et ses variantes. Bien que ces nombres puissent être représentés parfaitement en base décimale, le même nombre en format binaire devient le nombre binaire répétitif suivant lorsqu'il est stocké dans la mantisse :
000110011001100110011 (et ainsi de suite)
La spécification IEEE 754 ne contient pas de disposition spéciale pour un nombre spécifique ; elle stocke ce qu'elle peut dans la mantisse et tronque le reste. Ceci résulte en une erreur d'environ -2,8E-17, ou 0,000000000000000028, lors du stockage.

Même les fractions décimales courantes, telles que le nombre décimal 0,0001, ne peuvent pas être représentées exactement en format binaire. (0,0001 est une fraction répétitive binaire avec une période de 104 bits). Ceci est similaire à la raison pour laquelle la fraction 1/3 ne peut pas être représentée exactement en base décimale (nombre décimal répétitif 0,33333333333333333333).

En supprimant les listes de validation dans le fichier de Fo_rum, et sachant que 0,05 - 0,10 et 0,15 (de A3 à A5) donne un résultat erroné, on peut essayer les nombres 0,05 - 0,20 et 0,25 qui donnent un résultat correct en passant par la procédure Somme_Click.

De même, 0,09 - 0,06 et 0,15 donnent également un résultat correct lorsque qu'on active la moulinette Somme_Click, alors que 0,08 - 0,07 et 0,15 ne fonctionnent pas !

Conclusion : même si ce n'est pas toujours faux, il faut prendre les FPN (Floating Point Numbers) pour ce qu'ils sont : des représentations informatiques (donc binaires et limitées) de nombres décimaux qu'il nous paraît si simple de manipuler.

@+

 

[eriiic]

Re : Les mystères (?) de mon EXCEL 2002

Bonjour,

Bien évidemment pas toujours, on s'en serait apercu.
Ce que je voulais exprimer c'est qu'à partir du moment où on ne peut pas déterminer dans quel cas ça se produira, quand c'est inacceptable il faut en tenir compte.
Fo rum voulait savoir pourquoi, ça c'est fait.
Maintenant il veut savoir quand et là il n'y a pas de réponse ou plutôt une infinité de réponses.

eric

 

[Fo_rum]

Re : Les mystères (?) de mon EXCEL 2002

Salut,

merci à tous pour vos propositions et explications.
Selon le Principe de Précaution, il me faut donc modifier la macro et j'ai retenu l'introduction une variable de type Currency.

Cela m'a donné l'occasion de traiter un autre sujet : ici en précisant que je ne suis ni Maître ni Seigneur du VBA (dont les voies sont impénétrables voire inénarrables 😱).

 

[eriiic]

Re : Les mystères (?) de mon EXCEL 2002

Re,

bien vu d'avoir fait le lien.
Ca fiabilise cette macro et on peut supposer que les réponses sont maintenant exhaustives.
eric