Thanks for the explanation. The previous instructions on how to fix the
problem were very helpful, but I always like to know why something is
happening the way it is as well.

Mike


" wrote:

Mike wrote:
I'm still not clear on what is happening in the first place. My program
rounds the number to a single decimal before it ever puts it into the
spreadsheet. So, the spreadsheet shouldn't even see any extra decimal
positions.

There are two problems, one that you can mitigate, the other that you
have no control over.

The latter first. In simplified terms, binary computers approximate
decimal fractions as a sum of powers of 1/2, each one called a bit.
For example, 0.625 is represented by 1*(1/2) + 0*(1/4) + 1*(1/8). But
there is only a limited number of bits in the binary representation.
Most decimal fractions cannot be represented exactly within the fixed
number of bits.

However, the numerical error should be very small. And you might not
always see the numerical error because of heuristic algorithms in Excel
that attempt to hide such errors when it displays values in cells.

In any case, you exacerbate the problem by declaring your VBA variable
as Single instead of Double. In a binary computer, a Single floating
point representation has less than half the number of bits than a
Double floating point representation for what is called the mantissa
(the series of powers of 1/2). Consequently, the Single sum of binary
powers may be truncated when it is stored into a Double, which I
presume Excel uses for numeric cell values. For example, format cells
as Scientific with 14 decimal places and compare the results of the
following two functions (see the VBA below):

=singleval(23.7) (2.37000007629394E+01)
=doubleval(23.7) (2.37000000000000E+01)

Bottom line: You should use type Double in your VBA program to
minimize the numerical error due to the binary representation.

-----
VBA examples:

Function singleval(foo As Single) As Double
singleval = foo
End Function

Function doubleval(foo As Double) As Double
doubleval = foo
End Function