VBA code outputs currency value rounded to 2 decimal points

[tamarack007]

Hello,

I have a macro which calculates the value of a currency variable and
then using the statement
ActiveCell.Offset(-1,7).Value = currTradeValue outputs it to a
spreadsheet.

The value is always rounded to 2 decimal points despite trying the
following;

1. I have checked the value of the variable prior to the above
statement being executed and it is as I expect - 3 decimal points e.g.
-1.125

2. Formatted the target cell using
ActiveCell.Offset(-1,7).NumberFormat = "#,##0.00_);[Red](#,##0.00)"
prior to the assignment statement.

3. Manually formatted the cell within Excel prior to the assignment
statement.

4. Checked the Online Help.

If I have a cell that contains a 4 decimal point number for example
and format the cell to round to 2 decimal points, and then select that
cell, the number in the formula bar is the original 4 decimal point
number.

For some reason, the value of the currency variable seems to be
modifed to 2 decimal points before being output as it is the modified
value that I see in the formula bar when the cell in question is
selected.

Could someone please tell me what I or Excel is doing wrong.

Thank you
Mark.

 

[OssieMac]

Hi,

You can have as many decimal points as you want. Just increase the number of
zeros afer the decimal point. The following gives you 3 decimal points.

ActiveCell.Offset(-1, 7).NumberFormat = "#,##0.000;[Red]#,##0.000"

Note: The first section is for a positive value and after the semi colon (
is for a negative value. The above makes a negative value Red.

Regards,

OssieMac

 

[OssieMac]

Just a little more info. When I format a cell with the code that I gave you
if I enter 2.655 then I get 2.66 displayed in the cell and in the formula bar
is 2.655.

If I multiple the cell contents by 2 then I get 5.31 which is 2.655 x 2.

Tested above in both xl2002 and xl2007

Regards,

OssieMac

Hi,

You can have as many decimal points as you want. Just increase the number of
zeros afer the decimal point. The following gives you 3 decimal points.

ActiveCell.Offset(-1, 7).NumberFormat = "#,##0.000;[Red]#,##0.000"

Note: The first section is for a positive value and after the semi colon (
is for a negative value. The above makes a negative value Red.

Regards,

OssieMac

Hello,

I have a macro which calculates the value of a currency variable and
then using the statement
ActiveCell.Offset(-1,7).Value = currTradeValue outputs it to a
spreadsheet.

The value is always rounded to 2 decimal points despite trying the
following;

1. I have checked the value of the variable prior to the above
statement being executed and it is as I expect - 3 decimal points e.g.
-1.125

2. Formatted the target cell using
ActiveCell.Offset(-1,7).NumberFormat = "#,##0.00_);[Red](#,##0.00)"
prior to the assignment statement.

3. Manually formatted the cell within Excel prior to the assignment
statement.

4. Checked the Online Help.

If I have a cell that contains a 4 decimal point number for example
and format the cell to round to 2 decimal points, and then select that
cell, the number in the formula bar is the original 4 decimal point
number.

For some reason, the value of the currency variable seems to be
modifed to 2 decimal points before being output as it is the modified
value that I see in the formula bar when the cell in question is
selected.

Could someone please tell me what I or Excel is doing wrong.

Thank you
Mark.

 

[MarkM]

Hi OssieMac,

I am using Excel 2003. I did modify the number format to include an
extra digit, but based on my observation that the value is changed
prior to it being written to the spreadsheet, I did not think this
would make any difference and unfortunately it didn't.

Bottom line is I think the value is changed before being output and no
formatting is going to change it.

Thanks for your help
Mark

 

[Dave Peterson]

I don't think you shared enough of your code.

If you really used:

Dim currTradeValue As Currency
try using "As Double"

If you're picking up that value from the cell that's formatted as Currency, try
using .Value2

dim myVal as Double
myVal = activecell.value2

Hello,

I have a macro which calculates the value of a currency variable and
then using the statement
ActiveCell.Offset(-1,7).Value = currTradeValue outputs it to a
spreadsheet.

The value is always rounded to 2 decimal points despite trying the
following;

1. I have checked the value of the variable prior to the above
statement being executed and it is as I expect - 3 decimal points e.g.
-1.125

2. Formatted the target cell using
ActiveCell.Offset(-1,7).NumberFormat = "#,##0.00_);[Red](#,##0.00)"
prior to the assignment statement.

3. Manually formatted the cell within Excel prior to the assignment
statement.

4. Checked the Online Help.

If I have a cell that contains a 4 decimal point number for example
and format the cell to round to 2 decimal points, and then select that
cell, the number in the formula bar is the original 4 decimal point
number.

For some reason, the value of the currency variable seems to be
modifed to 2 decimal points before being output as it is the modified
value that I see in the formula bar when the cell in question is
selected.

Could someone please tell me what I or Excel is doing wrong.

Thank you
Mark.

 

[OssieMac]

Hi Mark,

If you are still having problems then as Dave Peterson suggested, you may
need to post more code so include all code where you create and/or manipulate
the data to get the value in currTradeValue and we can have another look at
it.

Regards,

OssieMac

 

[MarkM]

Hi Guys,

Dave suggested using Value2. Initially I did not use this as according
to VBA Help, Value2 property doesn't use the Currency and Date data
types, but I thought it best to try it before posting the code and to
my surprise it actually works.

Sorry I doubted you Dave.

Thanks for you help
Mark

 

[Dave Peterson]

Oh ye of little faith. <vbg>

I hope you tried it earlier and just posted your results now. I find that VBA's
help for .value2 kind of confusing. By saying that it doesn't use Currency or
date data types, it sounds (to me) like this wouldn't apply to currency or
dates.

I now read it that it ignores those Currency and date data types and brings back
the underlying value--as a "raw" number.