excel - 工作表名称与目标工作表名称不匹配时的VBA运行时错误9

Question

我有以下代码将具有特定工作表名称的Excel工作表转换为PDF，然后在文件夹中循环。用户可以将工作表名称输入到工具中以循环查找代码。但是，如果工作表名称不正确或不存在，则会显示“运行时错误9:下标超出范围”。代替此错误，我想获取一个MsgBox，然后退出Sub。我尝试使用On Error GoTo方法，该方法在代码与工作表名称与引用单元格不匹配并显示适当的消息时有效。但是，当插入正确的工作表名称时，它会显示该消息，并且不会继续执行代码。

我该如何解决此问题，以便仅在代码找不到工作表名称的情况下得到消息，而如果找到，则完成代码？

这就是我面临的问题

On Error GoTo ErrorExit

'Even when the cell value matches the sheet's name, I still get an error and it exist the sub
Set reportSheet = Sheets(reportSheetName)   

ErrorExit:
MsgBox "Incorrect Sheet Name or It Does Not Exist"
Exit Sub

Dim settingsSheet As Worksheet       'Source
Dim reportSheet As Worksheet        'To convert to PDF
Dim targetColumnsRange As Range     'feeds from source
Dim targetRowsRange As Range
Dim reportSheetName As String       'source sheet with the target's sheet name
Dim reportColumnsAddr As String
Dim reportRowsAddr As String
    ' Set a reference to the settings sheet

Set settingsSheet = ThisWorkbook.Worksheets("Sheet1")   ' source

    ' Gather the report sheet's name

reportSheetName = settingsSheet.Range("C7").Value       ' good

On Error GoTo ErrorExit

'If this doesnt match, display the message and exit sub, else continue the sub
Set reportSheet = Sheets(reportSheetName)   

ErrorExit:
MsgBox "Incorrect Sheet Name or It Does Not Exist"
Exit Sub

Answer 1

您可以这样做:

On Error Resume Next  'ignore errors
Set reportSheet = Sheets(reportSheetName) 
On Error Goto 0       'stop ignoring errors 

If reportSheet is nothing then
     Msgbox "no sheet named '" & reportSheetName & "' in this workbook!"
Else
     'all good
End If