Workday函数使用变体数组触发1004错误的解决求助

先帮你拆解下遇到的Run-time error '1004'问题，再一步步优化代码实现假期自动加入数组的需求。

错误根源分析 #

你碰到的Workday函数报错，主要来自这几个问题：

• 你的holidays数组里混有空值或非日期类型的值：在FindHolidays函数中，当单元格不是日期时，arrayHoli(i)会保留空值，而WorksheetFunction.WorkDay不允许假期数组包含无效值。
• 依赖Activate切换工作簿容易引发上下文混乱：用Workbooks(wbHoli).Activate操作工作表，可能因为工作簿焦点变化导致引用出错。
• 数组可能存在未初始化的冗余元素：如果lastrowHoli统计的行数里有非日期行，数组会多出空元素。

修复后的完整代码方案 #

1. 重构FindHolidays函数（确保数组仅含有效日期） #

这个函数会自动读取假期文件的最新数据，只收集有效日期，同时摒弃不稳定的Activate操作：

Function FindHolidays() As Variant
    Dim dirHoli As String
    Dim wsHoli As Worksheet
    Dim lastrowHoli As Long
    Dim dateList As Collection
    Dim i As Long
    Dim cellValue As Variant
    
    dirHoli = "\\dklesv701.nilemah.com\common\Diverse\CustomerSatisfactionTeam\JUMP_R2P\8. Repporting\6. CSL2\Helligdage.xlsx"
    
    ' 用对象变量直接绑定工作簿/工作表，避免切换焦点
    With Workbooks.Open(Filename:=dirHoli, UpdateLinks:=False)
        Set wsHoli = .Sheets("Ark1")
        ' 自动更新年份公式（也可以改成匹配reqDate的年份，更灵活）
        wsHoli.Range("B1").Formula = "=Year(Today())"
        .Save ' 保存年份更新
        
        lastrowHoli = wsHoli.Range("A1").CurrentRegion.Rows.Count
        Set dateList = New Collection
        
        ' 遍历行，只收集有效日期
        For i = 1 To lastrowHoli
            cellValue = wsHoli.Cells(i, 2).Value
            If IsDate(cellValue) Then
                dateList.Add CDate(cellValue)
            End If
        Next i
        
        ' 将Collection转为数组（Workday需要数组参数）
        Dim holidaysArray() As Date
        ReDim holidaysArray(1 To dateList.Count)
        For i = 1 To dateList.Count
            holidaysArray(i) = dateList(i)
        Next i
        
        FindHolidays = holidaysArray
        .Close SaveChanges:=False ' 已保存过年份，无需重复保存
    End With
End Function

2. 优化Workday调用逻辑 #

在PrepareSlaveCSL中增加数组空值判断，避免空数组传入引发错误：

Sub PrepareSlaveCSL(slaveDir As String, holidays As Variant)
    Dim MB5Bdate As Date
    
    ' 先判断假期数组是否为空
    If IsEmpty(holidays) Then
        ' 无假期时调用不带假期参数的Workday
        MB5Bdate = WorksheetFunction.WorkDay(reqDate, -3)
    Else
        MB5Bdate = WorksheetFunction.WorkDay(reqDate, -3, holidays)
    End If
    
    ' 这里可以添加MB5Bdate的后续使用逻辑
    ' Debug.Print "计算后的日期：" & MB5Bdate
End Sub

3. 实现新增假期自动同步 #

现在你只要在Helligdage.xlsx的Ark1工作表B列新增有效日期，下次运行MainUpdate时，FindHolidays会自动读取最新行，把新假期加入数组，完全不用修改代码。

如果想更灵活，还可以把年份公式改成匹配reqDate的年份，比如：

wsHoli.Range("B1").Formula = "=Year(" & Format(reqDate, "yyyy-mm-dd") & ")"

这样会根据你的reqDate自动匹配对应年份的假期，不用手动更新年份。

额外注意事项 #

• 确保Helligdage.xlsx的Ark1工作表B列是真正的日期格式，而非文本格式，否则IsDate判断会失效。
• 可以添加上文件打开错误捕获，避免多人编辑时的报错：

On Error Resume Next
Set wbHoli = Workbooks.Open(Filename:=dirHoli, UpdateLinks:=False, ReadOnly:=True)
On Error GoTo 0
If wbHoli Is Nothing Then
    MsgBox "无法打开假期文件，请确认路径正确且未被占用！"
    Exit Function
End If

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