I messed up the message. If you try to run what I posted here, you get an
Invalid Procedure Call or Argument message. In the 2nd message, the line was
supposed to read
p = Application.Match(D, v, 0)
I've included a modification of the NWrkDays function that applies this lesson
on speed.
I compared 4 functions: (1) the built-in NETWORKDAYS function, (2) your
original code, (3) my first code, and (4) NWrkDaysR. The dates are 5/15/2004,
7/15/2004, weekend days are 1 and 7, the holiday list has 190 entries. The
times are
NETWORKDAYS 1.07
Your original 31.47
My first code 2.45 'holidays read into a VBA array, then use MATCH
NWrkDaysR 1.37 'use MATCH on the source range
IMO NWrkDaysR compares favorably with the NETWORKDAYS, given its additional
functionality.
In the past I have posted code that was FASTER than NETWORKDAYS. AIR, I wrote
it to handle only Sat and Sun as the weekend days. The reason it was faster
was
(1) calculate the number of full weeks between start date and end date and
multiply that by 5
(2) calculate day-by-day only on the "tail", the days in the final partial
week
(3) subtract holidays between start date and end date via a binary search on
the holiday list instead of MATCH
The trade-off was that the holiday list had to be sorted ascending and it
could not include any holidays that fell on a weekend day.
~~~~~~~~~~~~~~~~~~~~~~~~~
Function NWrkDaysR(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Long
Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim DoHolidays As Boolean
DoHolidays = Not (Holidays Is Nothing)
w = Weekday(StartDate - 1)
For i = StartDate To EndDate
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
NWrkDaysR = Count
End Function
|