Virgule flotante
Invité
Bonjour à tous,
Je rencontre un problème de virgule flottante.
Alors j'ai un code qui me permet de copier des données d'un fichier vers en un autre en faisant quelques calculs.
Mon code fonctionne bien, mais par curiosité j'ai décidé de l'envoyer dans Chat GPT pour voir ce qui pouvait en sortir.
Je sais j'ai brisé la règle "if it ain't broken don't fix it".
Bref je post quand même ici si quelqu'un pouvait m'aider à mieux comprendre.
Private Sub FillInvoice2()
Dim TVA_1 As Single, TVA_2 As Single, HT As Single, TTC As Single, TVA As Single, HT_1 As Single, HT_2 As Single
Dim TotalDocuments As Integer, i As Integer, y As Integer, PaymentRow As Integer, InvoiceRow As Integer, TVAErrorCount As Integer
Dim ClientCode As Variant
Dim HT_Array As Variant, TVA_Array As Variant
Dim FindString As String
Dim FoundRange As Range
TVA_1 = wbmacro.Sheets("Notice").Cells(29, 3).Value
TVA_2 = wbmacro.Sheets("Notice").Cells(30, 3).Value
TVA_Array = Array(TVA_1, TVA_2)
TotalDocuments = wbinvoice.Sheets(1).UsedRange.Rows.Count
PaymentRow = 2
InvoiceRow = 2
TVAErrorCount = 0
'For i = 2 To TotalDocuments
For i = 2 To 10
If Left(wbinvoice.Sheets(1).Cells(i, 2).Value, lenghtfacture) = prefixefacture And wbinvoice.Sheets(1).Cells(i, 6).Value <> 0 Then
' Calculate HT and TTC for invoice
HT = wbinvoice.Sheets(1).Cells(i, 6).Value * 100
TTC = wbinvoice.Sheets(1).Cells(i, 7).Value * 100
MsgBox (HT & " fill invoice 2")
' Handle zero TVA case
If TTC - HT = 0 Then
FillInvoiceRow i, InvoiceRow, HT
InvoiceRow = InvoiceRow + 1
CreatePayment i, PaymentRow, TTC
PaymentRow = PaymentRow + 1
Else
' Calculate invoice TVA
TVA = TTC - HT
TVA = Round(TVA / HT * 100, 2)
' Handle rounded TVA values
TVA = GetRoundedTVA(TVA, TVA_Array)
' Handle single TVA rate case
If TVA = TVA_1 Or TVA = TVA_2 Then
FillInvoiceRow i, InvoiceRow, HT, TVA
InvoiceRow = InvoiceRow + 1
CreatePayment i, PaymentRow, TTC
PaymentRow = PaymentRow + 1
Else
' Calculate HT for both TVA rates
HT_2 = Round((TTC - (HT * (1 + TVA_1 / 100))) / (TVA_2 / 100 - TVA_1 / 100), 2)
HT_1 = HT - CDbl(HT_2)
HT_Array = Array(HT_1, HT_2)
' Create multi-rate invoices
For y = 0 To 1
HT = HT_Array(y)
FillInvoiceRow i, InvoiceRow, HT, TVA
' Check if calculated TVA equals document TVA
If TVA = CDbl(Round(HT_1 * TVA_1 / 100 + HT_2 * TVA_2 / 100, 2)) Then
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 33).Value = TVA_Array(y)
Else
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 33).Value = "TVA à verifier"
TVAErrorCount = TVAErrorCount + 1
End If
InvoiceRow = InvoiceRow + 1
Next y
End If
End If
If wbinvoice.Sheets(1).Cells(i, 8).Value <> TTC Then
' Create payments
CreatePayment i, PaymentRow, TTC
PaymentRow = PaymentRow + 1
End If
End If
Next i
If TVAErrorCount >= 1 Then
MsgBox ("Des erreurs sont présentes dans la colonne (AG) taux de TVA, pensez à vérifier les factures liées")
End If
End Sub
Private Function GetRoundedTVA(TVA As Single, TVA_Array As Variant) As Single
Dim y As Integer
For y = 0 To 1
If Abs(TVA - TVA_Array(y)) <= 0.1 Then
TVA = TVA_Array(y)
End If
Next
GetRoundedTVA = TVA
End Function
Private Sub FillInvoiceRow(ByVal i As Integer, ByVal InvoiceRow As Integer, ByVal HT As Single, Optional ByVal TVA As Single)
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 1).Value = wbinvoice.Sheets(1).Cells(i, 2).Value
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 2).Value = CDate(wbinvoice.Sheets(1).Cells(i, 3).Value)
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 3).Value = wbinvoice.Sheets(1).Cells(i, 4).Value
FindClientCode wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 3).Value, wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 4), InvoiceRow
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 12).Value = wbinvoice.Sheets(1).Cells(i, 5).Value
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 18).Value = "A réception"
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 28).Value = "NC"
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 29).Value = 1
MsgBox (Round(HT, 2))
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 31).Value = HT / 100
If Not IsMissing(TVA) Then
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 33).Value = TVA
End If
End Sub
Private Sub FindClientCode(ByVal searchString As String, ByRef outputCell As Range, ByVal InvoiceRow As Integer)
Dim rngfound As Range
With wbclient.Sheets(1).Range("C:C")
Set rngfound = .Find(searchString, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngfound Is Nothing Then
outputCell.Value = wbclient.Sheets(1).Cells(rngfound.Row, 1).Value
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 4) = outputCell.Value
End If
End With
End Sub
Private Sub CreatePayment(ByVal i As Integer, ByVal PaymentRow As Integer, ByVal TTC As Single)
wbgabarit_paiement.Sheets(1).Cells(PaymentRow, 1).Value = wbinvoice.Sheets(1).Cells(i, 2).Value
wbgabarit_paiement.Sheets(1).Cells(PaymentRow, 2).Value = CDate(wbinvoice.Sheets(1).Cells(i, 3).Value)
wbgabarit_paiement.Sheets(1).Cells(PaymentRow, 6).Value = "Règlement client"
wbgabarit_paiement.Sheets(1).Cells(PaymentRow, 7).Value = "Autres"
wbgabarit_paiement.Sheets(1).Cells(PaymentRow, 8).Value = wbinvoice.Sheets(1).Cells(i, 9).Value
End SubLe problème se situe dans le sub FillInvoiceRow.
Pour les lignes
MsgBox (Round(HT, 2))
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 31).Value = HT / 100J'ai placé une msgbox pour être certain de la valeur HT que j'ai à ce moment.
Quand j'attribue la variable HT à ma cellule dans la ligne suivante c'est à ce moment que je rencontre mon problème de virgule flottante.
J'ai essayé de multiplier les données par 100 avant de la diviser par 100, d'arrondir le résultat à 2 chiffres même problème.
Quand je lance ma macro ma première valeur pour le HT est de 63,16 tout le long même dans le msgbox et pourtant dans ma cellule j'obtiens 63,159999998.
Invité
Bon j'ai creusé encore avec chat GPT. et la correction est la suivante:
wbgabarit_invoice.Sheets(1).Cells(InvoiceRow, 31).Value = CDbl(format(HT, "0.00"))