Conversion, Rounding, and Truncation

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

When you convert a decimal value to an integer value, Microsoft® Visual Basic® for Applications (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 is rounded down if the digit immediately to the left of the decimal place is even, and up if it is 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 are 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 might 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 cannot 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.

See Also

Working with Numbers | The Integer, Long, and Byte Data Types | The Boolean Data Type | The Floating-Point Data Types | The Currency and Decimal Data Types | Formatting Numeric Values | Using the Mod Operator | Performing Calculations on Numeric Arrays