In LibreOffice Basic, MOD rounds its operands [1]. This matches MS VBA behavior [2]. But our help [3] does not mention that; and its samples imply that floating-point values are handled as one could expect. The rounding needs to be mentioned; and the samples should be corrected. [1] https://opengrok.libreoffice.org/xref/core/basic/source/sbx/sbxvalue.cxx?r=fbaf865f#837 [2] https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/mod-operator [3] https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03070600.html
Moving to NEW. @Rafael, I thought you might be interested in this issue
There seems to be a difference between MS VBA and LibreOffice Basic on how they handle fractional remainders: In Excel VBA, if you run (remainder is rounded to nearest integer): Sub ExampleMod MsgBox 15.1 Mod 5 'Remainder = 0.1; Returns 0 MsgBox 15.4 Mod 5 'Remainder = 0.4; Returns 0 MsgBox 15.5 Mod 5 'Remainder = 0.5; Returns 1 MsgBox 15.6 Mod 5 'Remainder = 0.6; Returns 1 MsgBox 15.9 Mod 5 'Remainder = 0.9; Returns 1 MsgBox 16 Mod 5 'Remainder = 1; Returns 1 End Sub And in LibreOffice Basic you get different results (even with the "Option VBASupport 1" flag): Sub ExampleMod MsgBox 15.1 Mod 5 'Remainder = 0.1; Returns 0 MsgBox 15.4 Mod 5 'Remainder = 0.4; Returns 0 MsgBox 15.5 Mod 5 'Remainder = 0.5; Returns 0 MsgBox 15.6 Mod 5 'Remainder = 0.6; Returns 0 MsgBox 15.9 Mod 5 'Remainder = 0.9; Returns 0 MsgBox 16 Mod 5 'Remainder = 1; Returns 1 End Sub In summary, by looking at the code in [1] (using Mike's references here), this difference is caused by LO Basic rounding operands before applying the MOD operator. I can add a note about that in the Help, as well as a few more examples to illustrate it better.
(In reply to Rafael Lima from comment #2) This difference is the result of bug 141201. If you change your samples to work not with literals, but with variables, like > a = 15.1 > MsgBox a Mod 5 you will get results consistent with VBA's. So the help should just reflect the correct intended result (rounding of both operands before Mod), and bug 141201 should resolve the inconsistency.
> you will get results consistent with VBA's. > So the help should just reflect the correct intended result (rounding of > both operands before Mod), and bug 141201 should resolve the inconsistency. Hi Mike. Now I understood where the problem is. I initially thought VBA rounded the remainder to the nearest integer. But from your explanation (and after reading more carefully [2]) both VBA and LO Basic (if variables are used) first round each variable to the nearest integer and then apply the MOD operator. Here's the example to demonstrate this: Sub ExampleMod2 a = 16.4 b = 5.9 Print a Mod b 'Prints 4 End Sub It should be noted that BASIC's MOD operator has a different behavior than Calc's MOD function. In Calc =MOD(16.4;5.9) returns 4.6. I'll update the help file to clarify these issues.
Rafael Lima committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/215eec55562243262f2549e2071d8298a659057d tdf#141200 Improve Basic MOD operator help page
anybody really thinking a user will read and remember the manual and will get his work done in a smooth way? will try something with formulas and then automate it with macros and remember oh, MOD works differently? he won't, having two different flavours of MOD in sheet and basic is ... ... Kafka is straightforward compared to that, (it's the level of different rounding in basic and sheet? assume it already hit?) suggestion: three modi: 1. correct math as good as possible, konsistent calculations, 2. compatibility to old calc versions, 3. compatibility to ex$el, everything else: Hell of lazy compromises
(In reply to Commit Notification from comment #5) > Rafael Lima committed a patch related to this issue. Please don't forget to mark this fixed. Thank you!