Customer has a large workbook (.xls) with multiple large worksheets. Our software, using Flexcel 6.0, XE4, Win 32, loads one of the sheets, reads several of the cells in each record, and populates a "results" cell with an alpha string. Then the .xls is saved, overwriting the original. We do nothing to affect any numeric cell nor do we touch any of the formulas.
Customer has a field on another sheet in the workbook that balances all the currency calculations in the workbook, and, if everything is correct, comes up with $0.00. He has used our product before we replaced another third-party tool with Flexcel and never had a problem, but now that he has updated our product to the version with Flexcel, he reports that after running it through our product, his balance calculation repeatedly comes up off by amounts in the range of 5 - 10 pennies. He has isolated this to just when he runs our product.
He can workaround by creating a separate workbook with a worksheet containing just the fields our software needs to read from and write to, and then importing the result back into his main workbook.
Have you run into anything like this? Is there something that sets precision that I might need to change from default in Flexcel?
Any help you might be able to provide would be great.
Floating point can introduce errors indeed, and actually FlexCel is more exact than Excel there (because it uses extended floating at places, while Excel uses doubles always).
If you can send me a file to adrian@tmssoftware.com so I can check it out, I might be able to give more information on what the issue is.
One workaround I can think of is to not make FlexCel recalculate. When you modify any cell, FlexCel will recalculate the full file before saving, because it can't know what formulas are affected by the change. For example, if you change cell A1 from 1 to 2, and in Sheet2!B3 you have the formula =A1 + 1, the result must be changed from 2 to 3. A single cell changed might cascade changes to all the formulas in the file.
But if you aren't changing any cell that might affect formulas, you might tell FlexCel not to recalculate at all. To do so, call:
xls.RecalcMode := TRecalcMode.Manual;
before opening the file. This will cause Excel not to recalculate the file, and make Excel calculate it on open.
As said, I could maybe give more ideas if I can see a file with the issue. But floating point can actually introduce errors if you have a formula like If(A1=A2,1,0) and A1 is 0 and A2 is 1e-300. Interestingly, with double precision instead of extended both might be 0 and so less precision would lead to better results, but in general, most times the opposite will be true.
Thanks! Customer is unwilling to send us his file because of personal data, but we will try setting recalculate to no and see if that fixes his issue. I'm sure there is some customer somewhere who uses one of the fields we populate in a calculation, but I sure can't imagine why, so this seems the safer option.
As always, thanks for very fast support. And it's great for us to finally be able to offer native Excel support for our Mac OSX customers.