Numerical accuracy in Form Text-boxes

  • Thread starter Thread starter IM
  • Start date Start date
I

IM

I use a text-box in a form to enter numerical data. When I enter a
number, say, 14.6, in the text-box and then have the VBA code transfer
the data to my spreadsheet. Sometimes, but not always, the number shown
on the spreadsheet is not exactly 14.6, but 14.59999987..

Of course, I could add a few lines to the code to round the number off
to 2 or 3 decimal places. But there must a better way. What is it ? Why
does this happen ?
 
I use a text-box in a form to enter numerical data. When I enter a
number, say, 14.6, in the text-box and then have the VBA code transfer
the data to my spreadsheet. Sometimes, but not always, the number shown
on the spreadsheet is not exactly 14.6, but 14.59999987..

Of course, I could add a few lines to the code to round the number off
to 2 or 3 decimal places. But there must a better way. What is it ? Why
does this happen ?
Click to expand...

It happens because VBA of precision limits in VBA and/or Excel; and due to the
fact that certain numbers cannot be represented exactly in binary. An example
in decimal arithmetic would be the attempt to represent 1/3 in decimal format.

There are several solutions depending on your goals:

Use the DECIMAL data type.
Round.
Use an appropriate number format in the Excel cell (this would leave the result
as 14.5999999... but display it as 14.60).


--ron
 
Ron,

Thanks for this info. It helps explain why I find strange values in my axis
limits for my charts. (I see the same effect there) Have been using round
functions and stuff to avoid it when changing the axis settings by code.
 
Ron

Thanks for the reply. I do know exactly how numbers are stored and the
cause of round-off errors. But what irks me is the problem of
consistency. What I mean by that is if I enter 14.6 in a worksheet cell,
the problem I mentioned will never happen. But if the number is
extracted from a textbox via VBA, it does.

Apparently, Excel by itself works fine in this respect. Only its
interfacing with VBA that will bring up the problem. I just wonder if
that's an inherent problem with Visual Basic. If yes, Microsoft could
handle that easily, since there is no problem with that within Excel.

IM
 
Ron

Thanks for the reply. I do know exactly how numbers are stored and the
cause of round-off errors. But what irks me is the problem of
consistency. What I mean by that is if I enter 14.6 in a worksheet cell,
the problem I mentioned will never happen. But if the number is
extracted from a textbox via VBA, it does.

Apparently, Excel by itself works fine in this respect. Only its
interfacing with VBA that will bring up the problem. I just wonder if
that's an inherent problem with Visual Basic. If yes, Microsoft could
handle that easily, since there is no problem with that within Excel.

IM
Click to expand...

Could there be an issue in your code?

If I enter 14.6 into an input box, assign it to a variable which has been dim'd
as double, and then print it, I get 14.6

Very simply:

=============
Sub test()
Dim c As Double
c = InputBox("Enter a number")
Debug.Print c
End Sub
============

It seems as if VBA is handling 14.6 OK in this simple routine.


--ron
 
Back
Top