Incrementing textbox value up and down in vba does not cancel evenly

Question

I can't figure out why my code isn't maintaining significant digits when incrementing a textbox value.

I have a spin control (textbox plus two small command buttons to move textbox value up or down in value) on a form.

The textbox default value is zero.

The up arrow command button should increment the textbox value by + 0.1. Here is the code:

Private Sub cmdIndexSpinUp_Click()
If Me!txtIndexSpin >= 1.5 Then
    MsgBox "The maximum Index adjustment has been reached."
    Exit Sub
Else
    Me!txtIndexSpin = Me!txtIndexSpin + 0.1
End If
End Sub

The down arrow command button should increment the textbox value by -0.1. Here is the code:

Private Sub cmdIndexSpinDown_Click()
If Me!txtIndexSpin <= -1.5 Then
    MsgBox "The minimum Index adjustment has been reached."
    Exit Sub
Else
    Me!txtIndexSpin = Me!txtIndexSpin - 0.1
End If
End Sub

So I would expect that from the default value of 0, if I spin up once and down once, I should return to 0. That works fine. If I spin up twice and then down twice, my textbox value suddenly becomes 2.77555756156289E-17 instead of 0.

After more testing, it does not consistently happen based on the number of clicks, but it may be related to the time between clicks. The more rapid, the more prone to this error it seems.

How could this be happening?

I am going to code around it, since I see nothing wrong, but am curious what I am missing.

Seems to me this is similar to [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) I'm not sure whether it's a duplicate, though. What more do you need? — HansUp, Aug 11 '16 at 17:09
As others have pointed out, this is a float point math issue. You might choose to avoid this by using a Currency or Decimal datatype (http://stackoverflow.com/documentation/vba/3418/data-types-and-limits) which allow greater precision for decimal numbers to really avoid any possibility of roundoff errors. Be aware that they're somewhat slower computationally. — Mikegrann, Aug 11 '16 at 17:19
@HansUp That did the trick. Shameful that I am just now learning this! — MoondogsMaDawg, Aug 11 '16 at 17:20
@ChristopherD. Be especially aware of this for non-deterministic code like intensive computations in a multithreaded process. If you do the same algorithm every time, but because of the multithreading you add different floating point numbers in different orders, you'll get a different result due to roundoff errors! — Mikegrann, Aug 11 '16 at 17:25

score 3 · Accepted Answer · edited May 23 '17 at 12:32

As @HansUp says floating points aren't precise. Source. An easy solution that you could use is to round the number before putting it in the text box.

Example :

Private Sub cmdIndexSpinDown_Click()
    Dim value As Double
    value = Me!txtIndexSpin
    If value <= -1.5 Then
        MsgBox "The minimum Index adjustment has been reached."
        Exit Sub
    Else
        value = Round(value - 0.1, 1)
        Me!txtIndexSpin = value
    End If
End Sub

Private Sub cmdIndexSpinUp_Click()
    Dim value As Double
    value = Me!txtIndexSpin
    If value >= 1.5 Then
        MsgBox "The maximum Index adjustment has been reached."
        Exit Sub
    Else
        value = Round(value + 0.1,1)
        Me!txtIndexSpin = value
    End If
End Sub

score 1 · Answer 2 · answered Aug 11 '16 at 17:48

Round() is a reasonable solution for your floating point precision problem. And it may well be exactly what you want. However, be aware that you will be using "banker's rounding", sometimes called "round to even". So you might not get what you expect from rounding to 1 decimal place when the second decimal place is 5:

? Round(1.15, 1)
 1.2 
? Round(1.25, 1)
 1.2

If that is not what you want, you could use a different rounding strategy. Or you could switch to integer math instead of floating point math ... and then the floating point precision challenge goes away. That might sound challenging, but it's actually simple to implement. Add a hidden text box to your form and use it like this ...

Private Sub cmdIndexSpinDown_Click()
    With Me!txtHidden
        If .Value <= -15 Then
            MsgBox "The minimum Index adjustment has been reached."
        Else
            .Value = .Value - 1
            Me!txtIndexSpin.Value = .Value / 10
        End If
    End With
End Sub

If txtIndexSpin is bound to a field in the form's Record Source, you can load txtHidden from the form's Current event:

Me!txtHidden.Value = Me!txtIndexSpin.Value * 10

And if you also allow the users to edit txtIndexSpin directly (not just via those command buttons), do that again from its After Update event.

Incrementing textbox value up and down in vba does not cancel evenly

2 Answers2