如何在VBA中使用NETWORKDAYS函数传入节假日数组计算工作日

你的核心问题在于：直接把VBA数组变量插入Excel公式字符串时，Excel无法识别这个数组格式——NETWORKDAYS的holidays参数需要的是单元格区域引用或者用花括号包裹的常量数组（如{"01-Jan-24","15-Feb-24"}），而不是VBA原生的数组对象。下面我会给出两种可行的解决方案，同时优化你原有的节假日获取代码。

先优化节假日获取函数 #

原函数存在两个小问题：固定长度数组可能不够用，且用Format把日期转成文本会影响NETWORKDAYS的识别。改成动态数组直接存储日期值：

Public Function GetHolidayDates() As Variant
    Dim wsInput As Worksheet
    Dim lastRow As Long
    Dim holidayRange As Range
    
    Set wsInput = ThisWorkbook.Sheets("InputSheet")
    lastRow = wsInput.Cells(wsInput.Rows.Count, "D").End(xlUp).Row
    
    ' 处理无节假日的情况
    If lastRow < 2 Then
        GetHolidayDates = Empty
        Exit Function
    End If
    
    ' 获取D列的节假日区域并转成动态数组
    Set holidayRange = wsInput.Range("D2:D" & lastRow)
    GetHolidayDates = holidayRange.Value
End Function

方案一：将VBA数组转为公式可识别的常量数组 #

把数组里的日期转成带引号的字符串，再用花括号包裹，直接拼入公式：

Sub CalculateWorkdays_ConstantArray()
    Dim taskCounter As Long
    Dim holidayArray As Variant
    Dim holidayFormulaStr As String
    Dim i As Long
    
    ' 替换成你实际使用的行号
    taskCounter = 1 
    holidayArray = GetHolidayDates()
    
    ' 构建公式用的常量数组字符串
    If IsEmpty(holidayArray) Then
        holidayFormulaStr = ""
    Else
        holidayFormulaStr = "{"
        For i = LBound(holidayArray, 1) To UBound(holidayArray, 1)
            ' 转成Excel能识别的日期格式字符串
            holidayFormulaStr = holidayFormulaStr & """" & Format(holidayArray(i, 1), "DD-MMM-YY") & """"
            ' 最后一个元素不加逗号
            If i <> UBound(holidayArray, 1) Then
                holidayFormulaStr = holidayFormulaStr & ","
            End If
        Next i
        holidayFormulaStr = holidayFormulaStr & "}"
    End If
    
    ' 写入NETWORKDAYS公式
    With ThisWorkbook.Sheets("Estimation")
        If holidayFormulaStr = "" Then
            .Range("Z" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",Y" & taskCounter & ")"
            .Range("AB" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",AA" & taskCounter & ")"
        Else
            .Range("Z" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",Y" & taskCounter & "," & holidayFormulaStr & ")"
            .Range("AB" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",AA" & taskCounter & "," & holidayFormulaStr & ")"
        End If
    End With
End Sub

方案二：用隐藏单元格区域存储节假日（更适合大量节假日） #

如果你的节假日数量较多，常量数组可能会超出Excel的长度限制，这时可以把节假日写入工作表的隐藏区域，再引用该区域：

Sub CalculateWorkdays_RangeReference()
    Dim taskCounter As Long
    Dim holidayArray As Variant
    Dim wsEstimation As Worksheet
    Dim holidayRange As Range
    Dim lastHolidayRow As Long
    
    taskCounter = 1 ' 替换成实际行号
    Set wsEstimation = ThisWorkbook.Sheets("Estimation")
    holidayArray = GetHolidayDates()
    
    ' 清空并写入节假日到隐藏列（这里用AC列，可自行调整）
    wsEstimation.Columns("AC").Clear
    If Not IsEmpty(holidayArray) Then
        lastHolidayRow = UBound(holidayArray, 1)
        wsEstimation.Range("AC1:AC" & lastHolidayRow).Value = holidayArray
        Set holidayRange = wsEstimation.Range("AC1:AC" & lastHolidayRow)
        ' 隐藏该列避免干扰
        wsEstimation.Columns("AC").Hidden = True
    End If
    
    ' 写入公式
    With wsEstimation
        If Not holidayRange Is Nothing Then
            .Range("Z" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",Y" & taskCounter & "," & holidayRange.Address & ")"
            .Range("AB" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",AA" & taskCounter & "," & holidayRange.Address & ")"
        Else
            .Range("Z" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",Y" & taskCounter & ")"
            .Range("AB" & taskCounter).Formula = "=NETWORKDAYS(X" & taskCounter & ",AA" & taskCounter & ")"
        End If
    End With
End Sub

关键注意点 #

• 确保InputSheet的D列里是真正的日期值（不是文本格式的日期），否则NETWORKDAYS无法正确识别。
• 方案一适合节假日数量较少的场景，方案二更适合大量节假日的情况，性能更稳定。
• 如果你需要频繁更新节假日，建议用方案二，只需要更新InputSheet的D列，公式会自动引用最新的节假日区域。

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