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

Question

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

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

如何测试该工作表是否存在？像这样的东西:

If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then 

Answer 1

有些人不喜欢这种方法，因为错误处理的“不恰当”使用，但我认为它在 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