June 07, 2012

Switch to LibreOffice! Now!

I just switched over to LibreOffice.

Trying to read the documentation for Basic at OpenOffice.org, I ran across the page where there are warnings about a bunch of bugs in the Currency type variable, which is supposed to store and calculate amounts of currency accurately (to four decimal places without rounding errors and amounts up to 922 trillion.) Trying some little tests that others had written in Basic, I reproduced those errors on my computer. The errors weren't small; they were like 5 times 50000 equals 10.949 trillion for example. It couldn't deal with any amount over 214 thousand reliably. The errors were still there after years and versions of people complaining.

I thought "I'm not having any of this" and as soon as I had documented how the tests went and found out that the LibreOffice team had found a solution to that bug in 2010, I shut it all down and went out to get something to eat. The next time I started the computer, the first thing I did was uninstall OpenOffice, then find the LibreOffice download page, look at their latest version, restart the computer, download it, install it, load up the Basic tests I was working on, and surprise, surprise, enjoy the clean, neat feeling of having 5 times 50000 equal 250000.

Update June, 10:

Don't trust the Basic Currency type variable to get every hundredth of a cent right on amounts multiplied to over 92 billion. In LibreOffice 3.5.4 it definitely works better than OpenOffice 3.3 did (one of the versions with extreme, unacceptable errors.) However, a Currency type product goes off by a hundredth of a cent on some results just over 92 billion, with more frequent and proportionately larger inaccuracies for higher amounts. Inaccuracies are almost inevitable at high amounts, because variables get cast to float at many points if you don't watch out [see hint below] and double check everything, and because the Currency type is a 64-bit integer representation of a fixed-point decimal fraction number, by the integer representing 10000ths, and it probably doesn't get multiplied into a 128-bit number before it's converted back to a 64-bit result, or at least it doesn't get the extra bits over 64 perfectly accurately. So some information can be lost when the result of a multiplication is greater than (2 ^ 63 - 1) / 10000 / 10000. That's just over 92 billion.

A useful hint in this sort of Basic is that the statement

[a sheet object].getCellByPosition(column, row).setValue(cTotal)

where cTotal = a large product you've calculated in Currency type, will lose precision in the spreadsheet display with default settings. Instead of  setValue(cTotal), use:

[a sheet object].getCellByPosition(column, row).setString(str$(cTotal))

Then it will show all the digits of precision (as you widen the column to make room) so you'll be able to see whether you're getting any rounding errors.

I also wrote a base-10000 multiplier with 32 decimal digits of precision to double check everything I'm writing about. Overall this has been sort of fun for me, because it's about learning not to make mistakes in programming, which I made a lot, and learning to make the computer not make mistakes. I think JavaScript and Python would be more practical and up-to-date languages to use.