2003.09.26 02:00 PM

VBA Round (again)

This post is superceded by VBA Round (groan). I believe you'll find it more complete, and certainly more accurate.

Quick addition to my prior post concerning the banker's rounding technique adopted by VB/VBA. Eric Lippert points out that VBScript also rounds this way.

I hadn't considered VBScript when writing my original post, because it was Access kicking my ass at the time. So, I looked to see whether VBScript's FormatNumber function suffers the same inconsistency (that's probably too harsh?) as the VBA.Strings.Format and FormatNumber functions by using asymmetric arithmetic rounding instead of the banker's rounding found in CInt, CLng, and Round. It does. (Maybe VBScript's FormatNumber just wraps a call to VBA.Strings.FormatNumber? Who knows.)

Here's a little VBScript to illustrate the difference:

wscript.echo FormatNumber(1.5, 0), CInt(1.5), FormatNumber(2.5, 0), CInt(2.5)


wscript.echo FormatNumber(1.5, 0), Round(1.5, 0), FormatNumber(2.5, 0), Round(2.5, 0)

Either way, the result is:

2 2 3 2

While we're looking, here's a line of VB.NET that does the same thing:

console.WriteLine("{0:0} {1} {2:0} {3}", 1.5, CInt(1.5), 2.5, CInt(2.5))

Using CType(x, Integer) instead of CInt(x) would get you the same result.

Of course, in C# you'll get no rounding with a cast:

Console.WriteLine("{0:0} {1} {2:0} {3}", 1.5, (int)1.5, 2.5, (int)2.5);

This produces:

2 1 3 2

Anyhow, just to be clear, I'm not complaining. As I originally posted, and as Mr. Lippert explains, banker's rounding is more accurate over lots of numbers. The different techniques used by different languages and/or libraries is just something else to remember. Or, like me, to forget, Google, and then remember (again).


Mr. Lippert was kind enough to add some additional insider information concerning this topic:


Thanks so much.

ewbi.develops | 2003.10.03 02:07 AM


TrackBack URL:  https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d834211cac53ef

Listed below are links to weblogs that reference VBA Round (again):