Fixed Point Numbers

Also known as Scaled Integer data types
Examples are: Currency, Decimal

These can provide a high level of accuracy.
They are not as precise as the floating-point data types - that is, they can't represent numbers as large or as small.
This method has a fixed decimal point position and therefore always has a fixed number of digits before and after the decimal point.
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.

Currency Data Type

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.

Variant/Decimal Data Type

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)

Debug.Print "Result using Double: " & dblNum
Debug.Print "Result using Decimal: " & varNum
End Sub

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

Debug.Print TypeName(2.34/5.9) 

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

Debug.Print 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":

Debug.Print TypeName(9\3) 

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext