Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Currency Data Type and automatic cell formatting
All,
Windows XP, Excel 2000 I have noticed that when my VBA code writes a variable (of Currency data type) out to a worksheet, the cell automatically truncates or rounds it to two decimal places, even though the currency variable contains a value with 4 decimal places. This can be duplicated by creating a simple VBA subroutine behind a clean worksheet (i.e. no formatting at all): Public Sub TestItOut() Dim curMyValue as Currency Dim dblMyValue as Double curMyValue = 548126789.4482 dblMyValue = 548126789.4482 Range("A1").Value = curMyValue Range("B1").Value = dblMyValue End Sub A1 will contain 548126789.45, and B1 will contain 548126789.4482. Does anyone know the rule that governs this automatic formatting? Is the value always truncated? Is it rounded? Is there any way to stop it from doing this, or control it? Thanks, Mark D. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Currency Data Type and automatic cell formatting
Look at VBA's help for .Value2
Range("A1").Value2 = curMyValue Range("B1").Value2 = dblMyValue Currency and dates can be a pain. Mark Dev wrote: All, Windows XP, Excel 2000 I have noticed that when my VBA code writes a variable (of Currency data type) out to a worksheet, the cell automatically truncates or rounds it to two decimal places, even though the currency variable contains a value with 4 decimal places. This can be duplicated by creating a simple VBA subroutine behind a clean worksheet (i.e. no formatting at all): Public Sub TestItOut() Dim curMyValue as Currency Dim dblMyValue as Double curMyValue = 548126789.4482 dblMyValue = 548126789.4482 Range("A1").Value = curMyValue Range("B1").Value = dblMyValue End Sub A1 will contain 548126789.45, and B1 will contain 548126789.4482. Does anyone know the rule that governs this automatic formatting? Is the value always truncated? Is it rounded? Is there any way to stop it from doing this, or control it? Thanks, Mark D. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Currency Data Type and automatic cell formatting
Dave,
Thanks for the info. I had no idea there was a "Value2" property. This allows me to return the number to the worksheet, without any formatting, and then I can handle the formatting separately. Thanks, Mark "Dave Peterson" wrote in message ... Look at VBA's help for .Value2 Range("A1").Value2 = curMyValue Range("B1").Value2 = dblMyValue Currency and dates can be a pain. Mark Dev wrote: All, Windows XP, Excel 2000 I have noticed that when my VBA code writes a variable (of Currency data type) out to a worksheet, the cell automatically truncates or rounds it to two decimal places, even though the currency variable contains a value with 4 decimal places. This can be duplicated by creating a simple VBA subroutine behind a clean worksheet (i.e. no formatting at all): Public Sub TestItOut() Dim curMyValue as Currency Dim dblMyValue as Double curMyValue = 548126789.4482 dblMyValue = 548126789.4482 Range("A1").Value = curMyValue Range("B1").Value = dblMyValue End Sub A1 will contain 548126789.45, and B1 will contain 548126789.4482. Does anyone know the rule that governs this automatic formatting? Is the value always truncated? Is it rounded? Is there any way to stop it from doing this, or control it? Thanks, Mark D. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Currency Data Type and automatic cell formatting
I think it comes as a surprise to everyone when they first notice it. It did to
me, too. Mark Dev wrote: Dave, Thanks for the info. I had no idea there was a "Value2" property. This allows me to return the number to the worksheet, without any formatting, and then I can handle the formatting separately. Thanks, Mark "Dave Peterson" wrote in message ... Look at VBA's help for .Value2 Range("A1").Value2 = curMyValue Range("B1").Value2 = dblMyValue Currency and dates can be a pain. Mark Dev wrote: All, Windows XP, Excel 2000 I have noticed that when my VBA code writes a variable (of Currency data type) out to a worksheet, the cell automatically truncates or rounds it to two decimal places, even though the currency variable contains a value with 4 decimal places. This can be duplicated by creating a simple VBA subroutine behind a clean worksheet (i.e. no formatting at all): Public Sub TestItOut() Dim curMyValue as Currency Dim dblMyValue as Double curMyValue = 548126789.4482 dblMyValue = 548126789.4482 Range("A1").Value = curMyValue Range("B1").Value = dblMyValue End Sub A1 will contain 548126789.45, and B1 will contain 548126789.4482. Does anyone know the rule that governs this automatic formatting? Is the value always truncated? Is it rounded? Is there any way to stop it from doing this, or control it? Thanks, Mark D. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I type in a number in a cell but it wont convert to currency help | Excel Worksheet Functions | |||
Formatting currency in cell | Excel Discussion (Misc queries) | |||
automatic formatting of cells to currency in Excel 2007 | Excel Worksheet Functions | |||
Change Cell Value Across whole WorkSheet/ Workbook if cell type is currency | Excel Programming | |||
Determine the type of currency in a cell | Excel Programming |