Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing an formulae with variable parameters | Excel Worksheet Functions | |||
Writing a macro to hide columns based on cell value | Excel Discussion (Misc queries) | |||
writing a variable to a cell | Excel Programming | |||
writing a variable to a cell | Excel Programming |