Microsoft Office Forums - View Single Post - [Solved] Need a Function with Select Case for the Holidays

gmaxey · 01-05-2021, 08:03 AM

Graham has shown you a way using and array. Paul, using fields. You don't really need a select case for the holidays. You could also use an error handler and leverage the fact that keys in collections can't be duplicated:

Code:

Sub Test()
  MsgBox Format(fcnAdvancedDate("1/6/2021"), "MMMM dd, yyyy")
  MsgBox fcnAdvancedDate
End Sub


Function fcnAdvancedDate(Optional StartDate As Variant, Optional DaysToCount As Long = 14) As Date
Dim lngIndex As Long
Dim dateTmp As Date
Dim varHolidays As Variant
Dim colHolidays As New Collection
 varHolidays = Array("01/01/21", "01/18/21", "02/15/21")
 dateTmp = Date
 If IsDate(StartDate) Then dateTmp = StartDate
 For lngIndex = 0 To UBound(varHolidays)
   colHolidays.Add varHolidays(lngIndex), varHolidays(lngIndex)
 Next lngIndex
 lngIndex = 0
 Do
   dateTmp = DateAdd("d", 1, dateTmp)
   Select Case Weekday(dateTmp)
     Case 2 To 6
       On Error Resume Next
       colHolidays.Add Format(dateTmp, "MM/dd/yy"), Format(dateTmp, "MM/dd/yy")
       If Err.Number = 0 Then
         lngIndex = lngIndex + 1
         colHolidays.Remove (colHolidays.Count)
       End If
   End Select
 Loop Until lngIndex = DaysToCount
 fcnAdvancedDate = dateTmp
lbl_Exit:
  Exit Function
End Function