vba incorrectly entering cell values from single variable

Question

I have the task of entering the weight of every product onto our company's system (over 65,000), so am using Excel and a slightly limited knowledge of vba to automate that where possible.

The plan is, I input part of a product name and then the figure to be entered, and it enters that figure in all the relevant rows on the sheet.

The problem; the number I enter is seldom more than 2 decimal places, however the figure entered on the sheet is not the same - always very nearly there, but not quite. For example when I just tried to enter 0.88 in relevant cells, it entered 0.87999995.

Code (simplified):

Sub EnterWeight()

Dim Filter As String
Dim Weight As Single

Filter = InputBox("Add text filter", "Add Filter")

w = InputBox("Insert weight in Kg", "Enter Weight", 1) 
'(yes, I know it should be 'mass in Kg', but ... ¯\_(ツ)_/¯ )

Weight = CDec(w)

Debug.Print Weight 'To test that it's the correct number, always seems to be ok.

    For b = 1 To Activesheet.UsedRange.Rows.Count
        If Cells(b, ) Like "*" & Filter & "*" Then 'Find the filter in any part of the cell

            If Cells(b, 2) <> "" And Cells(b, 2).Value <> SG Then 'Cells already populated with a different value

            y = MsgBox("Product """ & Cells(b, 1).Value & _
                """ already has a weight assigned of " & _
                Cells(b, 2).Value & Chr(13) & _
                "OverWrite?", vbYesNo + vbExclamation, _
                "Weight already assigned")
                If y = vbYes Then Cells(b, 2).Value = Weight

            Else
                Cells(b, 2).Value = Weight
            End If

        End If

    Next
End sub

Can anyone tell me why that wouldn't enter the Weight variable correctly in the relevant cells? hunting through google doesn't seem to have produced an answer, although perhaps I'm just asking the wrong thing.

Many Thanks in advance

Answer 1

This issue can be minimised down to:

Sub test1()
    Dim Weight As Single
    Weight  = InputBox("Insert weight in Kg", "Enter Weight", 1)
    Cells(2, 2).Value = Weight
End Sub

Entering ".88" into the input box causes the cell to receive "0.879999995231628"

However, changing the Weight to Double:

Sub test2()
    Dim Weight As Double
    Weight  = InputBox("Insert weight in Kg", "Enter Weight", 1)
    Cells(2, 2).Value = Weight
End Sub

Entering ".88" into the input box causes the cell to receive "0.88"

---

Check out the answer to VBA Double vs Single rounding for a very detailed explanation of why this happens.