Format affects precision?
 

Apologies if this is well known, couldn't find any references to it.

It seems that setting a cell to currency format affects the precision
with which the cell's values are passed to VBA. For example:

Public Function TestDouble(dDouble As Double) As Double

TestDouble = dDouble

End Function

Then set cells:
A1: 0.123456789, formatted as currency, displayed to 15 decimal
points.
A2: =A1, displayed to 15 decimal points.

Precision as displayed shouldn't factor in, but is turned off just to
be sure.

TestDouble(A1) returns 0.1235
TestDouble(A2) returns 0.123456789
TestDouble(A1*1) returns 0.123456789 (!!)

Yikes. Is there any way to turn this "feature" off, so that formatting
a cell does not change the precision of calculations based on it? I
tried to find documentation for this behavior but didn't have any luck
there either.

Thanks...

Format affects precision?
 

Don't confuse a "format" with a Type Declaration.

When you format a number as Currency....the result is the original value,
displayed with a dollar sign ($) and only 2 decimal places displayed. The
rest of the original value still exists....it's just not displayed.

When you declare a variable as Currency (Dim x as Currency)...VBA rounds the
original value to 4 decimal places THEN stores the resulting value.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Caveman" wrote:

Apologies if this is well known, couldn't find any references to it.

It seems that setting a cell to currency format affects the precision
with which the cell's values are passed to VBA. For example:

Public Function TestDouble(dDouble As Double) As Double

TestDouble = dDouble

End Function

Then set cells:
A1: 0.123456789, formatted as currency, displayed to 15 decimal
points.
A2: =A1, displayed to 15 decimal points.

Precision as displayed shouldn't factor in, but is turned off just to
be sure.

TestDouble(A1) returns 0.1235
TestDouble(A2) returns 0.123456789
TestDouble(A1*1) returns 0.123456789 (!!)

Yikes. Is there any way to turn this "feature" off, so that formatting
a cell does not change the precision of calculations based on it? I
tried to find documentation for this behavior but didn't have any luck
there either.

Thanks...

Format affects precision?
 

This happens because by default Excel/VBA converts an excel cell formatted
as currency to a VBA currency variable (and an excel cell formatted as date
to a VBA Date variable).

The confusing thing is the implicit conversions that are being done 'under
the covers' where you dont see them:
excel cell formatted as currency --VBA currency variable--VBA double
variable

You can prevent this by using the .Value2 property of a range object:

Public Function TestVar(dDouble As Range) As Double
' use an explicit range object

TestVar = dDouble.Value2
End Function

Public Function TestVar(dDouble As Variant) As Double
' use a variant that contains a range

TestVar = dDouble.Value2
End Function

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com


"Caveman" wrote in message
ps.com...
Apologies if this is well known, couldn't find any references to it.

It seems that setting a cell to currency format affects the precision
with which the cell's values are passed to VBA. For example:

Public Function TestDouble(dDouble As Double) As Double

TestDouble = dDouble

End Function

Then set cells:
A1: 0.123456789, formatted as currency, displayed to 15 decimal
points.
A2: =A1, displayed to 15 decimal points.

Precision as displayed shouldn't factor in, but is turned off just to
be sure.

TestDouble(A1) returns 0.1235
TestDouble(A2) returns 0.123456789
TestDouble(A1*1) returns 0.123456789 (!!)

Yikes. Is there any way to turn this "feature" off, so that formatting
a cell does not change the precision of calculations based on it? I
tried to find documentation for this behavior but didn't have any luck
there either.

Thanks...