Andy Baron and Mary Chipman
Working with floating-point numbers frequently causes headaches for developers. In this article, Andy and Mary explain the new Decimal data type and how you can use it to avoid precision errors in floating-point computations.
WE like to think of computers as being highly accurate, but eventually all Access programmers discover that performing math with floating-point numbers can produce inaccurate results. Decimal fractions often don’t have exact binary equivalents, which can cause rounding errors to creep into VBA (or Access Basic) computations when Single or Double data types are used. Many programmers have elected to work around these problems by choosing the Currency data type because it’s a scaled integer. Integer scaling relies on the fact that all decimal whole numbers have exact binary equivalents-they can be exactly represented by some combination of powers of 2. The Currency scaled integer data type uses whole numbers to store fractions by multiplying the fractional part of the number by 10,000, eliminating the need for the decimal point, and then storing it internally in binary form as an integer. The down side of using the Currency data type is that it’s capable of only four digits of precision to the right of the decimal point.
VBA 3.0 in Access 97 introduces the Decimal data type as a promising alternative to the limitations of the Currency and floating-point (Single and Double) data types. The Decimal data type uses 14 bytes, which allows it to store numbers that can be much larger and more precise than Currency values, which are stored using only 8 bytes. The range of values that you can store using the Decimal data type is in inverse proportion to the number of decimal places of precision you need. As more decimal places are required, the available range gets smaller. At one extreme, you can store a number with 28 decimal places, but the number would have to fall within the very narrow range between approximately -8 and 8. At the other extreme, if you’re working with whole numbers that require no decimal places, the available range is approximately +/-8 * 1028 (that’s an 8 followed by 28 zeros). Using the Decimal data type isn’t straightforward. You can’t declare a variable ÒAs DecimalÓ-you have to declare it as a Variant and then convert it to the Decimal subtype using the CDec() function. The following procedure uses the VBA TypeName() function to identify the data type of the number and the CDec() function to convert a Double to the Decimal subtype. Floating-point errors exposed by repeatedly incrementing a fractional amount are eliminated when the fractions are converted to Decimal before being incremented:
Sub TestDataTypes()
Dim intI As Integer
Dim dblSum As Double
Dim varDblSum As Variant
Dim varDecSum As Variant
For intI = 1 To 10000
dblSum = dblSum + 0.0001
Next intI
Debug.Print TypeName(dblSum) & ":"; dblSum
'This prints, "Double: .999999999999906"
For intI = 1 To 10000
varDblSum = varDblSum + 0.0001
Next intI
Debug.Print "Variant " & TypeName(varDblSum) & _
":"; varDblSum
'This prints, "Variant Double: 0.999999999999906"
For intI = 1 To 10000
varDecSum = varDecSum + CDec(0.0001)
Next intI
Debug.Print "Variant " & TypeName(varDecSum) & _
":"; varDecSum
'This prints,"Variant Decimal: 1"
End Sub
In the February 1997 issue of Smart Access, Luke Chung demonstrated the errors that VBA can cause when performing subtractions, and he presented a function that avoids those errors by rounding the result to the appropriate number of decimal places. The following function shows how the Decimal data type can also be used to avoid subtraction errors:
Function SubtractFloatingPoint(dblNum1 As Double, _
dblNum2 As Double) As Double
Dim varDec1 As Variant
Dim varDec2 As Variant
' Examine the difference between the numbers.
Debug.Print "Subtraction with Doubles: "; _
(dblNum1 - dblNum2)
' Convert to Decimal data type using CDec function.
varDec1 = CDec(dblNum1)
varDec2 = CDec(dblNum2)
Debug.Print "Subtraction with Decimals: "; _
(varDec1 - varDec2)
SubtractFloatingPoint = (varDec1 - varDec2)
End Function
The following is the output from the Debug Window when the function is passed 1.001 and 1, which clearly shows the problem with subtracting floating point numbers. You’d expect the difference between 1.001 and 1 to be 0.001, but that isn’t the case:
? SubtractFloatingPoint(1.001, 1) Subtraction with Doubles: 9.9999999999989E-04 Subtraction with Decimals: 0.001
Using the CDec function to convert only one side of the subtraction also works, as shown by the following:
? 1.001-1 9.9999999999989E-04 ? CDec(1.001)-1 0.001 ? 1.001-CDec(1) 0.001
Although the third result might seem surprising, here’s what’s probably happening under the covers: The number that wasn’t explicitly converted using the CDec() function is converted to a decimal anyway when VBA performs the computation.
Be careful to use the CDec() function on the inputs to your computation and not just on the result. Converting the result won’t eliminate errors, as shown by the following example from the Debug Window:
? CDec(1.001-1) 0.00099999999999989
In this case the incorrect result had already been computed before CDec() was called.
Before the introduction of the Decimal data type, you had to write an algorithm to round values to the desired number of decimal points, unless the range and precision of the Currency type was adequate. The Decimal data type now can be used to avoid errors involving floating point numbers-unless you’re dealing with extremely large numbers or numbers with an extraordinary number of decimal places.
The down side of scaled integer data types, such as Currency and Decimal, is that they’re slightly less efficient in their use of memory because they need more bytes of storage, but you’ll gain efficiency by not having to write special code to handle rounding. If the Decimal ever graduates from being just a Variant subtype to being a full-fledged data type of its own, you may be able to avoid ever using troublesome floating-point numbers. s
Andy Baron, MCP, is president of Key Data Systems Inc. He enjoys solving business problems by designing and implementing custom database applications. In 1996 and 1997 Andy was named an Access Most Valued Professional for his contributions to Microsoft’s online support. Andy_Baron@msn.com.
Mary Chipman, MCP, MCT, MCSD, is a senior consultant with MCW Technologies. She is a co-author of Microsoft Access and SQL Server Developer’s Handbook and is a regular contributor to Smart Access. She has been recognized by Microsoft as an MVP on the MSACCESS forums on CompuServe, MSN, and the Microsoft Internet newsgroups. mchip@msn.com or 71760.243@compuserve.com.
To find out more about Smart Access and Pinnacle Publishing, visit their website at:
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the May 1997 issue of Smart Access. Copyright 1997, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.