Error Adding Single data type 1

[FractalWalk]

I am having trouble with the simplest of issues. I am using VBA in Excel 2010 to read a list of prices from a spreadsheet. I then summarize the array by categories from the spreadsheet to get subtotal prices by category. But when I add the prices together, VBA does not total the price correctly.

I am using a single data type and when it reads for example, 10.50, it might read it as 10.51132 or something odd like that. This throws the totals off. I have tried rounding and truncating decimal places but I can't find 100% foolproof way to add a list of 2 decimal numbers together accurately. The data in the spreadsheet is correct (no extra hidden numbers after the second decimal place).

Below are the relevant pieces of the code.

Sub test()
    a = 2
    Do Until Cells(a, 1) = Empty
        ttl = WorksheetFunction.Round(ttl + Cells(a, 1), 2)
            'doesn't work: ttl = ttl + Cells(a, 1)
            'also doesn't work: ttl = ttl + Int(Cells(a, 1) * 100) / 100
        a = a + 1
    Loop
End Sub

 

[SkipVought]

hi,

read a list of prices...

Why not CURRENCY data type? Single is not a precise data type that anyone would use for currency precision.

Do yourself a favor and do some research on ACCURACY and PRECISION in Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[FractalWalk]

OK, thanks. That should work for this section.
But there are multiple parts to my code and one of them uses a variant array. Same issue there. WHen Iread the currency values into the variant array, the values change. I can't use currency for the whole array so how can I get proper accuracy within a variant array?

Do yourself a favor and do some research on ACCURACY and PRECISION in Excel.
I did and I find also sorts of information telling me that its a problem but no solutions.

 

[SkipVought]

so how can I get proper accuracy within a variant array?

Why not CURRENCY data type? Variant is not a precise data type that anyone would use for currency precision.

Use the CURRENCY data type!



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[FractalWalk]

The array contains text, dates and values. I can't use currency on that array. I have to use variant don't I?

 

[PHV]

You may use the CCur function to coerce the variant subtype to Currency.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FractalWalk]

Thanks. Evidently CCUR allows 4 decimal places and I need it fixed at 2, but I have worked around that.