Writing variable from a Macro to a Cell

  • Thread starter Thread starter Parker
  • Start date Start date
P

Parker

I am having trouble writing a variable (defined as a
single) from a macro into a given cell. I have several
subtotals (Billing by dept) in the macro that are numbers
(2 decimals). When I write these numbers to a cell in a
new worksheet, any odd decimals (Not .25, .50, .75) are
changed slightly (Ex: 100.06 becomes 100.059997558593). I
think this is because they are entered as a variant.
There's probably an easy fix, but I can't find anything in
the help files.

Thanks in advance!

Parker
 
Parker said:
I am having trouble writing a variable (defined as a
single) from a macro into a given cell. I have several
subtotals (Billing by dept) in the macro that are numbers
(2 decimals). When I write these numbers to a cell in a
new worksheet, any odd decimals (Not .25, .50, .75) are
changed slightly (Ex: 100.06 becomes 100.059997558593). I
think this is because they are entered as a variant.
There's probably an easy fix, but I can't find anything in
the help files.

Thanks in advance!

Parker
Click to expand...

Parker,
Dimension your variable as double.

Bob L.
 
I think the problem is caused not by your definition of
the variable type but by the fact that there is a limit
to the binary accuracy in translating decimal numbers
into binary.

2 ways around :

easiest way is to make sure that the destination cell is
formatted in number format with only 2 decimals

more complicated : instead of putting 10.50, put 1050 as
an integer and use a simple formula "=A1/100" to convert.
 
Just a demo from the immediate window:

? typename(100.06!)
Single
? cdbl(100.06!)
100.059997558594

Numbers stored in cells are stored as double.

' store a single
Range("A1").Value = 100.06!
? range("A1").Value
100.059997558594

' store a double
range("A1").Value = 100.06#
? range("A1").Value
100.06

So dimension your variable as double.
 
I just tested in Excel XP :

store the result of the division 201/2 in variables of 3
types (double, single, variant), and then write the
variables into cells in a worksheet

then change format of each worksheet to Number with 24
decimals

Result : each of the 3 sheets is showing
100.500000000000000000000000

There seems to be some contradiction with earlier
postings on this question here ?
 
the number in question was 100.06, not 100.5.

Excel only stores numbers with 15 digits of precision. Any digits after
that are garbage.
 
Back
Top