(In reply to libre officer from comment #0)
> Description:
> Calling Calc functions which accepts additional argument as an array of
> values throws a BASIC runtime error.

Are you sure this can work ?

According to:
https://help.libreoffice.org/7.1/en-US/text/scalc/01/func_workday.html?DbPAR=CALC#bm_id3149012

Holidays is a list of optional holidays.
Enter a *cell* *range* in which the holidays are listed individually.

(In reply to Oliver Brinzing from comment #1)
> (In reply to libre officer from comment #0)
> > Description:
> > Calling Calc functions which accepts additional argument as an array of
> > values throws a BASIC runtime error.
> 
> Are you sure this can work ?
> 
> According to:
> https://help.libreoffice.org/7.1/en-US/text/scalc/01/func_workday.
> html?DbPAR=CALC#bm_id3149012
> 
> Holidays is a list of optional holidays.
> Enter a *cell* *range* in which the holidays are listed individually.

That's the problem, I am obliged to provide a cell range object instead of a simple array of date values.

Actually, what I do is to create new temporary Calc file, and insert value from another generated date list, then get the cell range to feed it to `workday`, then close that Calc file.

I use Base form, so it is slower to create a Calc file and do all the stuffs I mentioned. Instead, it would be much convenient and faster if I feed `workday` directly with  array of values without the need to create a cell range.

Other Calc functions don't needs to create a new sheet, you just call it (weekday, weeknum, etc.).

no error with meguro's code.

Version: 7.1.0.0.alpha0+ (x64)
Build ID: 63d4e8aa10186b4ed0e3106c116ad25c5fb5ed6d
CPU threads: 4; OS:Windows 10.0 Build 18363; UI render: default; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: CL

the same error as the reporter's when I ran the reporter's code, whether this should be marked WONTFIX or ASSIGNED.

https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx?r=80f28e37#402
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr3.cxx?r=9d4c36d7#4125
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr3.cxx?r=9d4c36d7#3953

(In reply to libre officer from comment #2)
> (In reply to Oliver Brinzing from comment #1)
> > (In reply to libre officer from comment #0)
> > > Description:
> > > Calling Calc functions which accepts additional argument as an array of
> > > values throws a BASIC runtime error.
> > 
> > Are you sure this can work ?
> > 
> > According to:
> > https://help.libreoffice.org/7.1/en-US/text/scalc/01/func_workday.
> > html?DbPAR=CALC#bm_id3149012
> > 
> > Holidays is a list of optional holidays.
> > Enter a *cell* *range* in which the holidays are listed individually.
> 
> That's the problem, I am obliged to provide a cell range object instead of a
> simple array of date values.
> 
> Actually, what I do is to create new temporary Calc file, and insert value
> from another generated date list, then get the cell range to feed it to
> `workday`, then close that Calc file.
> 
> I use Base form, so it is slower to create a Calc file and do all the stuffs
> I mentioned. Instead, it would be much convenient and faster if I feed
> `workday` directly with  array of values without the need to create a cell
> range.
> 
> Other Calc functions don't needs to create a new sheet, you just call it
> (weekday, weeknum, etc.).

Any enhancement to support array argument instead of cell range, which make it mendatory to create Calc window ?

Hello libre officer,
A new major release of LibreOffice is available since this bug was reported.
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.

(In reply to Xisco Faulí from comment #10)
> Hello libre officer,
> A new major release of LibreOffice is available since this bug was reported.
> Could you please try to reproduce it with the latest version of LibreOffice
> from https://www.libreoffice.org/download/libreoffice-fresh/ ?
> I have set the bug's status to 'NEEDINFO'. Please change it back to
> 'UNCONFIRMED' if the bug is still present in the latest version.

Hello Xisco,

It still raises exceptions for the cases as follow (see code comments):

Sub Main
	oFA = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	holidays = Array("2020-02-22", "2020-02-23")
	
	 ' Gives 24.02.2020, which is the result in Calc
	Print CDate( oFA.callFunction( "WORKDAY", Array( "2020-02-21", 1 )) )

	 ' Runtime Error. Expected result should be : 25.02.2020
	Print CDate( oFA.callFunction( "WORKDAY", Array( "2020-02-21", 1, "2020-02-24" )) )
	
	' This one is not accepted as the third argument is a list of holidays
	Print CDate( oFA.callFunction( "WORKDAY", Array(  "2020-02-21", 1, holidays)))

End Sub

The second `Print` does not accept the 3rd argument. even though it is accepted in `Calc`.

I wish that in the last `Print`, I can pass the 3rd argument as a list of holidays to exclude from `WORKDAY`.



Reproducible: Always

User Profile Reset: No

Version: 7.1.0.3 (x64) / LibreOffice Community
Build ID: f6099ecf3d29644b5008cc8f48f42f4a40986e4c
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: fr-CH (fr_CH); UI: en-GB
Calc: CL

Note that this

  oFA.callFunction( "WORKDAY", Array( "2020-06-08", 1, "2020-06-09", "2020-06-10"))

can not work as it passes 4 arguments to WORKDAY that expects only 3.

This

  oFA.callFunction( "WORKDAY", Array( "2020-06-08", 1, holidays))

could work if holidays was declared as a two-dimensional array (array of arrays) as the definition of XFunctionAccess::callFunction() states, see
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XFunctionAccess.html#aaed56cc122754b1cfaf2edf7fcca91a8

So either for a column vector of rows

    dim holidays(1,0)
    holidays(0,0) = "2020-06-09"
    holidays(1,0) = "2020-06-10"

or for a row vector of columns

    holidays = Array(Array("2020-06-09", "2020-06-10"))

However, WORKDAY() for Excel legacy compatibility is an Add-In function (hence the code pointers of comment 7 are unrelated) that does not parse dates, all date values MUST be passed as date serial numbers, so this specific call wouldn't work anyway.


Summarized, this is actually a request for enhancement (RFE) to accept one-dimensional arrays as well, which I'm reluctant to implement because it would be an incompatibility and such newer macro code would not run with an older LibreOffice release.

So => WF;

and possibly a documentation issue - we need a note somewhere where we discuss calling Calc functions from API, that where ranges are expected, a two-dimensional array must be used.

By the way, IIRC, we automatically convert two-dimensional arrays into array of arrays (so the mixed terminology in comment 12 has a point :D)