vba 测试或检查工作表是否存在

Test or check if sheet exists

提问by yse

Dim wkbkdestination As Workbook
Dim destsheet As Worksheet

For Each ThisWorkSheet In wkbkorigin.Worksheets 
    'this throws subscript out of range if there is not a sheet in the destination 
    'workbook that has the same name as the current sheet in the origin workbook.
    Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name) 
Next

Basically I loop through all sheets in the origin workbook then set destsheetin the destination workbook to the sheet with the same name as the currently iterated one in the origin workbook.

基本上，我遍历原始工作簿中的所有工作表，然后将destsheet目标工作簿中的工作表设置为与原始工作簿中当前迭代的工作表同名的工作表。

How can I test if that sheet exists? Something like:

如何测试该工作表是否存在？就像是：

If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then 

回答by Tim Williams

Some folk dislike this approach because of an "inappropriate" use of error handling, but I think it's considered acceptable in VBA... An alternative approach is to loop though all the sheets until you find a match.

由于错误处理的“不恰当”使用，有些人不喜欢这种方法，但我认为它在 VBA 中被认为是可以接受的......另一种方法是循环遍历所有工作表，直到找到匹配项。

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

回答by Rory

If you are specifically interested in worksheets only, you can use a simple Evaluate call:

如果您只对工作表特别感兴趣，则可以使用简单的 Evaluate 调用：

Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function

回答by fbonetti

You don't need error handling in order to accomplish this. All you have to do is iterate over all of the Worksheets and check if the specified name exists:

您不需要错误处理来完成此操作。您所要做的就是遍历所有工作表并检查指定的名称是否存在：

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "MySheet" Then
        exists = True
    End If
Next i

If Not exists Then
    Worksheets.Add.Name = "MySheet"
End If

回答by Peter Albert

As checking for members of a collection is a general problem, here is an abstracted version of Tim's answer:

由于检查集合的成员是一个普遍问题，以下是 Tim 答案的抽象版本：

Function Contains(objCollection As Object, strName as String) As Boolean
    Dim o as Object
    On Error Resume Next
    set o = objCollection(strName)
    Contains = (Err.Number = 0)
    Err.Clear
 End Function

This function can be used with any collection like object (Shapes, Range, Names, Workbooks, etc.).

此功能可与像对象的任何集合中使用（Shapes，Range，Names，Workbooks，等等）。

To check for the existence of a sheet, use If Contains(Sheets, "SheetName") ...

要检查工作表是否存在，请使用 If Contains(Sheets, "SheetName") ...

回答by Shai Alon

Corrected:Without error-handling:

更正：没有错误处理：

Function CheckIfSheetExists(SheetName As String) As Boolean
      CheckIfSheetExists = False
      For Each WS In Worksheets
        If SheetName = WS.name Then
          CheckIfSheetExists = True
          Exit Function
        End If
      Next WS
End Function

回答by VirtualMichael

In case anyone wants to avoid VBA and test if a worksheet exists purely within a cell formula, it is possible using the ISREFand INDIRECTfunctions:

如果有人想避免使用 VBA 并测试工作表是否仅存在于单元格公式中，则可以使用ISREF和INDIRECT函数：

=ISREF(INDIRECT("SheetName!A1"))

=ISREF(INDIRECT("SheetName!A1"))

This will return TRUEif the workbook contains a sheet called SheetNameand FALSEotherwise.

TRUE如果工作簿包含调用的工作表，这将返回SheetName，FALSE否则返回。

回答by AOBR

I wrote this one:

我写了这个：

Function sheetExist(sSheet As String) As Boolean
On Error Resume Next
sheetExist = (ActiveWorkbook.Sheets(sSheet).Index > 0)
End Function

回答by uildriks

My solution looks much like Tims but also works in case of non-worksheet sheets - charts

我的解决方案看起来很像 Tims，但也适用于非工作表 - 图表

Public Function SheetExists(strSheetName As String, Optional wbWorkbook As Workbook) As Boolean
    If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook 'or ThisWorkbook - whichever appropriate
    Dim obj As Object
    On Error GoTo HandleError
    Set obj = wbWorkbook.Sheets(strSheetName)
    SheetExists = True
    Exit Function
HandleError:
    SheetExists = False
End Function

.

.

回答by Martin Carlsson

Put the test in a function and you will be able to reuse it and you have better code readability.

将测试放在一个函数中，您将能够重用它，并且您具有更好的代码可读性。

Do NOT use the "On Error Resume Next" since it may conflict with other part of your code.

不要使用“On Error Resume Next”，因为它可能与代码的其他部分冲突。

Sub DoesTheSheetExists()
    If SheetExist("SheetName") Then
        Debug.Print "The Sheet Exists"
    Else
        Debug.Print "The Sheet Does NOT Exists"
    End If
End Sub

Function SheetExist(strSheetName As String) As Boolean
    Dim i As Integer

    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = strSheetName Then
            SheetExist = True
            Exit Function
        End If
    Next i
End Function

回答by ashleedawg

Compact wsExistsfunction _{^{(withoutreliance on Error Handling!)}}

紧凑的wsExists功能_{^{（不依赖错误处理！）}}

Here's a short & simple function that doesn't rely on error handling to determine whether a worksheet exists (andis properly declared to work in anysituation!)

这是一个简短而简单的函数，它不依赖于错误处理来确定工作表是否存在（并且被正确声明为在任何情况下都可以工作！）

Function wsExists(wsName As String) As Boolean
    Dim ws: For Each ws In Sheets
    wsExists = (wsName = ws.Name): If wsExists Then Exit Function
    Next ws
End Function

---

---

Example Usage:

示例用法：

The following example adds a new worksheet named myNewSheet, if it doesn't already exist:

以下示例添加一个名为 的新工作表myNewSheet（如果它尚不存在）：

If Not wsExists("myNewSheet") Then Sheets.Add.Name = "myNewSheet"

---

---

More Information:

更多信息：

• MSDN : For Each…NextStatement(VBA)
• MSDN : ExitStatement(VBA)
• MSDN : Comparison Operators(VBA)

• MSDN : For Each...Next声明(VBA)
• MSDN：Exit声明（VBA）
• MSDN：比较运算符（VBA）