XL 2016 VBA - Excel et ses décimales extravagantes

[Dudu2]

Bonjour,

Je sais que le sujet des nombres, de leur représentation interne, de leurs effets de bord est un sujet récurrent.
Cependant j'aimerais avoir votre avis dans le cadre le valeurs comptables, donc de nombres à 2 décimales représentant les centimes.

Première chose, pour être sûr de ne pas récupérer des décimales extravagantes dans les calculs j'utilise maintenant cette fonction:

'---------------------------------------------------
'Convertit un nombre en nombre à 2 décimales exactes
'pour éliminer les décimales extravagantes d'Excel
'---------------------------------------------------
Function Nombre2Décimales(Nombre As Variant) As Double
    Nombre2Décimales = CDbl(Format(Nombre, "0.00"))
End Function

Je ne sais pas si c'est suffisant ou s'il vaut mieux passer par un entier long que je n'arrive pas à représenter sur 64 bits malgré mon Office 64 et CPU 64.

Un exemple de décimales extravagantes obtenues en VBA sur le fichier joint.

 

[chris]

Bonjour

Pourquoi pas plûtot Round(Nombre,2)

 

[sylvanu]

Bonjour CP4,
Effectivement c'est un sujet récurrent.
Cet exemple donne 1,164153E-10

Sub a()
    With ActiveSheet
        a2 = .[a2].Value
        b2 = .[b2].Value
        .[D2].Value = a2 - b2
    End With
End Sub

Cet autre donne 0

Sub b()
    Dim a2 As Long, b2 As Long
    With ActiveSheet
        a2 = .[a2].Value
        b2 = .[b2].Value
        .[D2].Value = a2 - b2
    End With
End Sub

Dans le second ex, on force le VBA a calculer avec des Long, dans le premier il fait ce qu'il veut. On peut alors retrouver un résiduel dû à la résolution des formats choisis.
Seul Long offre la meilleure résolution. Rien, Double ou Single laisse un résiduel.

 

[Dudu2]

Bonjour @chris, @sylvanu,

Pourquoi pas plûtot Round(Nombre,2)

Oui pourquoi pas en effet, c'est pour ça que je vous sollicite pour avoir des idées différentes.

on force le VBA a calculer avec des Long

Ok, mais si tu passes par des Long, ce sont des entiers et tu perds les décimales.
En fait j'envisageais aussi de passer par des entiers en faisant CLng(valeur * 100) / 100.
Mais un entier sur 32 bits ça ne fait guère que 2 milliards de centimes soit 200 millions, chiffre qui peut être dépassé en compta. Et je ne sais pas déclarer un entier Long (LongPtr ?) de 64 bits utilisable en calculs.

 

[sylvanu]

Re,
Alors passez par Round :

.[D2].Value = Round(.[A2].Value - .[B2].Value, 2)

Le problème est résolu.

 

[Dudu2]

Oui, passer par un Round, c'est sûrement ce que je vais faire.
Merci pour vos retours utiles.

 

[Dranreb]

Bonjour.
Rappel: en VBA vous disposez du type Currency pour les calculs.
Il est codé comme un entier sur 64 bits valant 10000 fois sa valeur conventionnelle
Ceci donne bien 0 :

Sub a()
    With ActiveSheet
        .[D2].Value = CCur(.[A2].Value) - CCur(.[B2].Value)
    End With
End Sub

Malheureusement en interne il est quand même codé en Double, même s'iI trichera pour arriver à restituer ultérieurement la .Value en Currency.
Remarque: c'était la B2 qui contenait la valeur la plus proche possible de -202752,56 soit -202752,5599999999976716935634613037109375 (=-3483262457521111 / 2^34)
La A2 est donc une copie par valeur d'un résultat de calcul.

 

[Dudu2]

Bonjour @Dranreb,
Ah oui je me souviens t'avoir lu mentionnant ce type de donnée.
Très intéressant pour la compta. Merci.

 

[Dudu2]

La A2 est donc une copie par valeur d'un résultat de calcul.

Exact.
Comment as-tu fait pour voir cette valeur que le format standard n'affiche pas ?

 

[Dranreb]

J'ai un classeur d'analyse fine des valeurs de cellules et d'informations utiles aussi en VBA

 

[Dudu2]

C'est toi qui as fait ça ?

 

[Dranreb]

Bien évidemment !

 

[mapomme]

Bonjour à tous,

Il me semble que même avec des arrondis à 2 chiffres, il y a des cas où on ne trouvera pas le résultat attendu.

Exemple :

• on dispose d'un certain nombre de valeurs (ex : 2, 3, 4)
• on calcule pour chacune des valeurs sa proportion par rapport au total
• si on arrondit à deux chiffres les proportions, leur somme a une propension non négligeable à refuser de vouloir égaler exactement 100

Faut faire avec...

 

[Dranreb]

Conseil: Pour vérifier si deux valeurs de cellules, par exemple A2 et B2, obtenues par des voies différentes sont fonctionnellement égales utilisez =ABS(A2-B2)<1E-12

 

[mapomme]

Conseil: Pour vérifier si deux valeurs de cellules, par exemple A2 et B2, obtenues par des voies différentes sont fonctionnellement égales utilisez =ABS(A2-B2)<1E-12

Bonjour @Dranreb ,

Oui mais dans mon cas, cela n'empêche pas un affichage souvent de l'ordre de 99,99% qui "défrise" l'utilisateur (surtout le financier et le contrôleur de gestion )