Serious excel/vba bug!

[jasmith4]

My test case can be downloaded from http://home.comcast.net/~jasmith4/ExcelVBAroundBug/RoundBug.xls
It's an Excel 2003 file, but the bug shows up in Excel 2007 and 2010 too.

The bug has to do with VBA rounding numbers that end with one decimal digit, xxx.5 -- for example, if you round 0.5 you'll get 0, not 1, but if you round 1.5 you'll get 2! Here are a few more results using VBA's ROUND function -- yikes!

0.5 0
1.5 2
2.5 2
3.5 4
4.5 4
5.5 6
6.5 6
7.5 8
8.5 8
9.5 10

So I decided to do a little more testing, and here's how I composed the test case:
First I created a new XLS (Excel 2003) file, added a regular module to its VBAproject, and wrote the following code into it:

Option Explicit

Public Function RoundVBA(TestNumber#)
    RoundVBA = Round(TestNumber#, 0)
End Function

Public Function DoubleToInt&(TestNumber#) ' implicit conversion
   DoubleToInt& = TestNumber#
End Function

Public Function RoundClng&(TestNumber#)
   RoundClng& = CLng(TestNumber#)
End Function

Public Function RoundCint%(TestNumber#)
   RoundCint% = CInt(TestNumber#)
End Function

Public Function RoundInt(TestNumber#)
   RoundInt = Int(TestNumber#)
End Function

Public Function RoundFix(TestNumber#)
   RoundFix = Fix(TestNumber#)
End Function

Then in the UI's first tab I added 12 headings to the first row (A1:L1):

Test number --> A1
UI Round(#,0) --> A2, etc.
UI Int(#)
UI Fixed(#,0,True)
UI RoundUp(#,0)
UI RoundDown(#,0)
VBA Round(#,0)
VBA implicit conversion
VBA CLng(#)
VBA CInt(#)
VBA Int(#)
VBA Fix(#)

Next I added the following formulas to the second row (A2:L2):

-50.5 <-- this is my first test number, in A2
=ROUND($A2,0) --> A3
=INT($A2) --> A4, etc.
=VALUE(FIXED($A2,0,TRUE))
=ROUNDUP($A2,0)
=ROUNDDOWN($A2,0)
=RoundVBA($A2)
=DoubleToInt($A2)
=RoundClng($A2)
=RoundCint($A2)
=RoundInt($A2)
=RoundFix($A2)

Finally I entered -49.5 into cell A3, selected A2:A3, filled down to A103 (ending up with 50.5), and filled down all the formulas.

The results are startling!!

 

[jasmith4]

OK -- but when you don't document bankers' rounding at all, it's not good design!

As for FORMAT I always use FORMAT$ so I'm not dealing with Variants. And yes, I know about WorksheetFunction.Round, and I guess I'll have to use that from now on...

 

[GooberTron]

It's the default rounding method as per IEEE 754.

 

[Rick Rothstein]

Also, Mod operates only on Longs, and rounds by truncation.

I know the documentation says Mod rounds fractional portions by truncating, but that is not true. For example, each of these evaluate to 0...

1) 2.5 mod 2
2) 3.5 mod 2
3) 4 Mod 2.5
4) 4 mod 3.5

If truncation type rounding were in effect, then 2) and 4) would evaluate to 1. Also, 1) and 3) both show that the rounding method used is Banker's Rounding. As I indicated earlier, every place the rounding takes place in VB, Banker's Rounding is used, with the sole exception of the Format function. I think the writer of that help file may have been referring to a result from the Mod operation with fractional values as having those fractional values truncated, but that make absolutely no sense given that fractional numbers in the operands of the Mod operator were rounded before the Mod operator "did its thing" on them... the Mod operator cannot return a fractional value when its two operands are both whole numbers.