Format affects precision?

[Caveman]

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...

 

[Ron Coderre]

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

 

[Charles Williams]

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