Spurious decimals
 

I have some VBA code that reads data from a worksheet into a variant array.
Some of the data is text, and some is numeric. All the numeric data is read
from cells where it has been rounded to 2 decimals using =ROUND(A1,2) etc.

I then write the contents of the variant (12 rows by 15 columns) back to a
different worksheet with this kind of command:

Worksheets("abc").Range("xyz").value = varData

It works OK, except for the fact that some of the numeric data in the new
worksheet contains spurious decimals. So for instance, varData(1,15) might
equal 1609.30 exactly when I step through the VBA code, but when this number
lands in the new worksheet using the command above, the cell value is now
1609.30004882812.

Can anyone shed any light for me? Am using Excel 2002 on Win XP.

Thanks, SB

Spurious decimals
 

You mean like this: (demo'd from the immediate window)

? typename(b!)
Single
b! = 1609.30
? b!
1609.3
? cdbl(b!)
1609.30004882813


so the problem is the coversion from single to double I suspect.

--
Regards,
Tom Ogilvy


"Stephen Brook" wrote in message
...
I have some VBA code that reads data from a worksheet into a variant
array.
Some of the data is text, and some is numeric. All the numeric data is
read
from cells where it has been rounded to 2 decimals using =ROUND(A1,2) etc.

I then write the contents of the variant (12 rows by 15 columns) back to a
different worksheet with this kind of command:

Worksheets("abc").Range("xyz").value = varData

It works OK, except for the fact that some of the numeric data in the new
worksheet contains spurious decimals. So for instance, varData(1,15) might
equal 1609.30 exactly when I step through the VBA code, but when this
number
lands in the new worksheet using the command above, the cell value is now
1609.30004882812.

Can anyone shed any light for me? Am using Excel 2002 on Win XP.

Thanks, SB

Spurious decimals
 

Tom,

Yes indeed, that was the problem - thanks. I'd rather stupidly assigned the
cell.value (which I assume Excel stores internally as a Double) to a variable
declared as a Single (not a Double) - so presumably this forced a type
conversion that inadvertently introduced the spurious decimals.

Thanks again,

SB

"Tom Ogilvy" wrote:

You mean like this: (demo'd from the immediate window)

? typename(b!)
Single
b! = 1609.30
? b!
1609.3
? cdbl(b!)
1609.30004882813


so the problem is the coversion from single to double I suspect.

--
Regards,
Tom Ogilvy


"Stephen Brook" wrote in message
...
I have some VBA code that reads data from a worksheet into a variant
array.
Some of the data is text, and some is numeric. All the numeric data is
read
from cells where it has been rounded to 2 decimals using =ROUND(A1,2) etc.

I then write the contents of the variant (12 rows by 15 columns) back to a
different worksheet with this kind of command:

Worksheets("abc").Range("xyz").value = varData

It works OK, except for the fact that some of the numeric data in the new
worksheet contains spurious decimals. So for instance, varData(1,15) might
equal 1609.30 exactly when I step through the VBA code, but when this
number
lands in the new worksheet using the command above, the cell value is now
1609.30004882812.

Can anyone shed any light for me? Am using Excel 2002 on Win XP.

Thanks, SB