The Currency and Decimal Data Types

  • Article

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.

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 cannot represent numbers as large or as small. However, if you cannot afford rounding errors, and you do not 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; 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

A Note About Division

Any time you use the floating-point division operator (/), you are 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 is 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)

Because all floating-point division returns a floating-point value, you cannot be certain that your result is accurate to every decimal place, even if you are performing division on Decimal or Currency values. There will always be an inherent possibility of rounding errors, although they are likely to be small.

If you are dividing integers, or if you do not 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)

See Also

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