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

--
Ian
--

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.

--
Regards,
Tom Ogilvy




"Ian" wrote:

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

--
Ian
--

Easy when you know how :-)

Thanks Tom.

--
Ian
--
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy




"Ian" wrote:

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

--
Ian
--