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.

 

[Dranreb]

L'arrondir règle en général le problème

 

[Dudu2]

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.

C'est certain mais à condition que les décimales ignorées aient une valeur significative.
Or dans le cas des "décimales extravagantes" ce sont des décimales infinitésimales qui sont non significatives. Donc on peut s'en passer et conserver un résultat cohérent. Enfin je le vois comme ça.

 

[Dudu2]

Pour info voici les élucubrations que j'ai du réaliser pour venir à bout (peut- être) de ce problème.

1er - Déterminer si la cellule contient un nombre

'----------------------------------------------
'Retourne True si la cellule contient un nombre
'----------------------------------------------
Function CelluleContientNombre(Cellule As Range) As Boolean
    Dim Valeur As Variant
    Dim ErrNumber As Long
 
    'Couvre le dépassement de capacité sur la simple référence à Cellule.Value (oui ça arrive !)
    On Error Resume Next
    Valeur = Cellule.Value
    ErrNumber = Err.Number
    On Error GoTo 0
 
    If ErrNumber Then Exit Function
    CelluleContientNombre = EstNombre(Valeur)
End Function

'-------------------------------------
'Retourne True si valeur est un nombre
'-------------------------------------
Function EstNombre(Valeur As Variant) As Boolean
    If VarType(Valeur) = vbDouble _
    Or VarType(Valeur) = vbCurrency _
    Or (VarType(Valeur) = vbString And IsNumeric(Valeur)) Then EstNombre = True
End Function

2ème - Test VBA des décimales extravagantes

'
----------------------------------------------------------------
'Recherche et éventuelle modification des décimales extravagantes
'----------------------------------------------------------------
Private Sub Cherche(WS As Worksheet, ByRef NbCasValues As Long, ByRef NbCasFormulas As Long, Optional Corrige As Boolean = False)
    Dim Cellule As Range
 
    'Initialisations
    NbCasValues = 0
    NbCasFormulas = 0
 
    With WS
        '-----------------------------------------------------
        'Recherche des décimales extravagantes dans la feuille
        '-----------------------------------------------------
        For Each Cellule In .UsedRange.Cells
            If CelluleContientNombre(Cellule) Then
                'If Cellule.Value <> CCur(Cellule.Value) Then
                'If Cellule.Value <> Round(Cellule.Value, 2) Then
                'Pour avoir une cohérence avec la MFC
                If CStr(Cellule.Value) <> CStr(Round(Cellule.Value, 2)) Then
                    If Cellule.HasFormula Then
                        NbCasFormulas = NbCasFormulas + 1
                    Else
                        NbCasValues = NbCasValues + 1
                        If Corrige Then Cellule.Value = Round(Cellule.Value, 2)
                    End If
                End If
            End If
        Next Cellule
    End With
End Sub

3ème - La formule de la MFC

Private Const MFC_FormulaLocal = "=SI(ESTNUM(A1);SI(A1<>CNUM(TEXTE(A1;""0,00""));VRAI;FAUX);FAUX)"

 

[Dranreb]

Juste une observation: VarType d'une propriété Value d'un Range d'une seule cellule ne peut être vbNull, vbObject, vbDataObject ni vbUserDefinedType. Par contre il peut être vbString.
Les seuls numériques possibles sont vbDouble et vbCurrency.

 

[Dudu2]

Merci pour cette info.
Faudrait-il que je ne teste que les VarTypes:
vbInteger
vbLong
vbSingle
vbDouble
vbCurrency
vbString (+IsNumeric)
vbDecimal
pour déterminer si la valeur est un nombre soit en texte (+ IsNumeric) soit un nombre ?

 

[Dranreb]

Non, les autres type numériques que Double et Currency ne sont pas utilisés par Excel. Ils sont certes compris d'Excel en entrée, mais seulement réellement stockés en Double et éventuellement restitué en Currency si la cellule porte un format monétaire ou comptable.

 

[Dudu2]

Ok, merci. Corrigé la fonction EstNombre() du Post #18.