Rounding issue for real number is a well know problem for any programming language (link).
Single/Double (link)
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. 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 (link)
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.
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: " & varNumEnd SubThe procedure prints these results to the Immediate window:
Result using Double: 0.999999999998084
Result using Decimal: 1