VBA报错：_Worksheet对象的Range方法执行失败，同类语句仅某行出错

项目背景 #

• 开发带排队系统的签到亭，支持多用户打开queueView表单，另有工作站打开signInFrm表单
• 原本日志查看、筛选/排序功能正常，但共享Excel文件无法使用AdvancedFilters，因此采用临时工作簿方案：在reportsButton_Click事件中新建工作簿，复制Log和Search工作表，在临时工作簿内执行筛选操作

问题描述 #

在设置AdvancedFilter所需的Range变量时，三条相似语句中仅第二条执行失败：

• 已确认表单与正确工作簿交互
• 已验证命名范围拼写正确且存在于临时工作簿
• 尝试激活对应工作表无效

相关代码：

'<WITHIN MY LOGSEARCH SUB>
'attempt to see what's happening
tmpSearch.Activate 'tmpSearch is a Worksheet variable set to the temp workbook's 'Search' sheet.

With tmpSearch
     'gather the search criteria
     'pastes the values gathered from userform into sheet to populate the AdvancedFilter using
     ' '.Cells(2,18).Value = variable' statement
     
     Set critRng = .Range("myCriteria")  'this line executes correctly
     Set dataRng = .Range("logSearchRng")  'this line generates the error
     Set resultRng = .Range("copyToRng")
End With

解决方案 #

经排查，报错的logSearchRng命名范围实际属于临时工作簿的tmpLog工作表，而非当前With块的tmpSearch工作表。将该语句移至tmpLog的单独With块后问题解决：

' 修改后的代码示例
tmpSearch.Activate

With tmpSearch
     Set critRng = .Range("myCriteria")
     Set resultRng = .Range("copyToRng")
End With

With tmpLog
     Set dataRng = .Range("logSearchRng")  ' 移至对应工作表的With块中
End With

关键原因 #

命名范围是工作表级别的，在With tmpSearch块中调用.Range("logSearchRng")会尝试在tmpSearch工作表中查找该范围，而实际该范围属于tmpLog工作表，因此触发报错。

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