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"))

 

[NateO]

It's an XLA file. However, what language are you using? Look in your Project Explorer (Ctrl-R), in the VBE, with no open Workbooks. What do you see? You should see some XLA files, pending which Add-ins you've installed. What are their names?

 

[Richard Schollar]

I don't know why it isn't working for you, but I'm thinking that it might be a good idea to use your Office CD to either reinstall Excel and all its components or run a Repair on Office.

 

[NateO]

Not just yet. And I don't mean VBA when I say language, I mean what is your local version of Office, e.g., Italian?

 

[lucianot]

My Excel is in English. The file is ATPVBAEN.XLAM.

I tried that and now I get "Cannot run the macro ATPVBAEN.XLAM!NetworkDays".

 

[NateO]

In your Project Explorer, what does it say in Parenthesis? There should be two names, Name and (Name), you want the latter.

I must admit, I have not tested this in Excel 2007.

Can you post the exact code that's failing? I want to see your syntax.

 

[lucianot]

I used the same syntax a in your post:

Function Nwdys() As Long
Nwdys = Application.Run("ATPVBAEN.XLAM!Networkdays", Date - 2, Date)
End Function


I just tried the different files names.

 

[xld]

Cut your own, it is trivial code

Public Function WorkingDays(StartDate As Date, _
                            ByVal EndDate As Date, _
                            Optional ByVal Holidays As Range) As Long
Dim mpDays As Long
Dim mpHolidays As Variant
Dim i As Long

    If Not Holidays Is Nothing Then mpHolidays = Application.Transpose(Holidays)
    For i = StartDate To EndDate
    
        If Weekday(i, vbMonday) < 6 Then
        
            If IsError(Application.Match(i, mpHolidays, 0)) Then
                mpDays = mpDays + 1
            End If
        End If
    Next i
    WorkingDays = mpDays
   
End Function

 

[lucianot]

That worked!

 

[xld]

You can even have configurable working days

Public Function WorkingDays(StartDate As Date, _
                            ByVal EndDate As Date, _
                            Optional ByVal Holidays As Range, _
                            Optional ByVal Workdays As Variant) As Long
Dim mpDays As Long
Dim mpHolidays As Variant
Dim mpWorkdays As Variant
Dim i As Long

    If Not Holidays Is Nothing Then mpHolidays = Application.Transpose(Holidays)
    
    If IsMissing(Workdays) Then
    
        mpWorkdays = Array(2, 3, 4, 5, 6)
    Else
    
        mpWorkdays = Workdays
    End If
    
    For i = StartDate To EndDate
    
        If Not IsError(Application.Match(Weekday(i), mpWorkdays, 0)) Then
        
            If IsError(Application.Match(i, mpHolidays, 0)) Then
                mpDays = mpDays + 1
            End If
        End If
    Next i
    WorkingDays = mpDays
   
End Function

 

[tinasomot]

Adding Application.WorksheetFunction in front of the function call works for me.
I had to add ATP VBA add-in into my Excel 2010 first (by going to Options> Add-in> Check the ATP VBA box, click OK). My working code looks like this

Dim datedif2 As Double
datedif2 = Application.WorksheetFunction.NetworkDays(duedate, Today, ThisWorkbook.Worksheets("Holidays").Range("A5:A25"))


Thank you!