Networkdays in VBA

[lucianot]

I know there are several posts about this on the boards, but still can't get it to work:

My goal is to get the number of workdays between two dates in a VBA function. I tried simply using the Advanced Toolpak function NETWORKDAYS, but I get the error message "Sub or function not defined". I did reference to atpvbaen.xls in the Tools > References menu, so I don't know why Excel is not recognizing the function.

I used the following function to test it:

Function CalcNetDays(dInitial As Date, dEnd As Date)
CalcNetDays = NetworkDays(dInitial, dEnd)
End Function

Any reason the function is not working?
Also, I need to add the holidays to the function. The holidays are listed on a worksheet column. Do I simply add the range to the function, like below?

CalcNetDays = NetworkDays(dInitial, dEnd, Range("Holidays"))

 

[jim may]

Try the line:

CalcNetDays = Application.WorksheetFunction.NETWORKDAYS(dInitial,dEnd,A4:A6)

Where A4:A6 is your Holidays

 

[Richard Schollar]

Assuming you've set the reference to atpvbaen.xls then try accessing the function like so:

CalcNetDays = [atpvbaen.xls].NetworkDays(dInitial, dEnd)

Jim, Networkdays is not a member of the WorksheetFunction class so I'm afraid what you've written won't work.

 

[jim may]

Thanks Richard - Good to know (hope to remember),

 

[lucianot]

No success. I'm getting a "Method or data member not found" error.

 

[Richard Schollar]

Have you installed the ATP VBA add-in via Tools>Add-Ins in the normal Excel interface?

 

[NateO]

Alternatively...

http://www.mrexcel.com/forum/showthread.php?t=118262

 

[lucianot]

Yes it is.

 

[Richard Schollar]

Try Nate's suggestion and see if that works for you...

 

[lucianot]

I tried NateO's suggetion of using Application.Run but that didn't work either. If I use the original code, it says atpvbaen.xla is not found. If I change it to atpvbaen.xls, I get "Run method failed".