VBA查找指定值所在列设全局变量及wsSrc定义报错排查

问题1：col属性运行首行报「需要对象（object required）」 #

错误原因 #

• 类型与赋值关键字不匹配：你将col的返回值定义为Long（值类型），却使用仅能给对象赋值的Set关键字为其赋值，直接触发运行错误。
• 逻辑设计缺陷：Range.Find方法返回值是匹配到的单元格Range对象，不是列号，直接将其赋值给Long类型变量会出现类型不匹配；逐列遍历调用Find的写法效率极低，全表单次搜索即可拿到目标位置。
• 未做结果缓存：属性每次被调用都会重复执行搜索，存在不必要的性能损耗。

修正代码 #

' 模块级缓存变量，仅首次调用属性时执行搜索
Private m_targetCol As Long
Private m_colInit As Boolean

Property Get col() As Long
    Dim findResult As Range
    If Not m_colInit Then
        ' 全表范围搜索，按列顺序查找首次匹配项
        Set findResult = wsSrc.Cells.Find( _
            What:="Value", _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            MatchCase:=False, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext)
        
        If findResult Is Nothing Then
            Err.Raise vbObjectError + 1001, , "指定工作表中未匹配到目标值"
        End If
        m_targetCol = findResult.Column
        m_colInit = True
    End If
    col = m_targetCol
End Property

问题2：wsSrc属性取当前宏所在工作簿工作表时报「下标越界（subscript out of range）」 #

错误原因 #

报错本质是ThisWorkbook.Worksheets("MBI DSCR")未匹配到对应工作表，常见诱因：

• 工作表名称存在肉眼不易识别的差异：名称前后/中间有多余空格、存在全角半角字符差异、包含不可见特殊字符
• 目标工作表是图表工作表，不属于Worksheets集合，需要从Sheets集合访问
• 代码运行时宏所在工作簿并非你预期的文件（比如加载了其他同名宏文件）

修复方案 #

1. 先在VBA编辑器工程资源管理器中确认目标工作表的准确显示名称，若存在前后空格可搭配Trim函数做容错处理
2. 推荐直接使用工作表代码名引用，不受用户手动修改工作表显示名称的影响：点击工程面板中的目标工作表，在属性窗口将其(Name)属性修改为wsSrcMbi，属性代码可简化为无硬编码名称的写法

修正代码 #

Property Get wsSrc() As Worksheet
    ' 容错写法：匹配去除前后空格的表名
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
        If Trim(sht.Name) = "MBI DSCR" Then
            Set wsSrc = sht
            Exit Property
        End If
    Next
    ' 遍历未找到时抛出明确错误
    Err.Raise vbObjectError + 1002, , "当前宏所在工作簿不存在名称为MBI DSCR的工作表"
End Property

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