如何调用引用SpecialCells的VBA子过程避免所选单元格无数据报错

问题表现 #

• 主脚本用于串联多套流程，生成发给不同收件人、附带对应独立附件的邮件，运行过程中会依次调用多个子过程完成附件准备、内容填充等操作。
• 其中Distribution子过程单独在目标工作簿下运行时，可完全正常完成邮件生成流程，无任何报错。
• 主脚本全流程运行时，Distribution子过程在执行到代码行For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)时中断，抛出错误所选单元格中无数据。
• 曾尝试在该报错代码执行前手动激活目标工作簿Free Trade Zone Weekly Reports.xlsm，问题仍未解决。

报错核心原因 #

• 对象引用存在无绑定的模糊匹配：原代码中Set sh = Sheets("Sheet1")语句未指定所属工作簿，默认引用当前活动工作簿下的Sheet1。主脚本运行过程中会多次切换处理中的工作簿，即使临时激活目标工作簿，只要对象赋值瞬间活动工作簿不是目标文件，sh对象就会指向错误的工作表，对应工作表B列不存在符合匹配条件的常量值，触发报错。
• SpecialCells方法的固有触发规则：当指定范围内无符合匹配条件的单元格时，该方法会直接抛出运行时错误，不会返回空集合，一旦引用范围错误就会直接中断流程。

正确调整方案 #

• 给所有工作表、范围对象绑定明确的工作簿归属，消除对活动工作簿状态的依赖：
  替换原有的模糊赋值语句，新增目标工作簿的对象定义：

  Dim wbTarget As Workbook
  ' 直接通过工作簿名称绑定目标文件，不依赖激活状态
  Set wbTarget = Workbooks("Free Trade Zone Weekly Reports.xlsm")
  ' 从固定的目标工作簿下获取工作表对象
  Set sh = wbTarget.Sheets("Sheet1")
  

• 为SpecialCells方法增加容错判断，避免空匹配时直接中断：
  在调用该方法前增加错误捕获逻辑，判断是否存在匹配的常量单元格：

  Dim rngMatched As Range
  ' 临时屏蔽空匹配场景下的系统报错
  On Error Resume Next
  Set rngMatched = sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
  On Error GoTo 0
  ' 无匹配单元格时，先恢复应用状态设置再退出，避免全局配置卡死
  If rngMatched Is Nothing Then
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      MsgBox "目标工作表B列未检测到有效常量收件人信息"
      Exit Sub
  End If
  ' 用校验后的范围替换原循环的取值范围
  For Each cell In rngMatched
  

• 删除无实际作用的激活语句Windows("Free Trade Zone Weekly Reports.xlsm").Activate，所有对象绑定完成后，流程不需要将目标工作簿切换为活动状态即可正常执行。

调整后效果 #

• 所有工作表引用都通过固定的工作簿对象层级获取，主脚本运行过程中即使切换其他工作簿的活动状态，也不会改变sh对象的指向正确性。
• 增加空范围的容错处理，避免SpecialCells在无匹配值时直接抛出运行时错误中断主流程。
• 原有邮件生成、收件人筛选、附件添加的核心逻辑保持不变，不会影响原有功能的执行效果。

内容的提问来源于stack exchange，提问作者Jorge Luis Martínez Nieto