[RESOLVED] Converting text to double

[Gymbo]

Hi,

I have a textbox control that contains the text "1.73205080756888" (sqrt(3)), I want to convert that to a double such that it's equal to the square root of 3 to 15 places.

dim x as double, y as double, z as variant

z = sqrt(3)
textbox.text = z
x = sqrt(3)
y = Cdbl(textbox.text)
debug.print x - y

The result is -2.88657986402541E-15 not 0

What am I doing wrong?

[Lenggries]

VB6 Doubles don't give you 15 digits of precision.

[jmsrickland]

z = 3 ^ (0.5)                     ' = 1.73205080756888 
TextBox.Text = z                  ' = "1.73205080756888"
x = 3 ^ (0.5)                     ' = 1.73205080756888
y = CDbl(TextBox.Text)            ' = 1.73205080756888
MsgBox x - y                      ' = -2.88657986402541E-15 
TextBox.Text = x - y              ' = "-2.88657986402541E-15"

Works for me. However, you would think it should be 0 because

x = 1.73205080756888
y = 1.73205080756888
x - y = 1.73205080756888 - 1.73205080756888 = 0

[Gymbo]

This is the code I used to test this situation.

Private Sub testtxt()
Dim x As Double, y As Double, z
z = Sqr(3)
x = Sqr(3)
txtXreg.text = z
y = CDbl(txtXreg.text)
Debug.Print x - y
Stop
End Sub

And this is the results I got:

-2.88657986402541E-15

[Gymbo]

VB6 Doubles don't give you 15 digits of precision.

How many digits does it give? More than 14, less than 16; I can't find an answer to that in the documentation.

[jcis]

You're losing precision in the implicit conversion Double->Variant->Text->Double. Specially the conversion to Text is the reason for the difference you get.

Also: is this VB6 or VBA? in VB6 it's sqr() not sqrt()

[jmsrickland]

How many digits does it give? More than 14, less than 16; I can't find an answer to that in the documentation.

Looks like 14

[jmsrickland]

This is the code I used to test this situation.

Private Sub testtxt()
Dim x As Double, y As Double, z
z = Sqr(3)
x = Sqr(3)
txtXreg.text = z
y = CDbl(txtXreg.text)
Debug.Print x - y
Stop
End Sub

And this is the results I got:

-2.88657986402541E-15

Then why did you ask what am I doing wrong?

[jmsrickland]

You're losing precision in the implicit conversion Double->Variant->Text->Double. Specially the conversion to Text is the reason for the difference you get.

Also: is this VB6 or VBA? in VB6 it's sqr() not sqrt()

Not so. I got the correct answer - see post #3

[Gymbo]

You're losing precision in the implicit conversion Double->Variant->Text->Double. Specially the conversion to Text is the reason for the difference you get.

Also: is this VB6 or VBA? in VB6 it's sqr() not sqrt()

I thought Variant would work the best since the value in the textbox could be almost anything.

I tried it as a String with the same results.

VB6, the sqrt() was a typo.

[jcis]

Not so. I got the correct answer - see post #3

x - y should be 0, not -2.88657986402541E-15

[Gymbo]

I just tried declaring z as a Double, got the same results, -2.88657986402541E-15

[Gymbo]

Then why did you ask what am I doing wrong?

Because the answer should be zero!

[dilettante]

VB & VBA use the same scalar types. See Data Type Summary, also INFO: Visual Basic and Arithmetic Precision

All of the numeric types are binary numbers, including even Decimal (VT_DECIMAL):

Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10

12 bytes of signed integer and 4 bytes of signed integer "power of 10" scale factor.

So since VB doesn't use decimal digits (even for Decimal data!) the number of "digits of precision" is always an approximation.

[jmsrickland]

Because the answer should be zero!

Oh, yeah. I missed that in your original post. I read it as you were getting 0 instead of -2.88657986402541E-15. You know, what is strange when I did a test the very first time I got an answer of 0 and I thought then that it was supposed to be 0 (which it should be) then I made some changes and then I got that very long number.

The below code produces 0

Private Sub Command1_Click()
Dim x As Double, y As Double, z As Variant

z = 1.73205080756888  ' =  3 ^ (0.5)  or sqr(3)
TextBox.Text = z
x = 1.73205080756888  ' = 3 ^ (0.5))  or sqr(3)
y = CDbl(TextBox.Text)
MsgBox x - y               ' answer is 0

TextBox.Text = x - y    ' answer is 0
End Sub

but the below code yields -2.88657986402541E-15

Private Sub Command1_Click()
Dim x As Double, y As Double, z As Variant

z =  3 ^ (0.5)
TextBox.Text = z
x = 3 ^ (0.5) 
y = CDbl(TextBox.Text)
MsgBox x - y 

TextBox.Text = x - y
End Sub

[DataMiser]

Very strange, I have no idea why this may be happening.

[jcis]

If we you do this:

Dim x as Double

 x = Sqr(3)

The value inside x is 1.7320508075688772 but if you show this value either with MsgBox or adding it to a TextBox or using the Inmediate Window or when you place the mouse pointer over x variable or whatever else you want that will convert this to Text, then the value you'll see is this: 1.73205080756888

So, no matter if it's an implicit or explicit conversion to text, when you try to see the value inside the Double variable you're already making an implicit conversion to text, the value you see is not the real value inside the variable.So when you convert x to String last 3 decimal positions are rounded as 8, see:

1.7320508075688772
1.73205080756888

And that is the reason for that value, here it's more clear:

MsgBox Sqr(3) - 1.73205080756888 'Result = -2,88657986402541E-15 
MsgBox 1.7320508075688772 - 1.73205080756888 = 'Result =  -2,88657986402541E-15 

Btw, +/- 2.88657986402541E-15 is a very small difference, insignificant for most Applications: ~= +/- 0,000000000000003 (aprox)

In the OP first post, all this happen when he converts to text:

textbox.text = z

[Gymbo]

The below code produces 0

Private Sub Command1_Click()
Dim x As Double, y As Double, z As Variant

z = 1.73205080756888  ' =  3 ^ (0.5)  or sqr(3)
TextBox.Text = z
x = 1.73205080756888  ' = 3 ^ (0.5))  or sqr(3)
y = CDbl(TextBox.Text)
MsgBox x - y               ' answer is 0

TextBox.Text = x - y    ' answer is 0
End Sub

but the below code yields -2.88657986402541E-15

Private Sub Command1_Click()
Dim x As Double, y As Double, z As Variant

z =  3 ^ (0.5)
TextBox.Text = z
x = 3 ^ (0.5) 
y = CDbl(TextBox.Text)
MsgBox x - y 

TextBox.Text = x - y
End Sub

In your first example did you just enter the 15 digit numbers, or did you calculate them?

Of course if you entered the numbers it would produce a zero (0).

[jmsrickland]

jcis,

Very nice explanation. Now I see what is going on where before I had no idea.

Gymbo,

Of course if you entered the numbers it would produce a zero (0).

Yeah, but I didn't know that. The reason it didn't make any sence to me was because when I did it the 2nd time I compared each step with what I entered explicitly the 1st time and they were exactly the same except when it got to the answer. Really blew my mind.

[Gymbo]

fwiw

Convert x and y to Strings [Cstr()], then convert them both back to Doubles [Cdbl()] seems to have solved my dilemma.

Thank you, everyone for your input.