Excel VBA：如何将表格作为WorkDay函数的节假日列表？报错求解

问题根源 #

WorksheetFunction.WorkDay的holidays参数要求传入一维数组，但通过Range("A1:A10").Value获取的是二维数组（格式为holidaysArray(1 to 10, 1 to 1)），参数类型不匹配直接触发了"Unable to get the WorkDay property"错误。

三种修复方案 #

方案1：直接传入Range对象（最省心） #

不需要转数组，直接把节假日所在的单元格范围作为参数传入：

Sub addWorkingdays()
    Dim holidaysRange As Range
    Set holidaysRange = Workbooks("Current.xlsm").Worksheets("Tools").Range("A1:A10")
    Dim endDate As Date
    daysRemaining = 10
    
    endDate = Application.WorksheetFunction.WorkDay(Now, daysRemaining, holidaysRange)
    workingSheet.Cells(N, 4) = CDate(endDate)
End Sub

方案2：把二维数组转成一维数组 #

如果必须用数组传递，手动将Range返回的二维数组转为一维：

Sub addWorkingdays()
    Dim holidaysArray As Variant
    Dim oneDimHolidays() As Date
    Dim i As Integer
    
    holidaysArray = Workbooks("Current.xlsm").Worksheets("Tools").Range("A1:A10").Value
    ' 初始化一维数组
    ReDim oneDimHolidays(1 To UBound(holidaysArray))
    ' 逐行提取数据转换维度
    For i = 1 To UBound(holidaysArray)
        oneDimHolidays(i) = holidaysArray(i, 1)
    Next i
    
    Dim endDate As Date
    daysRemaining = 10
    
    endDate = Application.WorksheetFunction.WorkDay(Now, daysRemaining, oneDimHolidays)
    workingSheet.Cells(N, 4) = CDate(endDate)
End Sub

方案3：用Application.WorkDay替代WorksheetFunction版本 #

这个版本不会直接抛出运行时错误，而是返回错误值，方便后续做异常处理：

Sub addWorkingdays()
    Dim holidaysArray As Variant
    holidaysArray = Workbooks("Current.xlsm").Worksheets("Tools").Range("A1:A10").Value
    Dim endDate As Variant ' 改为Variant类型接收可能的错误值
    daysRemaining = 10
    
    endDate = Application.WorkDay(Now, daysRemaining, holidaysArray)
    ' 判断计算是否成功
    If IsDate(endDate) Then
        workingSheet.Cells(N, 4) = CDate(endDate)
    Else
        MsgBox "工作日计算失败，请检查节假日数据格式"
    End If
End Sub

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