calc: macro: basic: 'MOD' in sheet different to 'MOD' in macros

[newbie-02]

i @all,

in “Macro using MOD and variables produces wrong answer” @icsheepnwntr wrote:

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

and he’s right, afais MOD() in a sheet works with fractions (’=MOD(3;1,5)’ → ‘0’), while MOD in macros (basic) uses integers (‘print 3 MOD 1.5’ → ‘1’ (‘1.5’ is rounded to ‘2’, and the leftover is ‘1’)).

such is at least ‘irritating simple minded users’ and thus - imho - not ‘user friendly’.

the help page cited in the other thread “help page for mod” states ‘Returns the remainder when one integer is divided by another.’, and then explains the implementation which gives fractional results for fractions, which seems to work differently for macros (basic, don’t know how python or other dialects do).

does anyone have a good idea how to make this more harmonious to avoid such irritations? and / or how to avoid that every now and then users produce garbage results when they try calculations in the sheet and then want to automate them with macros?

[Zizi64]

The original function MOD() is for the integer operands only. I suppose it, that the cell function works such way for the “better compatibility” with other spreadsheet applications. (I do not use Excel since 15 years.)

[sokol92]

The documentation for the mod Basic statement is not accurate (complete). A quote from the description of the Mod VBA statement, which is also relevant to Basic:
“The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result”.
The examples in the Star Basic documentation related to the Mod statement also contain errors.

[mikekaganski]

@sokol92: please look at the topic mentioned in the OP’s question, where it’s mentioned that bugs were filed. It’s all already fixed; and even available in online help for 7.2.

[sokol92]

Thanks Mike! It’s not easy for me to get used to the fact that
“latest” <“7.2”.

[schiavinatto]

Hello @newbie-02, in Basic I already had this problem, so I started using the formula:

Dividend - Divisor * INT(Dividend/Divisor)

.According to the Help

sub Test
	var = ( 3 - 1.5 * int(3/1.5))
	msgbox var
End sub

[newbie-02]

hello @Zizi64 and @sokol92,

thanks for your explanations, have to admit that i forgot to think about compatibility,
i understand and accept, to remain compatible with programs with ‘weaknesses’ one must … implement questionable things …

i’d build a workaround some time ago, ‘wrapping’ the ‘sheet function’ MOD in a ‘function call’ … or something like that. With the following macro (function) one can - then with ‘MOD_A’ instead of ‘MOD’ - calculate with fractional MOD’s in other Basic macros:

function mod_a (ByVal darg1 as Double, darg2 as Double)
'bs: 2020-10-27
'make "MOD" functionality accessible from basic macros, 
oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess")
mod_a = oFunctionAccess.callFunction( "MOD", array(darg1, darg2)) 
end function 'mod_a