NETWORKDAYS.INTL() function not working in VBA

Question

I have this line in my macro (Excel 2011 VBA on Mac):

nWeekDaysBetween = (Application.WorksheetFunction.NetworkDays.INTL(pDate, aDate, 7) - 1)

When I run the macro, it highlights NetworkDays and gives:

Compile error: Argument not optional.

The formula works when entered directly into a cell, so it does exist presumably. Seems that it is not recognizing the .INTL as part of the NetworkDays function.

Any ideas for what to do?

Here's the full code for reference:

Sub BHSecondsBetween()

  Dim includeWeekends As Integer: includeWeekends = 0
  Dim weekendType As Integer: weekendType = 7
  Dim openHour As Integer: openHour = 5
  Dim closeHour As Integer: closeHour = 24

'weekendType options: (days of week to include as weekend days)
'1   Saturday and Sunday
'2   Sunday and Monday
'3   Monday and Tuesday
'4   Tuesday and Wednesday
'5   Wednesday and Thursday
'6   Thursday and Friday
'7   Friday and Saturday
'11  Sunday
'12  Monday
'13  Tuesday
'14  Wednesday
'15  Thursday
'16  Friday
'17  Saturday

' Remove rows without responses

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

    Selection.Replace What:="", Replacement:="ThisIsADummyStringForMacro", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Selection.Replace What:="ThisIsADummyStringForMacro", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

    Columns("Q").SpecialCells(xlBlanks).EntireRow.Delete

    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


  Dim NumRows As Integer
  NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
  'Debug.Print "The value of variable NumRows is: " & NumRows

  Dim pDate As Date
  Dim aDate As Date
  Dim nWeekDaysBetween As Integer
  Dim answer As Integer
  Dim pDiff As Long
  Dim aDiff As Long
  Dim pEndOfDay As Date
  Dim aStartOfDay As Date

  Dim endCell As String

  For i = 2 To NumRows
    On Error Resume Next
    'Debug.Print "The value of variable i is: " & i

    pDate = Worksheets("Sheet1").Cells(i, "F").Value
    'Debug.Print "The value of variable pDate is: " & pDate

    aDate = Worksheets("Sheet1").Cells(i, "T").Value
    'Debug.Print "The value of variable aDate is: " & aDate

    If includeWeekends = 1 Then
      nWeekDaysBetween = DateDiff("d", pDate, aDate)
    Else:
      nWeekDaysBetween = (Application.WorksheetFunction.NetworkDays_INTL(pDate, aDate, 7) - 1)
    End If
    'Debug.Print "The value of variable nWeekDaysBetween is: " & nWeekDaysBetween

    If nWeekDaysBetween < 0 Then
      answer = 0
    ElseIf nWeekDaysBetween = 0 Then
      answer = DateDiff("s", pDate, aDate)
    Else:

      If (Hour(pDate) >= closeHour) Then
        pDiff = 0
      ElseIf (closeHour = 24) Or (closeHour = 0) Then
        pEndOfDay = DateSerial(Year(pDate), Month(pDate), Day(pDate)) + TimeSerial(23, 59, 59)
        pDiff = DateDiff("s", pDate, pEndOfDay)
      Else:
        pEndOfDay = DateSerial(Year(pDate), Month(pDate), Day(pDate)) + TimeSerial(closeHour, 0, 0)
        pDiff = DateDiff("s", pDate, pEndOfDay)
      End If

      aStartOfDay = DateSerial(Year(aDate), Month(aDate), Day(aDate)) + TimeSerial(openHour, 0, 0)

      If (Hour(aDate) < openHour) Then
        aDiff = 0
      Else:
        aDiff = DateDiff("s", aStartOfDay, aDate)
      End If

      answer = pDiff + (60 * (closeHour - openHour)) * (nWeekDaysBetween - 1) + aDiff

    End If
    'Debug.Print "The value of variable answer is: " & answer

    endCell = "M" & i
    'Dim endContent As String: endContent = hours & ":" & minutes
    Range(endCell).Value = answer

  Next i

End Sub

It takes [4 arguments](https://support.office.com/en-ie/article/NETWORKDAYS-INTL-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28), so whichever one you're not using, leave a `,` for it? — findwindow, Nov 20 '15 at 19:19
@findwindow - the last 2 arguments are optional so there is no need for any extra commas (unless you want to set the 4th argument without the 3rd) — Scott Holtzman, Nov 20 '15 at 19:30

score 3 · Answer 1 · answered Nov 20 '15 at 19:23

3

You need to write the function with an _ in VBA instead of a .

nWeekDaysBetween = (Application.WorksheetFunction.NetworkDays_Intl(pDate, aDate, 7) - 1)

Using the built-in intellisense can always be useful to find exactly how to write properties, methods, etc.

answered Nov 20 '15 at 19:23

Scott Holtzman

27,099
5
37
72

1

I had actually tried this - then it highlighted NetworkDays_INTL and said "method or data member not found". – andreyman Nov 21 '15 at 03:39
really? i just tested it in my Excel 2011 for Mac and it worked beautifully. – Scott Holtzman Nov 21 '15 at 15:29
That's really weird. I just added my full code to the original post for context - is a variable set incorrectly? Any ideas on what to do? – andreyman Nov 21 '15 at 21:50
at a general glance, everything looks okay. did you debug the code (step through line-by-line) and determine that all the values are as you would expect)? – Scott Holtzman Nov 21 '15 at 22:07

score 0 · Answer 2 · edited Aug 09 '17 at 06:13

Please add following function to access Networkdays_intl() function in Excel VBA: This I found at MSDN site VB code.

Declaration:

 Function NetworkDays_Intl ( _
        Arg1 As Object, _
        Arg2 As Object, _
        Arg3 As Object, _
        Arg4 As Object _
    ) As Double

Usage:

Dim instance As WorksheetFunction
Dim Arg1 As Object
Dim Arg2 As Object
Dim Arg3 As Object
Dim Arg4 As Object
Dim returnValue As Double

returnValue = instance.NetworkDays_Intl(Arg1, _
    Arg2, Arg3, Arg4)
End Function

NETWORKDAYS.INTL() function not working in VBA

2 Answers2