如何判断工作簿中是否存在工作表 a？

Question

我有以下代码未按预期工作：

If Sheets("a") <> "" Then MsgBox ("sheet a exists")

我如何知道工作簿中是否存在工作表 a？

Answer 1

我会为它创建一个单独的功能：

Function SheetExists(SheetName As String)
    On Error GoTo no:
    WorksheetName = Worksheets(SheetName).Name
    SheetExists = True
    Exit Function
no:
    SheetExists = False
End Function

然后，您可以在需要的地方轻松调用它，甚至可以在公式中调用它：

Sub ABC()
    If SheetExists("Test") Then
        MsgBox "Yay!"
    Else
        MsgBox "Boo!"
    End If
End Sub

或者

=If(SheetExists("Test"),"Yay!","Boo")

Answer 2

就像是：

Sub DoesSheetExist()
    Dim s As Worksheet

    For Each s In Sheets
        If s.Name = "a" Then
            MsgBox "Sheet a exists"
            Exit Sub
        End If
    Next s
    MsgBox "Sheet a does not exist"
End Sub

Answer 3

您可以检查错误。例如：

Dim A As String
    On Error Resume Next
        A = Worksheets("a").Name
        Select Case Err.Number
            Case 9
                MsgBox "Sheet ""a"" does not exist"
            Case 0
                MsgBox "Sheet ""a"" exists"
            Case Else
                Stop
        End Select
    On Error GoTo 0

Answer 4

If (Worksheets("a").Name <> "") Then MsgBox ("sheet A exists")

这按预期工作