Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned specific decimal space
To assign space for an Excel (2003) column that required maximum 5
characters, I declared it as Dim strCompound String * 5. But I do not know how to fit specific requirements for my designated calculation fields (I have no VBA help file to lookup!!!): - Declare a variable that has maximum 8 digits and 4 decimals (i.e.: 42352975.3687). - Declare a variable with currency value that has maximum of 6 digits and 2 decimals ( i.e.: $234567.25) I began to use macro since last month and do not know if there is a way to meet the needs for those columns. Please show me the rope if possible. Thank you. James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned specific decimal space
Excel stores all numbers as double.
Dim myNumber as double myNumber = 42352975.3687 a# uses the implicit designator # which types it as double - used in the immediate window only for demonstration as you can't type variables in the immediate window: ? typename(A#) Double a# = 42352975.3687 ? a 42352975.3687 a# = 234567.25 ? a 234567.25 ? format(a,"$#,##0.00") $234,567.25 -- Regards, Tom Ogilvy "James" wrote in message oups.com... To assign space for an Excel (2003) column that required maximum 5 characters, I declared it as Dim strCompound String * 5. But I do not know how to fit specific requirements for my designated calculation fields (I have no VBA help file to lookup!!!): - Declare a variable that has maximum 8 digits and 4 decimals (i.e.: 42352975.3687). - Declare a variable with currency value that has maximum of 6 digits and 2 decimals ( i.e.: $234567.25) I began to use macro since last month and do not know if there is a way to meet the needs for those columns. Please show me the rope if possible. Thank you. James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned specific decimal space
Thanks for a quick reply. Please forgive me, but here are the dumb
questions: Would this formatted value helps change the "actual" value of the cells in column? IT/IS folks are screaming at me left and right for choking their Oracle tables with those extra decimals in columns. These data were extracted from other Excel files and saved in .csv format before ftp to the IT/IS department. The macro is in the destination sheet, which was then need to be saved in .csv format. They allow me 1 column with 4 decimals, the rest of the columns are only 2 decimals. With "double", some cells in the can have 5, 6 or 7 decimals while other cells have zero or none. If it is formatted, it will definitely look good, but will it stop messing up their tables? Why are the differences between the "actual value" vs. format value? Again, thank you for your kind help. Confused in Plano, James. Tom Ogilvy wrote: Excel stores all numbers as double. Dim myNumber as double myNumber = 42352975.3687 a# uses the implicit designator # which types it as double - used in the immediate window only for demonstration as you can't type variables in the immediate window: ? typename(A#) Double a# = 42352975.3687 ? a 42352975.3687 a# = 234567.25 ? a 234567.25 ? format(a,"$#,##0.00") $234,567.25 -- Regards, Tom Ogilvy "James" wrote in message oups.com... To assign space for an Excel (2003) column that required maximum 5 characters, I declared it as Dim strCompound String * 5. But I do not know how to fit specific requirements for my designated calculation fields (I have no VBA help file to lookup!!!): - Declare a variable that has maximum 8 digits and 4 decimals (i.e.: 42352975.3687). - Declare a variable with currency value that has maximum of 6 digits and 2 decimals ( i.e.: $234567.25) I began to use macro since last month and do not know if there is a way to meet the needs for those columns. Please show me the rope if possible. Thank you. James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned specific decimal space
Perhaps rounding the number in cell can help out? Thx. James
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned specific decimal space
If they are constants,
format them to display as you wish then do Tools=Options, go to the calculated tab and click Precision as displayed. You will be warned about a permanent loss of precision. Say OK. Now you can change it back. Your data should now have only the required number of decimals. If theyare not constants, select all cells, do edit=copy, then edit=Paste special and select values. This will replace all formulas. -- Regards, Tom Ogilvy "James" wrote in message oups.com... Perhaps rounding the number in cell can help out? Thx. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i round a quotient to specific decimal places? | Excel Worksheet Functions | |||
Rounding up or down to specific decimal digits. | Excel Worksheet Functions | |||
how do I stop excel putting a space after a decimal point | Excel Discussion (Misc queries) | |||
Paper Space / Model Space ? | Excel Discussion (Misc queries) | |||
Space between decimal numbers | Excel Worksheet Functions |