Balneus

Australian Lefty on Politics, Governance, Science and Info Management

MS VB thinks 9.72 – 3.16 – 6.56 does not equal 0

Posted by Dave Bath on 2008-03-13


I hate Microsoft.  For some reason, it can’t deal with 3 digit, 2 decimal point numbers, thinking that 9.72 is not the sum of 3.16 and 6.56.  MS VB thinks that 3.5 rounds UP, but 2.5 rounds DOWN!!  Don’t believe me?  Look at the code and results over the fold.

Screen dump (large) or read text below the fold.

Environment: XP Professional, Excel 2003. Ran the following macro

Sub a()
x = 0
x = 9.72 - 3.16 - 6.56
MsgBox " Straight Subtraction of 9.72 - 3.16 - 6.56 = " & x
x = (9.72 <> (3.16 + 6.56))
MsgBox "(9.72 <> (3.16 + 6.56)) = " & x
MsgBox CDbl(9.72) - CDbl(3.16) - CDbl(6.56)
MsgBox "CSng(9.72)-CSng(3.16)-CSng(6.56) = " & CSng(9.72)-CSng(3.16)-CSng(6.56)
MsgBox 9.72 - 3.16 - 6.56
End Sub

Well, the boolean came back as True, because 9.72 does not equal (3.16 + 6.56) according to Microsoft, when everything was cast to Single Precision, the answer (rather than the correct 0) was 2.384186E-7, otherwise the answer (including when specifically cast to Double Precision) was 8.88178419700124E-16.

So, let’s make it really simple with the following code, again Excel 2003

MsgBox "CInt(3.6)= " & CInt(3.6)
MsgBox "CInt(3.5)= " & CInt(3.5)
MsgBox "CInt(3.4)= " & CInt(3.4)
MsgBox "CInt(2.6)= " & CInt(2.6)
MsgBox "CInt(2.5)= " & CInt(2.5)
MsgBox "CInt(2.4)= " & CInt(2.4)

Here are the results:

Input Output
3.6 4
3.5 4
3.4 3
2.6 3
2.5 2
2.4 2

Now figure THAT out!!!  Tried it on another machine, and using MS-Access: Same weirdness!  Use the immediate window and Debug.Print rather than MsgBox. Same weirdness!!!!

Moral of the story: don’t trust MS-Office with any code or formulae.  Have you any idea how long it took me to think "maybe I’ll see if the math handling inside VB is buggy rather than my Oracle database or the ODBC driver"?

Aaaah, thank goodness I use OpenOffice for my own work!!!

Has anybody else got anything like this?

Let’s try the following code and use debug immediate window:

Sub a()
Debug.Print "CInt(3.6)= " & CInt(3.6)
Debug.Print "CInt(3.5)= " & CInt(3.5)
Debug.Print "CInt(3.4)= " & CInt(3.4)
Debug.Print "CInt(2.6)= " & CInt(2.6)
Debug.Print "CInt(2.5)= " & CInt(2.5)
Debug.Print "CInt(2.4)= " & CInt(2.4)
Debug.Print 9.72 - 3.16 - 6.56
Debug.Print (9.72 <> (3.16 + 6.56))
Debug.Print "(9.72 (3.16 + 6.56)) = " & (9.72 <> (3.16 + 6.56))
Debug.Print CDbl(9.72) - CDbl(3.16) - CDbl(6.56)
Debug.Print " CDbl(9.72) - CDbl(3.16) - CDbl(6.56) = " & CDbl(9.72) - CDbl(3.16) - CDbl(6.56)
Debug.Print "CSng(9.72) - CSng(3.16) - CSng(6.56) = " & CSng(9.72) - CSng(3.16) - CSng(6.56)
End Sub

Now, cutting and pasting from the immediate window after the run:


CInt(3.6)= 4
CInt(3.5)= 4
CInt(3.4)= 3
CInt(2.6)= 3
CInt(2.5)= 2
CInt(2.4)= 2
8.88178419700125E-16
True
(9.72 <> (3.16 + 6.56)) = True
8.88178419700125E-16
CDbl(9.72) - CDbl(3.16) - Dbl(6.56) = 8.88178419700125E-16
CSng(9.72) - CSng(3.16) - CSng(6.56) = 2.384186E-07


Screen Image of MS XL VB innumeracy

Advertisements

2 Responses to “MS VB thinks 9.72 – 3.16 – 6.56 does not equal 0”

  1. PollyCyclic said

    How many Microsoft lovers would see that this is a problem?

  2. […] Another example of MS B.A.D. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: