Calc basic decimal rounding suggestion

[jheath]

I’m using Calc BASIC macros for various financial processing. When I enter a transaction amount, like “10.93”, I load that value into a Single variable, and according to my Watch as I step through the code, the value is actually 10.93. But then when I store that value into a cell like so:

shtJournal.getCellByPosition(6,jrnlRow).setValue(nCredit)

the value that shows up in the cell is actually 10.9300003051758. If I change the code to this:

shtJournal.getCellByPosition(6,jrnlRow).setValue(Int(nCredit*100+0.5)/100)

then it seems to store the value 10.93. This doesn’t make any sense to me. I understand that doing math on Single numbers results in little differences out 8 or 10 decimals, but if I just pull the value 10.93 out of a cell, and later do a setValue of it somewhere else, shouldn’t the value in the cell be the same value? Or looking at things the other way around, the second code snippet above seems to produce the desired result, but it is doing a series of calculations on a Single number; when it gets to the point of calculating 1093/100 (before the setValue), why doesn’t it produce 10.9300003051758 instead of 10.93? I would think it was more susceptible to rounding problems because it is the result of a mathematical expression, rather than just loading a value and saving it out!

In another place I used this code:

 Val(Format(nAmount, "0.##"))

and that also seems to work pretty well, turning the number into a string that has at most 2 decimal places and then getting the value of that string. But the bottom line is that I don’t really understand why things happen this way, and when I see problems, I figure out code which seems to “solve the problem”, but if I understood things better, maybe I could write the code in the first place that would avoid the problems.

So any suggestions or advice or pointers to pages to read?

[jheath]

Another related thing I don’t really understand: I don’t really understand the dual nature of a cell as string or value. E.g. if I setString(Format(nAmount, “0.##”)), what would be the implications be for the use of that cell in a SUM() formula? Does it no longer have a “value” just a “string”? What is the real difference in how those two things are treated?

[oweng]

I load that value into a Single variable

Presumably this Single variable is nCredit. According to OpenOffice Macros Explained, 3rd Ed., r523, 15.2.2, by Andrew Pitonyak the setValue() method takes a Double variable. Have you tried it with a Double?

Cells can contain one of four different types of data:

• Float (Double) i.e., use getValue() and setValue().
• Text (String) i.e., use getString() and setString().
• Formula (String) i.e., use getFormula() and setFormula().
• Empty (no data / unset).

[jheath]

I believe using Double helps, thanks. What I don’t really understand is the “multi” nature of the different types of data. If I setValue(), then any String that was there gets wiped out, correct? And if I have a String, a ClearContents of Values doesn’t touch it. Well if the user is entering data in cells, and enters “1234” then it becomes a Value, but if he enters “1234a” then it becomes a String. How do I reliably pull the data out of the cell in my code if I don’t know what it is?

[oweng]

Use the method getType() to find out the type of data that a cell contains. Listing 404 in Pitonyak provides an example.

[jheath]

Thanks, oweng. Yes, that section in Pitonyak’s document was just what I needed to read. It seems like in the old Excel days that it wasn’t quite this complicated, but Excel was probably just making assumptions for us, and probably not always correctly. So it’s probably not a bad thing to have this fine level of control. Note that in the latest version of OpenOffice.org Macros Explained (just downloaded from here), listings 407-408 contain the relevant code.