bugs, A bigger piece of Pi

by **Robert Morley** » Fri, 15 Dec 2006 02:24:49 GMT

Okay, here's a really oddball quirk. This came up indirectly as a result of
a recent question in the Access Modules/Coding group, but it works equally
well in VB6 and VBA.

Enter the following into any public module:

Public Const Pi As Double = 3.14159265358979

Then, in the debug window or in code, type:

Debug.Print Pi - 3.14159265358979

You should get 0.

Okay, NOW change the constant declaration to:

Public Const Pi As Double = 3.141592653589793

VB6/VBA round it off and display it just like the first constant, BUT...

Debug.Print Pi - 3.14159265358979

...returns 3.10862446895044E-15 !!!

(The second constant returns the same value as Atn(1) * 4, and is a slightly
more accurate value, if you're worried about it at that level.)

Weird, huh?

Rob

by **Bob Butler** » Fri, 15 Dec 2006 02:29:42 GMT

<cut>

No, quite normal. http://support.microsoft.com/kb/42980

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

by **Albert D. Kallal** » Fri, 15 Dec 2006 02:39:17 GMT

Actally, one of the first lessons you lean in compter scicne is that "real"
numers on a computer are ONLY an aprpociaztion.

hence:

Public Sub TestAdd()

Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:

1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1

You can see that after just 7 addtions..already rounding is occuring

and if we add the follwing line of code to the end of the above:

if MyNumber = 10.1 = True then

msgbox "the number is 10.1"

else
msgbox "the number is somthing else"
endif

The above will actuall produce:

the number is something else

NOTE VERY careful in the above how the actual debug.print produced 10.1, and
DOES NOT show any rounding errors..but in fact the test condition "= 10.1"
FAILS!!!!

So, any serious software design has to take into account that real numbers
can't be represented exactly in a computer. For most stuff this is not a
problem, but when you write any software that will do financial
calculations, then you must be VERY aware of rounding issues, and that
single, or double numbers in a computer will rapidly result in rounding
problems. "3/10th" in a computer is only an approximation of 3 over 10. That
even applies to "1 over 10". You can't reliblairy use conditations on
single, or double numbers in code....they will simply fail more often then
not!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM

by **Robert Morley** » Fri, 15 Dec 2006 02:49:20 GMT

I'm quite familiar with floating-point issues, but I've never before noticed
VB storing additional invisible data, nor is it mentioned anywhere I could
see in that article.

In other words, if you enter...

Public Const Pi As Double = 3.141592653589793

...the last digit gets chopped off, but it still seems to be retained in
compilation. If you do anything that causes re-compilation (such as Access'
/decompile option), however, it won't be; the value reverts to what is
displayed.

Rob

by **Robert Morley** » Fri, 15 Dec 2006 02:51:26 GMT

See my response to Bob...I'm all too familiar with "normal" floating point
issues...that's not quite what I was getting at here.

Rob

by **Bob Butler** » Fri, 15 Dec 2006 03:12:59 GMT

Ahh, misunderstood the question. Double-precision floating point values are
good to 15 digits so that's the maximimum VB displays. You entered 16
digits which introduced "noise" at the end of the value that is suppressed
in the normal display but still exists when used in calculations. I'm not
sure it's specifically documented anywhere. If you save the code and exit
and restart VB the "noise" will be gone since the IDE will read in just the
15 digits recorded in the source code.

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

by **Robert Morley** » Fri, 15 Dec 2006 03:38:39 GMT

What's interesting about this is that when I tried it with Access, it
*remembered* the so-called noise until I forced a decompile. So
conceivably, you could run code one day and come up with one set of values,
then de/recompile it and come up with something totally different the next
day!

Rob

by **Bob O`Bob** » Fri, 15 Dec 2006 06:25:39 GMT

Entertaining, though.

If anyone still doesn't see it, try this example:

======
Option Explicit

Const a = 1.23456789012346
Const b = 1.23456789012346

Private Sub Form_Load()
MsgBox a = b
Unload Me
End Sub
======

+start a new VB project, and replace all form1 code with that.

+run it.
--> True, of course

+change ONE of the constant declarations by inserting a "5" just before the final "6"

--> the IDE will restore it to APPEAR the same as the other line

+run it.
--> False

Change the other const the same way, and it'll go back to True.

Once they BOTH have the "noise" in them, repeating the same alteration has no effect.
But change the final digit of one to something other than 6, and then back to 6,
and they become unequal again.

While in the "false" condition, I saved my project, then compiled it.
I named that one "False.exe"
I then closed the project - there was no prompt to save changes.
I reopened the project, and compiled to a file I named "True.exe"
Each executable generates the promised messagebox.
They are, in at least one sense, compiled from "identical" source.

Goofy. And, like I said - a little entertaining.

Bob
--

by **Tony Proctor** » Fri, 15 Dec 2006 18:26:08 GMT

Not sure I'd say it's "goofy" Bob. It's just another variation of the issues
around floating point storing a whole number of binary digits, rather than a
whole number of decimal digits.

In this particular instance, it could probably be classed as bug in the
IDE's "refinement" of the source code that you enter. You know, like when it
magically changes 1.0 to 1# for you, or #00:00:00# to #12:00:00 AM#, or even
strips the '()' off a call to Date (thus making it almost impossible to
reliably search for those calls). It's obviously re-generating the
floating-point value using default formatting rules, and so loosing
important partially-significant digits.

I can't see it getting fixed though <sigh>

Tony Proctor

the final "6"
no effect.
to 6,

by **Ken Halter** » Sat, 16 Dec 2006 03:10:19 GMT

This has nothing to do with your question... but, it's easy enough to get
rid of the need to remember or type the value of Pi

'====
Dim Pi As Double
Pi = Atn(1) * 4
Debug.Print Pi
'====

I must say <g>... that trianlge/circle would have to be gigantic for that
trailing "noise" to make a noticable difference.

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
In Loving Memory - http://www.vbsight.com/Remembrance.htm

by **Robert Morley** » Sat, 16 Dec 2006 13:00:43 GMT

Actually, somewhere in this chain I mentioned the Atn(1) * 4 formula.
Seeing as I memorized Pi to 100 decimal places in high school however (I was
a weird kid), remembering the value to a "mere" 14 isn't really a challenge,
even 20+ years later. :P

Rob

by **Robert Morley** » Sat, 16 Dec 2006 13:40:41 GMT

Oh, and as for big circles, the original post was about calculating points
of intersection around the earth, so yeah, I'd say that's a pretty big
circle (or surface of a sphere, actually). Just as a point of reference, I
remember hearing once that at 200 decimal places, you could calculate the
radius of a circle 12 light years across to the nearest molecule, if I'm
remembering correctly.

Anyway, as to the original problem (entitled "VBA Function returning
different values for same input" in m.p.a.modulescoding), what we figured
out is that variables being passed--which had underlying values retrieved
from a database that apparently had slightly greater precision than a Double
and were being trimmed down--were causing a fairly significant variance in
the return value of the function (50.38 vs. 50.15) compared to the same
parameters passed as constants. This turned out to be the "noise" on the
end of two of the variable parameters that created the discrepancy.

The noise at the end of Pi was not a significant factor in the final answer,
but it was a convenient value to show what was happening.

Rob

by **Tony Proctor** » Sun, 17 Dec 2006 20:22:13 GMT

LOL. I thought that I was odd remembering it to 50, and I still can't forget
them even now (lots of years down the road). 100 is pretty good!

Tony Proctor

was
challenge,

result
get
that

by **John Vinson** » Mon, 18 Dec 2006 04:04:02 GMT

On Sun, 17 Dec 2006 12:22:13 -0000, "Tony Proctor"

My math professor at Michigan State (in 1964) recounted how one
tiresome old professor would show off by writing down 100 digits of pi
at any (or no) excuse. My prof said he memorized the 101st through
110th digits and got up and added them on the next time he did so...

John W. Vinson[MVP]

by **Tim Ferguson** » Mon, 18 Dec 2006 04:53:04 GMT

"Robert Morley" < XXXX@XXXXX.COM > wrote in

From what I remember, this used to be called ill conditioning, and can
often be eliminated by adjusting the algorithm. Trivial example:

(a-b) * ab

becomes

(a*a*b) - (a*b*b)

so that the subtraction uses big numbers rather than close numbers. It
may be that there are robust methods for numeric solutions for the
distance-round-the-great-circle problems too. Etc.

All the best

Tim F

bugs, A bigger piece of Pi

bugs >> A bigger piece of Pi

bugs >> A bigger piece of Pi

bugs >> A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi

A bigger piece of Pi