VBA非激活工作表执行SumIfs存入数组触发1004错误的原因排查

错误根源是混用Range与Columns对象的参数规则，和工作表激活状态无任何关联：

• Worksheet.Columns属性仅接受无行号的列范围标识（如.Columns("BF:BF")、.Columns(58)），作用是返回整列对象，不支持带行号的单元格区域字符串
• 你传入的"BF3:BF" & k是带行号的单元格区域格式，这类参数只能传给Range方法，传给Columns会直接触发对象参数非法的1004错误

当前修改后的代码还有两处引用或逻辑错误：

• 在With ws1块内的.Cells(1, 1)、.Cells(i + 2, 1)属于隐式引用ws1的单元格，但原逻辑中这两个条件值应取自ws3，不补全父对象会导致条件匹配完全错误
• 原代码中第一个For i = 1 To 22循环结束后，i的值为23，后续For j = i To 22的循环范围是23到22，完全不会执行，会导致Arr2所有元素为空

最优写法（沿用Cells坐标，无字符串解析损耗） #

With ws1
    ' 直接取BF列最后非空行的行号，替代原UsedRange的不稳定行数计算
    k = .Cells(.Rows.Count, "BF").End(xlUp).Row
    For i = 1 To 22
        Arr1(i) = wsf.SumIfs( _
            .Range(.Cells(3, 58), .Cells(k, 58)), _
            .Range(.Cells(3, 2), .Cells(k, 2)), _
            ws3.Cells(1, 1), _
            .Range(.Cells(3, 68), .Cells(k, 68)), _
            ws3.Cells(i + 2, 1))
    Next i
    ' 修正Arr2的循环起始值为1
    For j = 1 To 22
        Arr2(j) = 100 * (Arr1(j) / wsf.Sum(Arr1))
    Next j
End With

字符串区域写法（必须使用Range，禁用Columns） #

Arr1(i) = wsf.SumIfs( _
    .Range("BF3:BF" & k), _
    .Range("B3:B" & k), _
    ws3.Cells(1, 1), _
    .Range("BP3:BP" & k), _
    ws3.Cells(i + 2, 1))

原代码的对象引用方式已满足「非激活工作表访问」要求，不需要手动激活任何工作表，完全符合降低界面干扰、减少性能损耗的需求。

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