如何将Excel筛选条件传入自定义VBA ProcessCells函数？

核心思路 #

要让ProcessCells支持类似COUNTIF的动态筛选条件，核心是将用户传入的条件表达式作为模板，针对目标工作表的每一行对应列的单元格值进行替换后，通过VBA的表达式计算引擎验证条件是否成立。

COUNTIF函数参考（翻译自官方文档） #

COUNTIF函数用于对指定区域中满足单个条件的单元格进行计数，语法为COUNTIF(区域, 条件)。其中条件可以是数字、表达式、单元格引用或文本字符串，例如">100"、"苹果"或A2。

改造后的VBA代码 #

Function ProcessCells(sheetName As String, ParamArray MyParams() As Variant) As String
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, j As Long
    Dim colIndex As Integer
    Dim conditionTemplate As String, evaluatedCondition As String
    Dim cellVal As Variant
    Dim isMatch As Boolean
    
    ' 校验参数格式：列名与条件必须成对传入
    If UBound(MyParams) Mod 2 <> 1 Then
        ProcessCells = "参数错误：列名和条件需成对传入"
        Exit Function
    End If
    
    ' 获取目标工作表
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    If ws Is Nothing Then
        ProcessCells = "工作表不存在：" & sheetName
        Exit Function
    End If
    
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Dim matchedRows As Collection
    Set matchedRows = New Collection
    
    ' 遍历数据行（假设第一行为表头）
    For i = 2 To lastRow
        isMatch = True
        ' 遍历每一组列名+条件
        For j = 0 To UBound(MyParams) Step 2
            ' 定位目标列
            colIndex = ws.Rows(1).Find(MyParams(j), LookIn:=xlValues, LookAt:=xlWhole).Column
            If colIndex = 0 Then
                ProcessCells = "列名不存在：" & MyParams(j)
                Exit Function
            End If
            
            cellVal = ws.Cells(i, colIndex).Value
            conditionTemplate = MyParams(j + 1)
            
            ' 将模板中的"Val"替换为当前单元格的值，处理文本值的引号包裹
            If VarType(cellVal) = vbString Then
                evaluatedCondition = Replace(conditionTemplate, "Val", """" & cellVal & """")
            Else
                evaluatedCondition = Replace(conditionTemplate, "Val", cellVal)
            End If
            
            ' 计算条件是否成立
            On Error Resume Next
            isMatch = Application.Evaluate(evaluatedCondition)
            On Error GoTo 0
            
            ' 只要有一个条件不满足，跳过当前行
            If Not isMatch Then Exit For
        Next j
        
        If isMatch Then matchedRows.Add ws.Rows(i)
    Next i
    
    ' 示例：返回符合条件的行数，可替换为你的后续处理逻辑
    ProcessCells = "符合条件的行数：" & matchedRows.Count
End Function

使用示例 #

在Excel单元格中调用时，用Val代表当前列的单元格值，注意字符串需要用双引号转义：

' 筛选CarModel不等于Honda且City是Vancouver或Victoria的行
=ProcessCells("my-sheet-1", "CarModel", "Val <> ""Honda""", "City", "Val = ""Vancouver"" OR Val = ""Victoria""")

' 筛选Price大于Sheet2中A列平均值且City不为空的行
=ProcessCells("my-sheet-1", "Price", "Val > AVERAGE(Sheet2!A:A)", "City", "NOT(ISBLANK(Val))")

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