修复Excel自定义函数GetTwoLines：跨工作簿查找并返回溢出结果

需求说明 #

现有同一文件夹下两个工作簿：

• TargetFile：存储自定义函数GetTwoLines()，作为UDF调用（例如输入=GetTwoLines(C4)，C4内容用于在SourceFile中定位目标行）
• SourceFile：提供数据来源

函数需实现：

1. 根据传入内容在SourceFile中查找对应行号RowNum
2. 获取SourceFile中Range("B" & RowNum + 1)的单元格内容
3. 内容含换行符时：第一行返回至调用单元格，第二行写入调用单元格右侧相邻单元格
4. 内容无换行符时：内容返回至调用单元格，右侧单元格留空

修复后的VBA代码 #

Function GetTwoLines(lookupValue As Variant) As Variant
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim foundCell As Range
    Dim targetContent As String
    Dim lineArray() As String
    
    ' 处理传入值为空的情况
    If IsEmpty(lookupValue) Then
        GetTwoLines = ""
        Exit Function
    End If
    
    ' 尝试打开SourceFile（同一文件夹下）
    On Error Resume Next
    Set sourceWB = Workbooks("SourceFile.xlsx")
    If sourceWB Is Nothing Then
        Set sourceWB = Workbooks.Open(ThisWorkbook.Path & "\SourceFile.xlsx")
    End If
    On Error GoTo 0
    
    ' 假设数据在SourceFile的第一个工作表，可根据实际修改
    Set sourceWS = sourceWB.Sheets(1)
    
    ' 在SourceFile中查找匹配值，这里默认查找A列，可根据实际调整查找范围
    Set foundCell = sourceWS.Columns("A").Find(What:=lookupValue, LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not foundCell Is Nothing Then
        ' 获取目标单元格内容
        targetContent = sourceWS.Range("B" & foundCell.Row + 1).Value
        
        ' 按换行符拆分内容
        lineArray = Split(targetContent, vbCrLf)
        
        ' 返回第一行到调用单元格
        GetTwoLines = lineArray(0)
        
        ' 写入第二行到右侧单元格，处理数组长度不足的情况
        If UBound(lineArray) >= 1 Then
            Application.Caller.Offset(0, 1).Value = lineArray(1)
        Else
            Application.Caller.Offset(0, 1).Value = ""
        End If
    Else
        ' 未找到匹配值时返回空，右侧单元格也清空
        GetTwoLines = ""
        Application.Caller.Offset(0, 1).Value = ""
    End If
    
    ' 若需要自动关闭SourceFile，可取消下方注释（注意：关闭会中断后续函数调用）
    ' sourceWB.Close SaveChanges:=False
End Function

关键说明 #

• 工作簿打开逻辑：先检查SourceFile是否已打开，未打开则从当前文件夹路径打开，避免重复打开操作
• 查找范围：默认在SourceFile第一个工作表的A列查找匹配值，可根据实际数据位置修改sourceWS.Columns("A")
• 换行符处理：用vbCrLf拆分内容，兼容Windows系统的单元格换行；若为Mac环境，可替换为vbLf
• 跨单元格写入：通过Application.Caller定位函数调用单元格，实现向右侧单元格写入内容，这是UDF操作其他单元格的合法实现方式
• 异常处理：包含空值传入、未找到匹配值的情况处理，避免函数运行报错

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