Macro using MOD and variables produces wrong answer

I’m working on writing a simple macro using BASIC language, MOD and Variables and I can’t seem to get the right answer.

–What works–‘print 3 MOD 1.5’–it prints 0 as you would expect.

'I get and the wrong answer with–;
DIM n as single;
DIM n2 as single;
n = 3;
n2 = 1.5;
print n MOD n2 '–it yields 1;

Can anyone please explain what I am doing wrong?

I have printed the individual variables so I know they are set correctly. (I’ve literally worked on this for hours.)

Also I’ve tried the variables as variant type. That still yields 1.

Build ID: 1:6.1.5-3+deb10u6
I’m using writer to open the Macro Editor. I can’t imagine that mattering.

DIM n as single; DIM n2 as single; n = 3; n2 = 1.5; print n MOD n2 … yields 1

I’d suppose this is the correct result, if it is true, that all MOD operands are rounded to Integer values before the operation is performed - a statement which can be found in Andrew Pitonyak, Macros Explained. OOME Third Edition, Saturday, April 30, 2016, Document Revision: 567, page 65 -

Hence from my point of view the question is: Why does `print 3 MOD 1.5` print `0`?

We obviously have a documentation bug, and a program bug.

The values are indeed rounded to integers (see the code); and it matches what Andrew wrote, and what MS VBA documentation tells.

However, our documentation does not tell about rounding; and its samples imply that floating-point operands are handled as user expects.

And the difference between handling of literals (that likely get truncated) and variables (that get rounded) is a bug.

Filed tdf#141200 and tdf#141201.

AFAIK, in maths, MOD is defined only on natural integers. Signed integers need to define precisely what happens with negative numbers, so there are several possible behaviours. MOD is not defined (nor can be theoretically extended) for fractions (Q) or continuous numbers (R and Z).

An easy way to handle MOD with LO numbers is to round them first (how: floor? ceil? nearest? round to even? to zero? to infinity?). The difference between constants and variables in your case might be explained by a double rounding: first when constant are converted to internal representation, second when variables are considered for the operation. We could also have an optimisation where 3 MOD 1.5 is computed during parsing while n1 MOD n2 is necessarily computed dynamically.

(see the code);

@mikekaganski - I like your (false) assumption, I’ve got a clue, where that “turn-it-to-integers” happens :wink:

To see the rounding vs. truncation difference, better test 5 MOD 2.5.

@anon73440385: sorry, didn’t quite get you - what is false?

@mikekaganski - Nothing, really nothing. Just kidding that you take into account I’d be capable to understand such code - but I appreciate any pointers given, so don’t stop it.

@anon73440385: sorry for that; it was just for curious if you like to look at that - and for myself, to have a code pointer.

The rounding is there, when the GetType() is checked, and then types of aL and aR (left and right operands) are set - always to some integral type: UINT64, INT64, CURRENCY, ULONG, BOOL, LONG; and then original values are converted to the requested types (using Get() calls), and only then the math happens on the results of the conversion.

Open Office Documentation regarding MOD:
“This function is implemented as number - divisor * INT( number/divisor) , and this formula gives the result if the arguments are not integer.”
If you follow the correct operation order. PEMDAS. Then the answer is correct at 0.

I think this other guy answered my question. I have to read it slower.
I was just putting this here to explain when it gets rounded and such.


You seem to assume MOD in Basic is the same thing as function MOD() in Calc formula. That’s not the case.

> I think this other guy answered my question.

May be I completely misunderstand @mikekaganski, but I read him confirming my statement.

@anon73440385: yes, you are absolutely right. And our Calc MOD is implemented here.


Thanks a lot for the explanation!

I see. I hadn’t paid attention to that.
I was using the assumption that the same math would be used. (Just because that would seem right to me i guess.) I’m using several sources at once and obviously didn’t reference Pitonyak last.

It is very odd that the
5 MOD 2.5 =1
3 MOD 1.5 = 0

Am not sure about him agreeing with you. It’s sounding like he’s 100% agreeing with you. I’m going to read it very slowly several more times. I’m out of my depth here.

I guess the answer and work around would be to use Calc to get the value and then run it in the macro with it’s result?

@icsheepnwntr: please don’t introduce that complexity of invoking Calc functions for such a task. Just create a function like

Sub myMOD(a, b)
  myMOD = a - b * Int(a / b)
End Sub

and use it in your code :slight_smile:


Thank you so much.
I had the realization my suggestion was stupid while going out to lunch. :slight_smile:
On the way there I realized I should just use that formula directly.

I appreciate it so much.
Everyone has been a huge help.


doesn’t it need to be a Function instead of a Sub myMOD(a, b) ?

Function myMOD(a, b)
  myMOD = a - b * Int(a / b)
End Function

@anon73440385: thank you! Of course you are absolutely right! I should had tested before writing.


Fix will be available in 7.2.0.