我需要一个VBA函数来计算两个日期之间的工作日
问题描述:
我有这个:
Public Function WorkingDays(StartDate As Date, _
ByVal EndDate As Date, _
Optional ByVal Holidays As Range, _
Optional ByVal Workdays As Variant) As Long
Dim mpDays As Long
Dim mpHolidays As Variant
Dim mpWorkdays As Variant
Dim i As Long
If Not Holidays Is Nothing Then mpHolidays = Application.Transpose(Holidays)
If IsMissing(Workdays) Then
mpWorkdays = Array(2, 3, 4, 5, 6)
Else
mpWorkdays = Workdays
End If
For i = StartDate To EndDate
If Not IsError(Application.Match(Weekday(i), mpWorkdays, 0)) Then
If IsError(Application.Match(i, mpHolidays, 0)) Then
mpDays = mpDays + 1
End If
End If
Next i
WorkingDays = mpDays
End Function
但是它非常慢,我在工作簿和我的工作表中有130K记录,都使用了此功能.该如何改善?
but it is very slow, I use this function in my Workbook and my sheet I've 130K records. How can it be improved?
答
您是否看到Excel具有NETWORKDAYS和WORKDAYS函数?
Have you seen that Excel has the NETWORKDAYS and the WORKDAYS functions?
与其循环地查看从StartDate到EndDate的日期非常慢,为什么不计算两个日期之间的工作日数,然后遍历假期以查看每个日期是否在StartDate和EndDate之间?
Instead of looping through the dates from StartDate to EndDate which is very slow, why not work out the number of week days between the two dates and then loop through the holidays to see if each date falls between StartDate and EndDate?