如何查找Excel单元格与指定字符串间的缺失文本项并解决VBA报错

原代码错误点 #

• 未定义输入参数，无法接收单元格传入的文本内容
• Replace语法错误，没有指定需要替换的目标字符，也没有处理输入文本的空格、大小写匹配问题
• 变量拼写错误：Lena应为Len，函数返回值名称与定义的函数名不匹配
• 替换逻辑存在漏洞，未考虑列表最后一项无尾随逗号的场景，容易出现残留字符或截断错误

可直接使用的修复代码 #

Public Function MissingWords(inputStr As String) As String
    ' 定义固定参考列表，统一输出格式
    Const REF_LIST As String = "Apple,Orange,Grapes,Pineapple,Maa"
    Dim refArr As Variant, inputArr As Variant
    Dim i As Long, j As Long, isExist As Boolean
    Dim result As String
    
    ' 预处理输入文本：移除所有空格、转为小写后分割为数组
    inputStr = Replace(LCase(inputStr), " ", "")
    inputArr = Split(inputStr, ",")
    
    ' 分割参考列表为数组
    refArr = Split(REF_LIST, ",")
    
    ' 遍历参考列表，筛选不存在于输入内容的项
    For i = LBound(refArr) To UBound(refArr)
        isExist = False
        For j = LBound(inputArr) To UBound(inputArr)
            If LCase(refArr(i)) = inputArr(j) Then
                isExist = True
                Exit For
            End If
        Next
        If Not isExist Then
            result = result & refArr(i) & ","
        End If
    Next
    
    ' 移除末尾多余的逗号
    If Len(result) > 0 Then
        MissingWords = Left(result, Len(result) - 1)
    Else
        MissingWords = ""
    End If
End Function

使用方法 #

1. 打开Excel后按Alt+F11调出VBA编辑器，右键当前工作簿选择「插入」-「模块」
2. 将上述代码粘贴到模块窗口后保存
3. 在需要输出结果的单元格输入公式=MissingWords(要对比的单元格)即可，比如对比A1单元格就输入=MissingWords(A1)

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