| Microsoft Office 2000/Visual Basic Programmer's Guide | |
When you convert a decimal value to an integer value, VBA rounds the number to an integer value. How it rounds depends on the value of the digit immediately to the right of the decimal place—digits less than 5 are rounded down, while digits greater than 5 are rounded up. If the digit is 5, then it's rounded down if the digit immediately to the left of the decimal place is even, and up if it's odd. When the digit to be rounded is a 5, the result is always an even integer.
For example, running the following line of code from the Immediate window prints "8," because VBA rounds down when the number immediately to the left of the decimal is even:
? CLng(8.5)
However, this code prints "10," because 9 is odd:
? CLng(9.5)
If you want to discard the decimal portion of a number, and return the integer portion, you can use either the Int or Fix function. These functions simply truncate without rounding. For example, Int(8.5) returns 8, and Int(9.5) returns 9. The Int and Fix functions behave identically unless you're working with negative numbers. The Int function rounds to the lower negative integer, while the Fix function rounds to the higher one.
For example, the following code evaluates to "-8":
? Fix(-8.2)
Using the Int function, on the other hand, yields "-9":
? Int(-8.2)
Note The Int and Fix functions always return a Double value. You may want to convert the result to a Long value before performing further operations with it.
VBA includes a new rounding function called Round, which you can use to round a floating-point or fixed-point decimal to a specified number of places. For example, the following code rounds the number 1.2345 to 1.234:
? Round(1.2345, 3)
Although the Round function is useful for returning a number with a specified number of decimal places, you can't always predict how it will round when the rounding digit is a 5. How VBA rounds a number depends on the internal binary representation of that number. If you want to write a rounding function that will round decimal values according to predictable rules, you should write your own. For more information, see the Visual Basic Language Developer's Handbook by Ken Getz and Mike Gilbert (Sybex, 1999).