问返回同一工作簿中另一个工作表上的唯一日期数

EN

=WorkingDaysCount(B:B,C:C,K3)

Option Explicit

Public Function WorkingDaysCount(ByRef Dates As Range, ByRef EmployeeRange As Range, ByVal Employee As String) As Integer
    Dim ct As Integer 'counter
    Dim i As Long
    'for looping

    Dim EmployeeDates() As Date 'array to track which dates are already counted
    ReDim EmployeeDates(0)  'create initial

    'loop through all the cells
    For i = 1 To Dates.Count
        If (EmployeeRange(i) = Employee) Then   'if the employee is equal to the employee in that range
            If (Not (IsInArray(Dates(i), EmployeeDates))) Then  'if the date hasn't already been counted
                EmployeeDates(UBound(EmployeeDates)) = Dates(i) 'add the date
                ReDim Preserve EmployeeDates(UBound(EmployeeDates) + 1) 'add another element to the array for the next one
            End If
        End If
    Next i

    WorkingDaysCount = UBound(EmployeeDates)    'since ubound is 0-based this is the total count of dates
End Function

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
    'DEVELOPER: Ryan Wells (wellsr.com)
    'DESCRIPTION: Function to check if a value is in an array of values
    'INPUT: Pass the function a value to search for and an array of values of any data type.
    'OUTPUT: True if is in array, false otherwise
    Dim element As Variant
    On Error GoTo IsInArrayError: 'array is empty
        For Each element In arr
            If element = valToBeFound Then
                IsInArray = True
                Exit Function
            End If
        Next element
    Exit Function
IsInArrayError:
    On Error GoTo 0
    IsInArray = False
End Function