Writing variable from a Macro to a Cell
 

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

Writing variable from a Macro to a Cell
 

"Parker" wrote in message
...
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,
Dimension your variable as double.

Bob L.

Writing variable from a Macro to a Cell
 

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.
-----Original Message-----

"Parker" wrote in message
...
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,
Dimension your variable as double.

Bob L.


.

Writing variable from a Macro to a Cell
 

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.

--
Regards,
Tom Ogilvy


philip wrote in message
...
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.
-----Original Message-----

"Parker" wrote in message
...
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,
Dimension your variable as double.

Bob L.


.

Writing variable from a Macro to a Cell
 

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 ?
-----Original Message-----
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.

--
Regards,
Tom Ogilvy


philip wrote in message
...
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.
-----Original Message-----

"Parker" wrote in message
...
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,
Dimension your variable as double.

Bob L.


.



.

Writing variable from a Macro to a Cell
 

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.
--
Regards,
Tom Ogilvy

philip wrote in message
...
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 ?
-----Original Message-----
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.

--
Regards,
Tom Ogilvy


philip wrote in message
...
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.
-----Original Message-----

"Parker" wrote in message
...
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,
Dimension your variable as double.

Bob L.


.



.