When you convert a decimal value to an integer value it gets rounded to the nearest integer value.
Digits less than 5 are rounded down
Digits greater than 5 are rounded up
If the digit is 5, then it looks at the digit immediately before the decimal place
1) if its even, its rounded down
2) if its odd, its rounded up
3) if there is no digit, its rounded down
CLng(8.5) = 8 (because 8 is even)
CLng(9.5) - 10 (because 9 is odd)
More info Datatypes > Conversion
The Floating-Point Data Types
Microsoft® Visual Basic® for Applications (VBA) provides two floating-point data types, Single and Double. The Single data type requires 4 bytes of memory and can store negative values between -3.402823 x 1038 and -1.401298 x 10-45 and positive values between 1.401298 x 10-45 and 3.402823 x 1038. The Double data type requires 8 bytes of memory and can store negative values between -1.79769313486232 x 10308 and -4.94065645841247 x 10-324 and positive values between 4.94065645841247 x 10-324 and 1.79769313486232 x 10308.
The Single and Double data types are very precise — that is, they make it possible for you to specify extremely small or large numbers. However, these data types are not very accurate because they use floating-point mathematics. Floating-point mathematics has an inherent limitation in that it uses binary digits to represent decimals. Not all the numbers within the range available to the Single or Double data type can be represented exactly in binary form, so they are rounded. Also, some numbers cannot be represented exactly with any finite number of digits — pi, for example, or the decimal resulting from 1/3.
Because of these limitations to floating-point mathematics, you might encounter rounding errors when you perform operations on floating-point numbers. Compared to the size of the value you are working with, the rounding error will be very small. If you do not require absolute accuracy and can afford relatively small rounding errors, the floating-point data types are ideal for representing very small or very large values. On the other hand, if your values must be accurate — for example, if you are working with money values — you should consider one of the scaled integer data types.
The Scaled Integer Data Types
The two scaled integer data types, Currency and Decimal, provide a high level of accuracy. These are also referred to as fixed-point data types. They are not as precise as the floating-point data types—that is, they can't represent numbers as large or as small. However, if you can't afford rounding errors, and you don't require as many decimal places as the floating-point data types provide, you can use the scaled integer data types. Internally, the scaled integer types represent decimal values as integers by multiplying them by a factor of 10.
The Currency data type uses 8 bytes of memory and can represent numbers with fifteen digits to the left of the decimal point and four to the right, in the range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
The Decimal data type uses 12 bytes of memory and can have between 0 and 28 decimal places. The Decimal data type is a Variant subtype; in order to use the Decimal data type, you must declare a variable of type Variant, and then convert it by using the CDec function.
The following example shows how to convert a Variant variable to a Decimal variable. It also demonstrates how using the Decimal data type can minimize the rounding errors inherent in the floating-point data types.
' This procedure demonstrates how using the
' Decimal data type can minimize rounding errors.
Dim dblNum As Double
Dim varNum As Variant
Dim lngCount As Long
' Increment values in loop.
For lngCount = 1 To 100000
dblNum = dblNum + 0.00001
' Convert value to Decimal using CDec.
varNum = varNum + CDec(0.00001)
Debug.Print "Result using Double: " & dblNum
Debug.Print "Result using Decimal: " & varNum
The procedure prints these results to the Immediate window:
Result using Double: 0.999999999998084
Result using Decimal: 1
The DoubleVsDecimal procedure is available in the modNumbers module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.
A Note About Division
Any time you use the floating-point division operator (/), you're performing floating-point division, and your return value will be of type Double. This is true whether your dividend and divisor are integer, floating-point, or fixed-point values. It's true whether or not your result has a decimal portion.
For example, running the following code from the Immediate window prints "Double":
So does this code, even though the result is an integer:
Since all floating-point division returns a floating-point value, you can't ever be certain that your result is accurate to every decimal place, even if you're performing division on Decimal or Currency values. There will always be an inherent possibility of rounding errors, although they're likely to be small.
If you're dividing integers, or if you don't care about the decimal portion of the result, you can use the integer division operator (\). Integer division is faster than floating-point division, and the result is always an Integer or Long value, either of which requires less memory than a Double value. For example, running this code from the Immediate window prints "Integer":