Ask Your Question

How can the holiday list be specified for NETWORKDAYS in a macro?

asked 2017-01-20 16:53:08 +0100

Hartley Mays gravatar image

I'm trying to call the NETWORKDAYS function within a macro using the UNO function access service. It works fine when I specify the to and from dates. However, I can't get the optional Holiday list to work. Everything I try results in an Illegal Argument Exception. How should this be done?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-01-21 02:24:43 +0100

Ratslinger gravatar image

If you are looking for the holidays themselves to be located within the macro, I don't believe this is possible. The function is looking for a cell range for the list of holidays.

The following works with the holidays listed on a sheet:

function MYNETWORKDAYS(X,Y)       'Where X = Start Date & Y = End Date'
    dim oFunction as variant
    dim oSheet as object
    dim oRange as object
    dim result as double
    oFunction = createUnoService("")
    oSheet = ThisComponent.Sheets.getByName("SHEET-NAME-CONTAINING-HOLIDAY-DATES")
    dim aArgument(2) as variant
    oRange = oSheet.getCellRangeByName( "G3:G6" )  'Cell range of holiday dates'
    result = oFunction.callFunction( "NETWORKDAYS", aArgument() )
    MYNETWORKDAYS = result
end Function
edit flag offensive delete link more


Great, this works! I had tried both adding an array of dates to the arguments and a range but had improperly extended the range definition with some unneeded and erroneous range address stuff. Thanks, Hartley Cincinnati, Ohio

Hartley Mays gravatar imageHartley Mays ( 2017-01-21 03:22:36 +0100 )edit
Login/Signup to Answer

Question Tools


Asked: 2017-01-20 16:53:08 +0100

Seen: 271 times

Last updated: Jan 21 '17