如何将VBA数组作为range参数用于SUMIF等工作表函数？

错误原因 #

• 自定义函数UnitCheckArr未设置返回值，函数执行完成后生成的数组直接被销毁，外部程序无法调用该数组
• WorksheetFunction.SumIf的求和区域参数仅支持传入工作表Range对象，不支持直接传入VBA内存数组，代码中Worksheets("Sheet1").Range("UnitCheckArr")是尝试读取工作表的命名区域，和你定义的VBA函数完全无关，因此触发对象定义错误
• 原代码中SUMIF的第二个参数"I2"是匹配字符串"I2"，如果需要匹配I2单元格的实际值，需要去掉引号改为Range("I2").Value

最优实现方案（无辅助列，内存运算） #

直接用VBA循环实现SUMIF的匹配逻辑，全程操作内存数组，不需要调用工作表函数，性能更稳定：

第一步：修改数组生成函数，增加返回值 #

Function UnitCheckArr() As Long()
    Dim UnitValueArr(2 To 250) As Long
    Dim UnitValue As Long
    For UnitValue = LBound(UnitValueArr) To UBound(UnitValueArr)
        UnitValueArr(UnitValue) = Cells(UnitValue, 4) * Cells(UnitValue, 6)
    Next UnitValue
    UnitCheckArr = UnitValueArr '将生成的数组作为函数返回值
End Function

第二步：修改求和逻辑，替换SUMIF调用 #

根据你的需求二选一即可：

场景1：K2:K250所有单元格统一填充「I列等于I2对应值」的求和结果

Sub NetSumIF()
    If Application.WorksheetFunction.CountA(Range("F2:F250")) = 0 Then
        Dim sumArr As Variant, criteriaVal As Variant, total As Long
        Dim i As Long
        ' 读取匹配条件，如需要匹配字符串"I2"可改为 criteriaVal = "I2"
        criteriaVal = Worksheets("Sheet1").Range("I2").Value
        ' 获取预计算的数组
        sumArr = UnitCheckArr
        ' 循环匹配求和
        total = 0
        For i = 2 To 250
            If Worksheets("Sheet1").Cells(i, "I").Value = criteriaVal Then
                total = total + sumArr(i)
            End If
        Next i
        ' 结果写入K列
        Worksheets("Sheet1").Range("K2:K250").Value = total
    End If
End Sub

场景2：K列每行对应当前行I列的值作为条件，逐行求和

Sub NetSumIF()
    If Application.WorksheetFunction.CountA(Range("F2:F250")) = 0 Then
        Dim sumArr As Variant, resArr(2 To 250) As Long
        Dim i As Long, j As Long
        sumArr = UnitCheckArr
        ' 逐行匹配求和
        For i = 2 To 250
            Dim criteriaVal As Variant, tempSum As Long
            criteriaVal = Worksheets("Sheet1").Cells(i, "I").Value
            tempSum = 0
            For j = 2 To 250
                If Worksheets("Sheet1").Cells(j, "I").Value = criteriaVal Then
                    tempSum = tempSum + sumArr(j)
                End If
            Next j
            resArr(i) = tempSum
        Next i
        ' 结果批量写入K列
        Worksheets("Sheet1").Range("K2:K250").Value = Application.Transpose(resArr)
    End If
End Sub

备选方案（保留SUMIF调用） #

如果一定要使用SUMIF函数，可以把生成的数组先写入临时列，再调用SUMIF，使用后可删除临时列内容：

Sub NetSumIF()
    If Application.WorksheetFunction.CountA(Range("F2:F250")) = 0 Then
        Dim sumArr As Variant, tempRng As Range
        sumArr = UnitCheckArr
        ' 把数组写入临时列Z列
        Set tempRng = Worksheets("Sheet1").Range("Z2:Z250")
        tempRng.Value = Application.Transpose(sumArr)
        ' 调用SUMIF
        Worksheets("Sheet1").Range("K2:K250").Value = Application.WorksheetFunction.SumIf( _
            Worksheets("Sheet1").Range("I2:I250"), _
            Worksheets("Sheet1").Range("I2").Value, _
            tempRng _
        )
        ' 清空临时列
        tempRng.ClearContents
    End If
End Sub

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