Field length of VBA totals in Excel 2003

[JoanE]

Hi,

I've not had much experience using VBA within Excel and have scratched
my head for days on this one.

I have a spreadsheet containing several rows representing bank
statement transactions. They either have a value in a DR or CR column
so I don't have to worry about signs. These values in the spreadsheet
are formatted to 2 decimal places.

I have to produce a batch file for entry to an application that we use.
This file starts with a batch header containing a total of all the
debits and one of all the credits. This is followed by individual
transactions taken from the spreadsheet rows.

I go down the rows totalling the DR and CR columns. This can't be done
on the sheet since several rows are omitted by criteria too complex to
put in a cell formula.

The batch header fields are 13 characters long, padded at the start
with spaces.

My method of working out the number of spaces required was to subtract
the length of my total field from 13, which works most of the time.

However, now and again the total that I would expect to be something
like 2294.94 will appear as 2294.9400000000000001. This obviously gives
my number of space characters a negative value and crashes the macro.

My question is, after all that, how can I get my total to be the
correct length? The above should have been 7, meaning 6 spaces would be
required before it on the batch header. I know how to format a cell to
2 decimal places in VBA but not a variable.

Any assistance gratefully received.

Joan

 

[Dave O]

Hi, Joan-
Yours is the second post I've seen today that has addressed the idea of
phantom decimal places. As a workaround, you can use the ROUND
function on a variable in your VBA code or in the worksheet that
supplies the data to your code.

An interesting point: your post says >These values in the spreadsheet
are formatted to 2 decimal places
.... but this is the displayed value, not the cell's actual value. It
may be worthwhile for you to apply the ROUND function on the worksheet
and to the code, so the numbers all match up.

Dave O

 

[JoanE]

Hi Dave,

That did the trick. I still don't know where the spurious 1 at the
umpteenth decimal place came from, though. All my input comes from a
bank statement that is already to 2 decimal places which is probably
why I never thought of rounding.

Many thanks.

Joan