Microsoft Office 2000/Visual Basic Programmer's Guide

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.

Sub DoubleVsDecimal()
   ' 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)
   Next
   
   Debug.Print "Result using Double: " & dblNum
   Debug.Print "Result using Decimal: " & varNum
End Sub

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":

? TypeName(2.34/5.9)

So does this code, even though the result is an integer:

? TypeName(9/3)

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":

? TypeName(9\3)