Number of working days in a month with VBA

[Skippy]

The code below gives me the total number of days in a month. But I would like only the working days. There are many examples using the NetworkDays function but this function is not available in VBA (as far as I can see). Can someone help?

Sub FillWeekdays()
    Dim x As Long
    Dim m As Integer
    Dim y As Integer
    Dim DaysInMonth As Integer
    Dim DestinationRange As Range
    Dim SourceRange As Range
    
    With Worksheets("Data")
        ' define last empty row
        x = .Range("B65536").End(xlUp).Row + 1
        
        ' add new starting date
        .Range("A" & x).Value = frmInput.cboMonth.Text & " 1, " & _
            frmInput.cboYear.Text
        
        ' define variable for number of the month
        m = Month(.Range("A" & x).Value)
        
        ' define variable for the year
        y = frmInput.cboYear.Text
        
        ' get number of days in month
        DaysInMonth = DateSerial(y, m + 1, 1) - DateSerial(y, m, 1)
  
    End With
End Sub

 

[Norie]

You can actually use Networkdays in VBA, you just need to add a reference
to atpvbaen.xls.

That should be available if you have the Analysis Toolpak VBA add-in installed.

 

[Skippy]

you just need to add a reference to atpvbaen.xls.

Norie - Could you explain what you mean? Thanks

 

[Norie]

In VBA if you have the Add-In I mentioned installed then you can use the functions
from it if you add a reference to that library.

To do this goto Tools>References... and select atpvbaen.xls.

Now if you goto the Object Browser all the available functions (and subs)
will be listed in the VBA Functions and Subs class.

 

[Skippy]

Thanks... I did what you suggested. Will now see if I can the function to work with my code

 

[Skippy]

Hmmm... I have the ToolPak installed and NetWorkDays shows in the Object Browser. I tried using Application.WorkbookFunction.NetworkDays but it is not supported.. Is there another way to get the number of working days in a month with VBA?

 

[Norie]

Don't use Application.WorksheetFunction.

 

[Skippy]

Thanks Norie... the following works after following your advice. A tip of the hat

Sub FillWeekdays()
    Dim x As Integer
    Dim DestinationRange As Range
    Dim SourceRange As Range

   With Worksheets("Data")
        ' define last empty row
        x = .Range("B65536").End(xlUp).Row + 1
        
        ' add new starting date
        .Range("A" & x).Value = frmInput.cboMonth.Text & " 1, " & _
            frmInput.cboYear.Text
        
        ' define variable for number of the month
        m = Month(.Range("A" & x).Value)
        
        ' define variable for the year
        y = frmInput.cboYear.Text
        
        ' get number of "working" days in month
        DaysInMonth = networkdays(DateSerial(y, m, 1), DateSerial(y, m + 1, 1)) - 1
     End With
End Sub