调用返回Range的VBA函数时出现Object required错误求助

你遇到的「Object required」错误，核心是返回的Range对象已失效：
在findInExcel函数中，找到目标Range后，你立刻关闭了外部工作簿并退出Excel应用。此时rngSize引用的是已关闭工作簿中的Range对象，该对象已被系统销毁，无法传递给调用函数使用。

以下两种方案可根据需求选择：

方案1：返回行/列数值（推荐） #

既然你只需要获取行号、列号这类信息，直接返回数值更安全，避免对象失效问题：

修改后的findInExcel函数：

' 查找值并返回行号（如需列号可修改返回值或返回数组）
Function findInExcel(PATH As String, searchValue As Variant) As Long
    Dim oApp_Excel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim rngSize As Range

    Set oApp_Excel = CreateObject("EXCEL.APPLICATION")
    oApp_Excel.DisplayAlerts = False
    oApp_Excel.Visible = False
    Set oBook = oApp_Excel.Workbooks.Open(PATH)

    Set rngSize = oBook.Sheets("Sheet1").Cells.Find(What:=searchValue)
    If Not rngSize Is Nothing Then
        findInExcel = rngSize.Row ' 返回行号，如需列号改为rngSize.Column
    Else
        findInExcel = -1 ' 未找到返回标记值
    End If

    oBook.Close False ' 无需保存，避免修改原文件
    Set oBook = Nothing
    oApp_Excel.Quit
    Set oApp_Excel = Nothing
End Function

调用代码：

Dim rowNum As Long
rowNum = findInExcel(filePath, "5611")
If rowNum <> -1 Then
    MsgBox "找到的行号：" & rowNum
Else
    MsgBox "未找到目标值"
End If

方案2：保持外部Excel应用打开（返回Range） #

如果必须返回Range对象，需避免在函数内关闭工作簿和Excel应用，后续手动释放资源：

修改后的findInExcel函数：

' 查找值并返回Range（注意：外部Excel应用会保持打开状态）
Function findInExcel(PATH As String, searchValue As Variant) As Range
    Dim oApp_Excel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim rngSize As Range

    Set oApp_Excel = CreateObject("EXCEL.APPLICATION")
    oApp_Excel.DisplayAlerts = False
    oApp_Excel.Visible = False
    Set oBook = oApp_Excel.Workbooks.Open(PATH)

    Set rngSize = oBook.Sheets("Sheet1").Cells.Find(What:=searchValue)
    If Not rngSize Is Nothing Then
        Set findInExcel = rngSize
    End If

    ' 此处不关闭工作簿和Excel，由调用函数处理资源释放
End Function

调用代码：

Dim rr As Range
Set rr = findInExcel(filePath, "5611")
If Not rr Is Nothing Then
    MsgBox "找到的列号：" & rr.Column
    ' 使用完后手动释放资源
    rr.Parent.Parent.Close False ' 关闭工作簿
    rr.Parent.Parent.Application.Quit ' 退出Excel应用
    Set rr = Nothing
Else
    MsgBox "未找到目标值"
End If

• 原函数中PATH和value参数最好指定类型，避免类型不匹配问题；
• 使用Find方法时，建议明确指定LookIn、LookAt等参数，避免因Excel默认设置导致查找结果不符合预期，示例：

  Set rngSize = oBook.Sheets("Sheet1").Cells.Find( _
      What:=searchValue, _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      MatchCase:=False)
  

内容的提问来源于stack exchange，提问作者S.M_Emamian