workday holiday argument doesn't accept array

Question

I'm trying to pass a vba declared array of dates into the Holidays Argument in excel function WorkDay. From the research so far its possible to pass a Range and TechontheNet suggests it is possible to pass an array of date serial numbers. The code below is fine up to the point where the workday function is executed - at which point in bombs out...

Note: EasterDate and HolidayDate are functions that return value "date" format, I've also tried running without these values set, but it makes no difference...

Function WorkDayFiveA(DateofSpend As Date) As Boolean
    Dim WDFive As Date, RefDate As Date
    Dim Holidays(9) As Date
    Dim wf As WorksheetFunction

' load holiday array
    Holidays(0) = DateSerial(Year(Date) - 1, 12, 25)    ' last Christmas
    Holidays(1) = DateSerial(Year(Date) - 1, 12, 26)    ' Last Boxing day
    Holidays(2) = DateSerial(Year(Date), 1, 1)          ' News year day
    Holidays(3) = EasterDate(Year(Date), 1)             ' Good Friday
    Holidays(4) = EasterDate(Year(Date), 2)             ' Easter Monday
    Holidays(5) = PublicHolidayDate(Year(Date), 1)      ' May day
    Holidays(6) = PublicHolidayDate(Year(Date), 2)      ' Spring Holidays
    Holidays(7) = PublicHolidayDate(Year(Date), 3)      ' Spring Holidays
    Holidays(8) = DateSerial(Year(Date), 12, 25)        ' next Christmas day
    Holidays(9) = DateSerial(Year(Date), 12, 26)        ' next Boxing

    Set wf = Application.WorksheetFunction

    WDFive = wf.WorkDay(DateSerial(Year(Date), Month(Date), 1), 4, Holidays)

    If Date < WDFive Then
        RefDate = DateSerial(Year(WDFive), Month(WDFive) - 1, 1)
    Else
        RefDate = DateSerial(Year(WDFive), Month(WDFive), 1)
    End If

    If DateofSpend < RefDate Then
        WorkDayFiveA = True
    Else
        WorkDayFiveA = False
    End If
End Function

Any help gratefully received.

Answer 1

This problem seems appear sometimes in Excel 2016 and always in Excel 2013 and earlier versions.

A possible solution - the Holdiay array should be consisted of constant serial numbers to work, e.g. declare it as Long, not as Date:

Dim Holidays(9) As Long
Holidays(0) = DateSerial(Year(Date) - 1, 12, 25)    ' last Christmas
Holidays(1) = DateSerial(Year(Date) - 1, 12, 26)    ' Last Boxing day
Holidays(2) = DateSerial(Year(Date), 1, 1)          ' News year day

The documentation of WorkDay:

Holidays - an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

---

For me (Excel 2013 x32) this code works, but does not work if I declare holidays(3) as Date:

Public Sub TestMe()

    Dim wf              As WorksheetFunction
    Dim holidays(3)     As Long 'As Date does not work
    Dim wdFive          As Date

    Set wf = Application.WorksheetFunction

    holidays(0) = DateSerial(Year(Date) - 1, 12, 25)    ' last Christmas
    holidays(1) = DateSerial(Year(Date) - 1, 12, 26)    ' Last Boxing day
    holidays(2) = DateSerial(Year(Date), 1, 1)          ' News year day

    wdFive = wf.WorkDay(DateSerial(Year(Date), Month(Date), 1), 4, holidays)
    Debug.Print wdFive

End Sub

Answer 2

Try to create hidden worksheet with a named range "Holidays" on it. Put all of the holiday dates there. (You can use your functions EasterDate and PublicHolidayDate as regular if they are public). In you code instead loading holiday array put:

 Set Holidays = Names("Holidays").RefersToRange