I've inherited a project from another VBA developer. It deals a lot with cost (price) calculations, but I notice that the original developer has used Double for prices, rather than Currency. I've read that Currency should be used for money, but I'm not sure if and why it would matter in this project. The figures are up to 1 billion, and a precision of 6 decimals is enough. Maybe Double can handle that just as well as Currency? Wondering if I would gain anything by using Currency in this case.

Gustaf

hi
currency data type will only handle up to 4 decimal while double data type
has a floating decimal(will handle more decimals). both are 64 bit (8 bytes).

in vb help look up data type summary, double data type and currency data
type for more details.


Regards
FSt1

"Gustaf" wrote:

I've inherited a project from another VBA developer. It deals a lot with cost (price) calculations, but I notice that the original developer has used Double for prices, rather than Currency. I've read that Currency should be used for money, but I'm not sure if and why it would matter in this project. The figures are up to 1 billion, and a precision of 6 decimals is enough. Maybe Double can handle that just as well as Currency? Wondering if I would gain anything by using Currency in this case.

Gustaf

"Gustaf" wrote:
I've read that Currency should be used for money

I would not say that, exactly. The name of the type is a misnomer.

The key difference is: Currency arithmetic is "exact", like any integer,
whereas Double arithmetic results in approximate decimal fractions in most
cases.

Currency is essentially a 63-bit integer (plus a sign bit). For display
purposes, the number is divided by 10,000, so it appears to have 4 decimal
places. But that is misleading.

Currency can handle numbers with 6 decimal places, for example; but you must
scale the numbers yourself by multplying by 100. You must also scale any
Double values, if you mix them with Currency values that effectively have
more than 4 decimal places.

Double is a floating-point real number (variable number of decimal places).
Effectively, it has only 53 bits of precision -- less than Currency.
However, it can have almost any number of decimal places and it can handle
almost any magnitude of numbers, for practical purposes, with some potential
loss of accuracy depending on the magnitude and number of decimal places.

Which to use is a difficult trade-off to make.

Consider the number 5.123456.

' using Double
dX# = 5.123456
Msgbox dX#

' using Currency
cyX@ = 5.123456 * 100
Msgbox cyX@ & " " & cyX@ / 100

Suffice it to say, Currency is more difficult to deal with if you want to
have more than 4 decimal places.


The figures are up to 1 billion, and a precision of
6 decimals is enough.

Is that a US billion (10 digits or more), or a British billion (13 digits or
more)?


Maybe Double can handle that just as well as Currency?

Either way, you risk loosing precision if you work with Double. With
Double, you can count on only 15 digits of precision, including the 6
fractional digits. You are talking about up to 16 digits at least, unless
you mean "__less_than__ 1 billion US".

In contrast, with Currency, you can retain up to 18 digits of precision,
including the 6 fractional digits -- and up to 19 digits in some cases. See
the VB help page for "currency data type".

With 6 fractional digits, Currency is limited to numbers up to about
9,223,372,036,853. But that seems to meet your requirements, regardless of
the which definition of "billion" you mean.

However, that might be deceptive. It is all very easy to inadvertently mix
Double and Currency values in arithmetic operations, in which case the
arithmetic is performed using Double.


The benefits of Currency can be seen with this example:

' using Currency
cyX@ = 10.1 - 10
cyY@ = 0.1
Msgbox (cyX@ = cyY@)

' using Double
dX# = 10.1 - 10
dY# = 0.1
Msgbox (dX# = dY#)

In the Currency example, the result is TRUE, as expected.

In the Double example, the result is FALSE(!). The reason is because of the
approximate nature of the Double representation.

For this reason, in banking circles, it used to be common practice to retain
financial data in the form of large integers, scaling numbers to retain the
required degree of precision; for example, scaling by 10000 to retain
numbers to the "basis point" -- 1/100 of a cent (4 decimal places).

On the other hand, the above problem with Double can be minimized by
judicious and prolific use of rounding. For example:

dX# = WorksheetFunction.Round(10.1 - 10, 6)

(There is an important difference between the Excel ROUND and VB Round
functions.)


The benefits of Double, besides the simplicity of avoiding scaling when your
numbers have more than 4 decimal places, can be seen with this example:

' using Double
dX# = 100 / 3
dY# = 3 * dX#
Msgbox (dY# = 100)

' using Currency
cyX@ = 100 / 3
cyY@ = 3 * cyX@
Msgbox (cyY@ = 100)

In the Currency example, the result is FALSE(!). This is because 100 / 3 is
stored as exactly 33.3333, so 3*cyX@ results in exactly 99.9999.

In the Double example, the result is TRUE, as you might hope. It works in
this case because 100 /3 is stored as
33.3333333333333,357018091192003339529037475585937 5. When that is
multiplied by 3 and rounded to the precision of the Double type, we get
exactly 100.

However, Double will not always work so predictably; in fact, this example
is more the exception than the rule. Once again, the judicious and prolific
use of rounding of most Double computations will minimize surprises.

Alternatives to explo (a) the VB Decimal data type; and (b) commercial
products that provide "decimal" arithmetic libraries.

I do not have experience with either one.


----- original message -----

"Gustaf" wrote in message
...
I've inherited a project from another VBA developer. It deals a lot with
cost (price) calculations, but I notice that the original developer has
used Double for prices, rather than Currency. I've read that Currency
should be used for money, but I'm not sure if and why it would matter in
this project. The figures are up to 1 billion, and a precision of 6
decimals is enough. Maybe Double can handle that just as well as Currency?
Wondering if I would gain anything by using Currency in this case.

Gustaf