arrays - 工作日假期参数不接受数组

Question

我正在尝试将 vba 声明的日期数组传递到 Excel 函数 WorkDay 中的假期参数中。从迄今为止的研究来看，可以传递一个范围，而 TechontheNet 表明可以传递一组日期序列号。下面的代码在执行工作日函数时都很好 - 此时炸弹就出来了......

注意:EasterDate 和 HolidayDate 是返回值“日期”格式的函数，我也尝试在不设置这些值的情况下运行，但没有区别...

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

非常感谢您的帮助。

Answer 1

此问题似乎有时会出现在 Excel 2016 中，但总是出现在 Excel 2013 及更早版本中。

一个可能的解决方案 - Holdiay 数组应由恒定的序列号组成才能工作，例如将其声明为Long，而不是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)          'New Year's Day

The documentation of WorkDay :

假期 - 要从工作日历中排除的一个或多个日期的可选列表，例如州和联邦假期以及 float 假期。该列表可以是包含日期的单元格区域，也可以是表示日期的序列号的数组常量。

<小时/>

对于我(Excel 2013 x32)，此代码有效，但如果我将 holidays(3) 声明为 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