Currency

This is a value data type.
Use the Currency data type to store numbers in the range from -922,337,203,477.5808 (-9.22 E14) to 922,337,203,685,477.5807 (+9.22 E14)
You should use this data type when you do not want any rounding errors.
Currency is exact, Single and Double are rounded.
This data type is useful for calculations in which accuracy is particularly important.
These are stored as 64 bit (8 byte) numbers in an integer format, scaled by 10,000 to give a fixed point number.



This data type uses 8 bytes
The default value is 0.
This data type allows you to save numbers in specific number formats

Dim mynumber As Currency 
mynumber = 922337203685477.5807@
Call MsgBox(mynumber)

The @ at the end of the second line is a Variable Abbreviation Character


This data type has an accuracy of 15 digits to the left of the decimal point.
This data type has an accuracy of 4 digits to the right.

Dim mynumber As Currency 
mynumber = 12345678901234567890
Call MsgBox(mynumber)
mynumber = 100.123456
Call MsgBox(mynumber)


Floating Point Numbers

Examples are: Single, Double and Date
You can use floating point to save the value of number.
This method is called floating point because the the decimal point floats.
This method represents a number in an approximate way to a certain number of significant digits.
This is then scaled using an exponent.


Fixed Point Numbers

Examples are: Currency, Decimal
You can use fixed point to save the value of a number.
This method has a fixed decimal point position and therefore always has a fixed number of digits before and after the decimal point.




Losing Precision

You will lose precision if you convert a Decimal value with 5 decimal places to a variable of type Currency.
Use the Currency data type to avoid rounding errors when precision is of the utmost importance.


Excel Round Trip

Enter the number 100.123456 into cell "A1" and format this with a currency number format

Dim mynumber As Variant 
mynumber = Range("A1").Value
If TypeName(mynumber) = "Currency" Then
   Call MsgBox("currency")
End If

This number is converted into a Currency data type.
This automatic data type conversion of a Double into a Currency is not a good thing and can lead to a loss of precision when putting the value back into Excel.
If you use the Value2 property then this conversion does not take place.


Dim mynumber As Variant 
mynumber = Range("A1").Value2
If TypeName(mynumber) = "Double" Then MsgBox("double")


Public Sub RunCurrency() 
Dim myCurrency As Currency
   myCurrency = 1

   Do
      On Error GoTo ErrorHandler
      myCurrency = myCurrency * 2
      Debug.Print myCurrency
   Loop

ErrorHandler:
   Stop
End Sub



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