VBA中Workday函数调用报错：无法获取WorkDay属性求助

问题根源 #

VBA的WorksheetFunction.WorkDay对节假日参数的要求比Excel手动调用更严格：它需要接收一维数组或单个日期值，直接传入多单元格Range对象时，Range的默认返回值是二维数组（即使是单列区域），导致函数无法识别，从而抛出"Unable to get the WorkDay property of the WorksheetFunction class"错误。

解决方法 #

以下两种方案均可解决该问题：

方案1：将节假日区域转换为一维数组

把单列Range的二维值转成一维数组，适配WorksheetFunction.WorkDay的参数要求：

Sub WorkdayMacro()
    Dim PaymDate As Date
    Dim holidays As Variant
    Dim paths As Long
    paths = .Range("paths").Value

    PaymDate = .Range("First_Payment_Date").Value
    ' 把单列区域转成一维数组
    holidays = Application.Transpose(.Range("B1:B5").Value)

    Dim row As Long
    row = 5
    Dim i As Long
    Dim targetDate As Date

    For i = 0 To paths - 1
        targetDate = Application.WorksheetFunction.EDate(PaymDate, i)
        If Application.WorksheetFunction.WorkDay(targetDate - 1, 1, holidays) = targetDate Then
            .Range("A" & row).Value = targetDate
        Else
            .Range("A" & row).Value = Application.WorksheetFunction.WorkDay(targetDate, 1, holidays)
        End If
        row = row + 1 ' 补充行号递增，避免覆盖同一单元格
    Next i
End Sub

方案2：改用Application.WorkDay（不带WorksheetFunction）

Application.WorkDay对参数兼容性更强，可直接接收Range对象，且出错时返回错误值而非抛出运行时错误，代码更健壮：

Sub WorkdayMacro()
    Dim PaymDate As Date
    Dim holidays As Range
    Dim paths As Long
    paths = .Range("paths").Value

    PaymDate = .Range("First_Payment_Date").Value
    Set holidays = .Range("B1:B5")

    Dim row As Long
    row = 5
    Dim i As Long
    Dim targetDate As Date
    Dim checkDate As Variant

    For i = 0 To paths - 1
        targetDate = Application.EDate(PaymDate, i)
        checkDate = Application.WorkDay(targetDate - 1, 1, holidays)
        ' 先检查是否为有效日期，避免错误
        If Not IsError(checkDate) And checkDate = targetDate Then
            .Range("A" & row).Value = targetDate
        Else
            .Range("A" & row).Value = Application.WorkDay(targetDate, 1, holidays)
        End If
        row = row + 1 ' 补充行号递增
    Next i
End Sub

额外注意事项 #

• 确保B1:B5区域内的单元格都是有效日期格式，无文本型日期或空值，否则可能导致函数参数异常。
• 原代码未在循环中递增row变量，会持续覆盖A5单元格，需添加row = row + 1实现逐行写入。

内容的提问来源于stack exchange，提问作者mathi