Number discrepency

[Ian]

I have a userform to prompt users to enter certain data before being allowed
access to a sheet for further data entry.

On this form I have a textbox for a software version in the format x.xx

The data in this textbox is then entered in a cell on the sheet using the
line below.

If UserForm1.ComboBox1.Value <> "Other" Then Worksheets("Ziehm").Range("L2")
= CSng(TextBox1.Value) 'Software version

This works well except that the value transferred is not exactly the same as
that entered

eg I entered 4.16 in the textbox and, although 4.16 is shown in the sheet,
this is only due to cell formatting. The actual value transferred is
4.1599998474121.

Can anyone a) explain why, and b) suggest alternative/additional code such
that what is in the text box is transferred exactly? I'm more concerned with
the solution than the explanation, but it would be interesting to know why.

Due to other code this value MUST be a number.

TIA

 

[Guest]

the solution is to change

csng( )
to
cdbl( )

numbers in Excel are stored as Double.

When you convert the single to the double, you get the loss of precision
(demo'd from the immediate window):

? csng("4.16")
4.16
? cdbl("4.16")
4.16
? cdbl(csng("4.16"))
4.15999984741211

The root cause probably includes the storage specification of IEEE double
precision numbers.

 

[Ian]

Easy when you know how

Thanks Tom.