Undesired Rounding in VBA; accumulating values

G

Glenn Ray

Sorry I don't have a copy of the source code handy, but here goes:

I'm reading values from a series of cells in one sheet (stored in a 2D
array), then accumulating values in a destination sheet. The array is
type Double; using XL2002.

The source data goes to three decimal places (ex. 0.245), but the
value accumulated is rounded (ex. 0.25).

Source Sheet reading:
ActiveCell.Offset(Row, Column).Value = dblArray(RV,CV)

Destination Sheet accumulating:
ActiveCell.Offset(Row, Column).Value =
ActiveCell.Offset(Row,Column).Value + dblArray(RV,CV)


-Glenn
Any clue as to why this is happening?
 
R

Ron Rosenfeld

Sorry I don't have a copy of the source code handy, but here goes:

I'm reading values from a series of cells in one sheet (stored in a 2D
array), then accumulating values in a destination sheet. The array is
type Double; using XL2002.

The source data goes to three decimal places (ex. 0.245), but the
value accumulated is rounded (ex. 0.25).

Source Sheet reading:
ActiveCell.Offset(Row, Column).Value = dblArray(RV,CV)

Destination Sheet accumulating:
ActiveCell.Offset(Row, Column).Value =
ActiveCell.Offset(Row,Column).Value + dblArray(RV,CV)


-Glenn
Any clue as to why this is happening?
Click to expand...

What is the format of ActiveCell.Offset(Row, Column)?
What is the setting of Options/Tools/Precision as displayed?


--ron
 
G

Glenn Ray

"Pecision as Displayed" is turned off. Format for both source and
destination cells is "$0.00" (Currency, "$", 2 digits).

I actually solved the problem last night by making the following
change in the accumulating formula:
Destination Sheet accumulating:
ActiveCell.Offset(Row, Column).Value = _
ActiveCell.Offset(Row,Column).Value2 + dblArray(RV,CV)

This corrected the rounding I was seeing, but I didn't expect the
error in the previous case in any regard.

Thanks,
Glenn
 
R

Ron Rosenfeld

"Pecision as Displayed" is turned off. Format for both source and
destination cells is "$0.00" (Currency, "$", 2 digits).

I actually solved the problem last night by making the following
change in the accumulating formula:
Destination Sheet accumulating:
ActiveCell.Offset(Row, Column).Value = _
ActiveCell.Offset(Row,Column).Value2 + dblArray(RV,CV)

This corrected the rounding I was seeing, but I didn't expect the
error in the previous case in any regard.
Click to expand...

I don't believe there should be any difference between Value and Value2 unless
you were using Currency or Date data types.

But if the format of the destination cells is set to 2 digits, then the display
in those cells will be arithmetically rounded to two digits. So 0.245 will be
displayed as 0.25; although the contents of the cell should still be 0.245.

You could determine the latter by temporarily reformatting the cell to have
more decimal digits.


--ron
 
R

Ron Rosenfeld

? ActiveCell.NumberFormat
$#,##0.00
? typename(activecell.Value)
Currency
Click to expand...

Aha! That's why Value2 works differently than Value in this instance.

Thanks for the info.


--ron
 
Top