Assigned specific decimal space

[James]

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

 

[Tom Ogilvy]

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

 

[James]

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.

 

[James]

Perhaps rounding the number in cell can help out? Thx. James

 

[Tom Ogilvy]

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.