Calling WORKDAY function from a BASIC macro is not working

[papposilene]

Hi everyone,

I need to create a BASIC macro with a function that returns if a given date is, or not, a workday. Actually my code looks like that :

Function myfWorkDay(ADate as Date, Holidays as Object) as String
    Dim oService as Object
    Set oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    myfWorkDay = oService.callFunction("WORKDAY", Array("2024-01-02", 0, Array("2024-01-01", "2024-05-01", "2024-05-08")))
End Function

The holidays object contains a cell range with all french holidays, but i hardcoded all the dates to test the function.

The call to “WORKDAY” function is not working, returning the “NoSuchElementException”. When I read the API reference, NETWORKDAYS and WORKDAY are in the same library. NETWORKINGDAYS works fine, but WORKDAY doesn’t want to.

Do you have any idea?

Thanks a lot.

[Zizi64]

@papposilene

Do you have any idea?

The passed values are pure strings but not dates. The dates are numeric values. Try to pass real DateValues to the function.

[mikekaganski]

oService.callFunction("WORKDAY", Array("2024-01-02", 4, array(array(#2024-01-01#),array(#2024-05-01#),array(#2024-05-08#))))

[papposilene]

It seems that “WORKDAY” needs 3 parameters as date, integer and date. You can’t pass an array of holidays in the third parameter.

[mikekaganski]

Did you test what I wrote above? It works OK with the array.

[Villeroy]

@papposilene

You can’t pass an array of holidays in the third parameter.

callFunction takes 2 arguments

1. Function name WORKDAY
2. Array of 2 or 3 function arguments to WORKDAY:
   2.1. Start date is "2024-01-02" as ISO string in double-quotes or date notation #2024-01-02# or day number 45293
   2.2. Number of workdays is 4
   2.3. Data array of dates in date notation or numbers, but no ISO-dates (why?).
   array(array(#2024-01-01#), array(#2024-05-01#), array(#2024-05-08#)) represents the data array of one column with 3 rows, say A1:A3.
   array(array(#2024-01-01#, #2024-05-01#, #2024-05-08#)) represents the data array of one row with 3 columns, say A1:C1. Both data arrays do work just like they do on sheet.
   array(array(cLng("2024-01-01"), cLng("2024-05-01"), cLng("2024-05-08"))) converts the ISO strings into correct day numbers, given that your document assumes 1899-12-30 00:00:00 as point zero on the time scale.