Currency Data Type and automatic cell formatting

[Mark Dev]

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]

Look at VBA's help for .Value2

Range("A1").Value2 = curMyValue
Range("B1").Value2 = dblMyValue

Currency and dates can be a pain.

 

[Mark Dev]

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]

I think it comes as a surprise to everyone when they first notice it. It did to
me, too.