Ask Your Question
0

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
0

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("com.sun.star.sheet.FunctionAccess")
    oSheet = ThisComponent.Sheets.getByName("SHEET-NAME-CONTAINING-HOLIDAY-DATES")
    dim aArgument(2) as variant
    aArgument(0)=X
    aArgument(1)=Y
    oRange = oSheet.getCellRangeByName( "G3:G6" )  'Cell range of holiday dates'
    aArgument(2)=oRange
    result = oFunction.callFunction( "NETWORKDAYS", aArgument() )
    MYNETWORKDAYS = result
end Function
edit flag offensive delete link more

Comments

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

Stats

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

Seen: 271 times

Last updated: Jan 21 '17