Excel中用自定义VBA函数结合数组公式统计带背景色单元格报错

问题描述 #

自定义VBA函数FillIndicator可判断单个单元格是否存在背景色，单个调用累加（如=FillIndicator(C5)+FillIndicator(C6)+FillIndicator(C7)）能正常计算，但使用数组公式=SUM(FillIndicator(C5:C7))（按Ctrl+Shift+Enter确认）时返回#VALUE!错误，使用版本为Excel 365 2208。

报错原因 #

原函数的参数color As Range仅支持单个单元格输入，当传入多单元格区域时，函数无法自动遍历每个单元格进行处理，导致数组运算逻辑失效。

修正后的VBA函数 #

Function FillIndicator(rng As Range) As Variant
    Dim result() As Integer
    Dim cell As Range
    Dim i As Integer
    
    ReDim result(1 To rng.Cells.Count)
    
    i = 1
    For Each cell In rng
        result(i) = IIf(cell.Interior.ColorIndex = -4142, 0, 1)
        i = i + 1
    Next cell
    
    FillIndicator = result
End Function

使用方法 #

• 替换原VBA函数后，直接使用公式=SUM(FillIndicator(C5:C7))，Excel 365中无需按Ctrl+Shift+Enter，回车即可得到结果；
• 也可使用=SUMPRODUCT(FillIndicator(C5:C7))，同样无需数组公式确认就能正常计算。

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