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 E+14) to 922,337,203,685,477.5807 (+9.22 E+14)
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.
More info Numbers > Fixed Point
This data type uses 8 bytes
The default value is 0.
This data type allows you to save numbers in specific number formats

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

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 myCurrency As Currency 
myCurrency = 12345678901234567890
Call MsgBox(myCurrency)
myCurrency = 100.123456
Call MsgBox(myCurrency)

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 myVariant As Variant 
myVariant = Range("A1").Value
If TypeName(myVariant) = "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 myVariant As Variant 
myVariant = Range("A1").Value2
If TypeName(myVariant) = "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

Conversion Function

The CCUR function returns an expression converted to a Currency data type.


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