VBA报错‘Object Variable or With Block Variable Not Set’排查求助

核心问题分析 #

1. getdata函数中ws变量未初始化：声明了ws As Worksheet但未用Set指定具体工作表，调用getcolumnindex(ws, ...)时传入的是未设置的空对象，导致getcolumnindex内的sht参数无效，执行sht.Range时触发“Object Variable or With Block Variable Not Set”错误。
2. getcolumnindex函数未返回值：函数定义为As Integer但未给getcolumnindex赋值，调用后无法获取有效列索引。
3. 未处理Find方法找不到匹配项的场景：当Find未找到目标列名时，name变量为Nothing，后续直接操作会触发错误。
4. Cells未绑定指定工作表：getdata中Cells(WDrow, parametercol)默认使用活动工作表，易导致数据读取错误。

修正后的完整代码 #

Main 过程 #

Public Sub Main() 
    Dim wb As Workbook, ws As Worksheet, i As Range, dict As Object
    Dim value As Long 

    Set wb = ThisWorkbook 
    Set ws = wb.Worksheets("Sheet1") 
    Set dict = CreateObject("scripting.dictionary") 

    For Each i In ws.Range("E2:E15").Cells 
        sysnum = i.Value 
        sysrow = i.Row 
        syscol = i.Column 

        Dim colIndex As Integer
        colIndex = getcolumnindex(ws, "Range (nm)")
        value = getdata(sysrow, "Range (nm)", ws) ' 传入指定工作表
    Next i 
End Sub 

getcolumnindex 函数 #

Function getcolumnindex(sht As Worksheet, colname As String) As Integer 
    Dim name As Range, colind As Integer 
    colind = 0 ' 初始化默认值
    
    Set name = sht.Range("A1:Z2").Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True) 
    If Not name Is Nothing Then 
        colind = name.Column 
        MsgBox name.Value & " column index is " & colind
    Else
        MsgBox "未找到列名：" & colname
    End If
    
    getcolumnindex = colind ' 给函数赋值返回值
End Function 

getdata 函数 #

Function getdata(WDrow As Integer, parametercol As String, ws As Worksheet) As Variant 
    Dim colIndex As Integer
    
    colIndex = getcolumnindex(ws, parametercol)
    If colIndex > 0 Then
        getdata = ws.Cells(WDrow, colIndex) ' 绑定指定工作表的单元格
        MsgBox getdata
    Else
        MsgBox "无法获取数据：未找到目标列"
        getdata = Empty
    End If
End Function 

关键优化说明 #

• 所有工作表操作均绑定指定对象，避免依赖活动工作表引发的不确定性。
• 增加未找到列名时的提示，便于快速定位问题。
• 函数明确赋值返回值，确保调用方能获取有效结果。

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