Description: hello, i'm trying to do some 'exact' calculations with calc, and 'rawsubtract' sometimes helps me to avoid idiosyncratic 'rounding' of calc, now i wanted to automate something like this the other day, and 'rawsubtract' is unfortunately not available in basic macros, but you can 'wrap' it in a 'unoservice-function-access-call' like in the example below, and then use it in basic macros (i'd read somewhere and it works fine so far, for other functions as well as for rawsubtract), but for certain values e.g. '=RAWSUBTRACT_A(33,0000000000001;3,0000000000001)' delivers 29,99999999999990000000 as result, while '=RAWSUBTRACT_A(33,0000000000001;3,0000000000001)' correctly calculates to 30,00000000000000000000 so there is a worm in there somewhere, but i can't find where :-( since it's an elementary worm - elementary functions don't work - i think it's important to get rid of it and would be happy if a dev would look for it ... ------------ function rawsubtract_a (ByVal darg1 as Double, darg2 as Double) 'bs: 2021-03-09 'make "RAWSUBTRACT" functionality accessible from basic macros, oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess") result = oFunctionAccess.callFunction( "RAWSUBTRACT", array(darg1, darg2)) rawsubtract_a = result end function 'rawsubtract_a ------------ yet investigated: args look well in basic, result is wrong in already in macro, thus less likely a 'handover problem'? result is exactly 1E-13 off while having capa (granularity) to store values with 1E-14 precision (in the bits / digits not shown in the sheet but accessible with appr. 'rawsubtract'), Steps to Reproduce: 1. place the macro in your macro collection, 2. key '=RAWSUBTRACT(33,0000000000001;3,0000000000001)' in a cell, 3. observe result: 30,00000000000000000000, correct 4. key '=RAWSUBTRACT_A(33,0000000000001;3,0000000000001)' in a cell, 5. observe result: 29,99999999999990000000, wrong Actual Results: 29,99999999999990000000 Expected Results: 30,00000000000000000000 Reproducible: Always User Profile Reset: No Additional Info: Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community Build ID: 722ec600e85cca2e94e82e69f8d13773061172b9 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc:
I tried to test it, but =RAWSUBTRACT_A(33,0000000000001;3,0000000000001) gives #NAME? for me. Could you please attach an example file? Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the document. Version: 7.4.0.0.alpha0+ / LibreOffice Community Build ID: 66a9795f27fda887d9f66d0d8bb196fd636fe452 CPU threads: 8; OS: Linux 5.16; UI render: default; VCL: kf5 (cairo+xcb) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded
Created attachment 178692 [details] Sample ODS w BASIC macro Here's a sample with the macro added. Note that the difference is only visible with decimals set in the Format Cells dialog to some large number, in this case, 20.
Confirmed in: Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 4fc8df93a722a9e623fec2e76a1bdf7e54e8d56b CPU threads: 16; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: en-AU (de_DE); UI: en-US Calc: CL threaded The exact samw results differ.
The problem is in sbxToUnoValueImpl [1], which is used to prepare the data passed from Basic (where the value arrives correctly) to UNO (i.e., to the callFunction method). It uses rtl::math::approxEqual to check if this value should be represented as an integer; and for 33.0000000000001, the approximate equality check succeeds, which makes the value passed as 33 (byte type). Since this code is required for compatibility, I have no idea how to change this. Likely, WONTFIX (unfortunately). [1] https://opengrok.libreoffice.org/xref/core/basic/source/classes/sbunoobj.cxx?r=ff23937c&mo=36076&fi=984#984