Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I format one cell so that it affects another cell? | Excel Worksheet Functions | |||
Sort affects formula | New Users to Excel | |||
One workbook affects another | Excel Discussion (Misc queries) | |||
VBA compiled - affects conditional compile? | Excel Programming | |||
Precision displayed does not match precision in cell | Excel Discussion (Misc queries) |