我一直找不到一种快速的方法来检查你传递的Variant参数是否包含字符串或工作簿，因为如果你检查它是否是字符串，它会抛出一个错误（但话又说回来，如果你只使用一个参数，也许使用On Error GoTo会起作用），所以我把它放在一起：

Function isWorkbookWorksheetExisting(sheetName As String, Optional targetWorkbook As Workbook, Optional targetWorkbookStr As String) As Boolean
    Dim sh As Worksheet
    Dim wb As Workbook
    
    If targetWorkbook Is Nothing And Len(targetWorkbookStr) = 0 Then
        Set wb = ActiveWorkbook
    ElseIf targetWorkbook Is Nothing Then
        Set wb = Workbooks(targetWorkbookStr) 'this does assume the workbook is open
    Else
        Set wb = targetWorkbook
    End If
    For Each sh In wb.Worksheets
        If sheetName = sh.Name Then
            isWorkbookWorksheetExisting = True
            Exit For
        End If
    Next sh
    
End Function

在配方中的用途：如果要提供工作簿名称
isWorkbookWorksheetExisting（“Sheet1”，，“Workbook1”）
如果你不想提供
isWorkbookWorksheetExisting（“Sheet1”）
在VBA中
isWorkbookWorksheetExisting（“Sheet1”，wbVariable）

我建议将Workbook参数设置为Variant，并添加一些逻辑来可选地处理Workbook对象或Workbook名称为String。
我为此目的使用的函数看起来像这样：

Public Function WorksheetExists(ByVal wsName As String, _
                       Optional ByVal xlWb As Variant = Nothing) As Boolean
    WorksheetExists = False
    
    If VarType(xlWb) = vbString Then
        Set xlWb = Application.Workbooks(xlWb)
    ElseIf VarType(xlWb) <> vbObject Or TypeName(xlWb) <> "Workbook" Then
        If xlWb Is Nothing Then
            Set xlWb = ThisWorkbook
        Else
            Err.Raise 5, "WorksheetExists", _
                "Parameter 'xlWb' must be of type 'Workbook' or 'String'."
        End If
    End If
    
    Dim Worksheet As Excel.Worksheet
    For Each Worksheet In xlWb.Worksheets
        If Worksheet.Name = wsName Then
            WorksheetExists = True
            Exit Function
        End If
    Next Worksheet
End Function

如果代码驻留在您正在使用此函数的工作簿中，则可以像这样调用它：

=WorksheetExists("Sheet1")

如果你想在另一个工作簿中检查工作表的存在，可以这样调用它：

=WorksheetExists("Sheet1", "Book1.xlsx")