Excel VBA运行时错误'91'：对象变量未设置问题求助

我正在Excel中开发跨表更新的数据库：

• "Line List"工作表：存储管线编号（管道唯一标识）、规格、材质等信息
• "Database"工作表：包含阀门、调压阀等各类仪表对应的表格

我在窗体中创建了两个按钮：

• 基于用户选中单元格的管线数据更新所有仪表表格
• 用全量管线数据更新所有表格

两者均使用类模块及FindMatchingRows类函数，原本运行正常，现报错Run time Error '91' Object variable or with block variable not set，调试指向代码行：matchingRows = oLine.FindMatchingRows(TableName, LineNum)。

相关代码如下：

editTable 子过程 #

Private Sub editTable(TableName As String, LineNum As String, oLine As LineClass)
    Dim oInstrument As Variant
    Dim ws As Worksheet
    Dim tbl As Object
    Dim i As Integer
    i = 1
    Dim matchingRows As Variant
    Set ws = Nothing
    Set tbl = Nothing
    
    Set ws = ThisWorkbook.Sheets("Line List")
    Set tbl = ws.ListObjects("LineTable")

    matchingRows = oLine.FindMatchingRows(TableName, LineNum)
    
    If VarType(matchingRows) = 3 Then
        Exit Sub
    End If


    wsName = "Database"
    Select Case TableName
        Case "LCVTable"
            Set oInstrument = New LCVClass
        Case "VCVTable"
            Set oInstrument = New VCVClass
        Case "OOVTable"
            Set oInstrument = New OOVClass
        Case "FITable"
            Set oInstrument = New FIClass
        Case "PRTable"
            Set oInstrument = New PRClass
        Case "MITable"
            Set oInstrument = New MIClass
        Case "TITable"
            Set oInstrument = New TIClass
        Case "LITable"
            Set oInstrument = New LIClass
        Case "PITable"
            Set oInstrument = New PIClass
        Case Else
            MsgBox "Unexpected Instrument selected"
    End Select
    
    For Each Item In matchingRows
        Dim temp As Integer
        temp = matchingRows(i)
        oInstrument.EditInstrument oLine, temp
        i = i + 1
    Next Item
    
    Set ws = Nothing
    Set tbl = Nothing
    
End Sub

FindMatchingRows 函数 #

Function FindMatchingRows(TableName As String, searchString As String) As Variant
    
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim rng As Range
    Dim foundRows() As Variant
    Dim i As Long
    Dim rowCount As Long
    Dim matchCount As Long
    
    Set ws = Nothing
    Set tbl = Nothing
    ' Initialize worksheet and table
    Set ws = ThisWorkbook.Sheets("Database")  ' Change "Sheet1" to your sheet name
    Set tbl = ws.ListObjects(TableName)

    If tbl.ListRows.count > 0 Then
        lastRow = tbl.Range.Rows.count
    End If
            
    ' Define the range to search in (column 5 of the table)
    Set rng = tbl.ListColumns(5).DataBodyRange
    
    rowCount = rng.Rows.count
    matchCount = 0

    
    ' Loop through each cell in the range
    For i = 1 To rowCount
        If rng.Cells(i, 1).value = searchString Then
            ' Resize array to store the matching row number
            ReDim Preserve foundRows(1 To matchCount + 1)
            foundRows(matchCount + 1) = i
            matchCount = matchCount + 1
        End If
    Next i
    If matchCount = 0 Then
        'MsgBox "No matching lines in " & tableName
        FindMatchingRows = matchCount
        Exit Function
    End If
    ' Return the array of matching row numbers
    FindMatchingRows = foundRows
    
    Set ws = Nothing
    Set tbl = Nothing
    
End Function

我尝试过重启Excel排查oLine变量异常，但问题仍未解决，以下是具体排查思路：

排查思路 #

• 确认oLine对象实例化状态：在调用editTable的代码中，检查是否通过Set oLine = New LineClass完成初始化，且传递过程中未被重置为Nothing。可在editTable开头添加调试代码：If oLine Is Nothing Then MsgBox "oLine未初始化": Exit Sub快速验证。
• 检查FindMatchingRows内部对象引用：
  • 确认Database工作表存在，且传入的TableName与工作表中ListObject的名称完全匹配（注意大小写、空格）；
  • 当仪表表格无数据行时，tbl.ListColumns(5).DataBodyRange会返回Nothing，需添加判断：

    Set rng = tbl.ListColumns(5).DataBodyRange
    If rng Is Nothing Then
        FindMatchingRows = 0
        Exit Function
    End If
    

• 验证类模块函数定义：确认LineClass类模块中的FindMatchingRows是公共函数（开头为Public Function），否则外部无法调用；若类模块疑似损坏，可尝试重新导出再导入。
• 检查参数传递有效性：确认传入的TableName非空且正确，LineNum参数也不为空，避免空值导致查找逻辑异常。

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