Bug 102381 - A user function called from a Calc formula wrongly gets passed numeric 0 for missing optional parameters.
Summary: A user function called from a Calc formula wrongly gets passed numeric 0 for ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Macro-VBA Macro-StarBasic
  Show dependency treegraph
Reported: 2016-09-23 08:56 UTC by Wolfgang Jäger
Modified: 2022-09-15 12:54 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:

Demo mentioned in the OP (20.58 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-23 08:56 UTC, Wolfgang Jäger

Note You need to log in before you can comment on or make changes to this bug.
Description Wolfgang Jäger 2016-09-23 08:56:26 UTC
Created attachment 127568 [details]
Demo mentioned in the OP

A function programmed in BASIC for use in Calc may have parameters declared optional. Positions of optional parameter may occur anywhere in the formal parameter list. 
Calls to the function with omitted parameters from a BASIC Sub yield the expected results.

Calls from a formula evaluated for a Calc cell wrongly pass the numeric 0 for omitted optional parameters. 

I only stumbled over the bug recently, but this in a case where I tried to get a function actually usable "in production". I could not find a promising workaround. The severity is therefore higher than normal from my point of view. 

For trailing omitted optional parameters an error Err:511 is thrown by LibO V3.3 Calc if parameter separators are used. No separator, nothing passed. (This also is the behaviour of AOO up to 4.1.2.)

V3.5 and higher accept trailing omitted, even if there is a surplus in positions.
they then also pass a 0 for declared but omitted parameters and nothing for parameter positions in surplus.

See attached demo.
Comment 1 Wolfgang Jäger 2016-09-23 20:45:37 UTC
For clarification: The bug was found using V5.2.0. Only for the report here I also tried many old versions.
Comment 2 Buovjaga 2016-10-11 18:24:09 UTC
Ok I get this in the demo, so I guess it is not as expected:

Arch Linux 64-bit, KDE Plasma 5
Build ID: 65f2d6b1cc40b4b90f8987e8ea14d24b5f38f950
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on October 10th 2016
Comment 3 QA Administrators 2018-10-22 02:48:10 UTC Comment hidden (obsolete)
Comment 4 Buovjaga 2019-08-11 11:47:02 UTC
Still confirmed

Arch Linux 64-bit
Build ID: 37fc9f51a8de11d40632e8cda17ccf1fa4b1f503
CPU threads: 8; OS: Linux 5.2; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 6 August 2019
Comment 6 Mike Kaganski 2022-09-15 12:54:55 UTC
Eike: I intended to write that this all works as intended (except for extra arguments, which is related to bug 150422), but I couldn't find relevant piece in ODFF 1.3; 5.6 "Functions and Function Parameters" there doesn't define a value to use for empty parameter (which is what the "omitted" value *after the separator* is, according to the syntax there). 6.2 "Common Template for Functions and Operators" also doesn't talk about value used in place of empty parameter.