NETWORKDAYS.INTL () function does not work 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 selects NetworkDaysand gives:

Compilation error: argument is optional.

A formula works when entered directly into a cell, so it exists, presumably. It does not seem to recognize .INTLas part of the function NetworkDays.

Any ideas on what to do?

Here is the complete 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

Answer 1

You need to write a function with _in VBA instead.

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

intellisense , , , ..

Answer 2

Networkdays_intl() Excel VBA: VDN MSDN.

:

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

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