VBA自定义PARSE函数提示‘Function is invalid’，求排查与解决

报错核心原因 #

原代码存在几个致命问题，直接导致「Function is invalid」提示：

• 错误使用Set关键字：Set仅用于给对象类型变量（比如Range、Worksheet）赋值，像Integer、Boolean、Double这类基础数据类型直接用=赋值即可，你代码里所有Set i=1、Set Row=Col1.Row这类写法都是错误的。
• UDF违规操作单元格：Excel自定义工作表函数（UDF）不能直接修改其他单元格的值，也不能使用ActiveCell、Activate这类操作——UDF的核心作用是返回计算结果到调用它的单元格，而非主动修改其他单元格。
• 数据范围逻辑错误：遍历从第1行开始不符合输入参数的设计（输入是第一列任意单元格），且空行判断逻辑可能导致无限循环（如果数据列末尾没有空行）。
• 变量类型限制：将Col1Value定义为Double，无法匹配文本类型的关键字，应该用Variant兼容更多数据类型。

修复后的代码 #

下面是实现你需求的正确UDF，它会返回匹配关键字的第二列数据数组，需要以数组公式的形式调用：

Public Function PARSE(Col1 As Range, key As Variant, Col2 As Range) As Variant
    Dim dataStartRow As Long
    Dim lastRow As Long
    Dim col1Index As Integer
    Dim col2Index As Integer
    Dim resultArr() As Variant
    Dim i As Long
    Dim count As Long
    
    ' 获取列索引和数据起始行
    col1Index = Col1.Column
    col2Index = Col2.Column
    dataStartRow = Col1.Row
    
    ' 找到第一列最后一行数据（避免空行判断的无限循环）
    lastRow = Cells(Rows.Count, col1Index).End(xlUp).Row
    
    ' 先统计匹配的数量
    count = 0
    For i = dataStartRow To lastRow
        If Cells(i, col1Index).Value = key Then
            count = count + 1
        End If
    Next i
    
    ' 初始化结果数组
    ReDim resultArr(1 To count, 1 To 1) As Variant
    
    ' 填充结果数组
    count = 0
    For i = dataStartRow To lastRow
        If Cells(i, col1Index).Value = key Then
            count = count + 1
            resultArr(count, 1) = Cells(i, col2Index).Value
        End If
    Next i
    
    ' 返回数组
    PARSE = resultArr
End Function

使用方法 #

1. 选中需要输出结果的多行单元格（比如预估有5个匹配结果，就选5行1列）
2. 在编辑栏输入公式：=PARSE(A2,"关键字",B2)（替换成你的实际单元格和关键字）
3. 按下Ctrl+Shift+Enter组合键，以数组公式的形式确认输入

说明 #

• 函数会从你指定的Col1单元格所在行开始，向下遍历第一列的所有有效数据
• 匹配到关键字后，将对应行第二列的数据收集到数组中返回
• 必须用数组公式调用，才能将结果输出到多行单元格中

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