Excel VBA实现筛选区域唯一值计数报错如何解决

报错原因分析 #

1. WorksheetFunction调用失败的原因
   VBA语法中的IF和工作表函数的IF逻辑完全不同，直接将嵌套工作表IF的公式写在WorksheetFunction里时，VBA会优先按VBA的IF语法解析，无法识别工作表的数组判断逻辑，必然会报错。
2. 原有Evaluate调用失败的原因
   你的Evaluate代码存在两个明显的语法错误：

• 变量调用错误：NUM是数值变量，你写的NUM(")-ROW(A2)把NUM当函数调用，属于语法错误
• 范围未完全动态化：公式后半部分的A2:A6没有替换成和NUM关联的动态范围，拼接出的公式本身语法错误，Evaluate无法正常计算

正确实现方案 #

直接用Evaluate方法即可实现动态范围的计算，只要保证公式字符串拼接正确即可，参考代码如下：

Sub 统计筛选后去重非空单元格数()
    ' 可自定义的参数部分
    Const 统计列 As String = "A"
    Const 数据起始行 As Long = 2
    Dim 数据结束行 As Long
    数据结束行 = 6 ' 这里可以换成动态获取最后一行的逻辑：Cells(Rows.Count, 统计列).End(xlUp).Row
    
    ' 拼接公式字符串
    Dim 公式字符串 As String
    公式字符串 = "=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(" & 统计列 & 数据起始行 & _
                ",ROW(" & 统计列 & 数据起始行 & ":" & 统计列 & 数据结束行 & ")-ROW(" & 统计列 & 数据起始行 & "),,1))," & _
                "IF(" & 统计列 & 数据起始行 & ":" & 统计列 & 数据结束行 & "<>""""," & _
                "MATCH(""~""&" & 统计列 & 数据起始行 & ":" & 统计列 & 数据结束行 & "," & 统计列 & 数据起始行 & ":" & 统计列 & 数据结束行 & "&"""",0)))," & _
                "ROW(" & 统计列 & 数据起始行 & ":" & 统计列 & 数据结束行 & ")-ROW(" & 统计列 & 数据起始行 & ")+1),1))"
    
    ' 计算结果
    Dim 符合条件的数量 As Long
    符合条件的数量 = ActiveSheet.Evaluate(公式字符串)
    
    ' 测试输出，可根据需要修改
    MsgBox "统计结果：" & 符合条件的数量
End Sub

注意事项 #

• Evaluate方法原生支持数组公式的计算，不需要额外设置数组公式标记，只要公式本身语法正确即可返回正确结果
• VBA字符串中要表示一个双引号，需要连续写两个双引号，拼接公式时要注意引号转义正确
• 该公式的逻辑和你原来的数组公式完全一致，支持筛选状态下忽略隐藏行、忽略空值、统计去重后的单元格数量

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