测试或检查是否存在纸张 [英] Test or check if sheet exists

问题描述

  Dim wkbkdestination As Workbook 
 Dim目录作为工作表
 
对于每个ThisWorkSheet在wkbkorigin.Worksheets 
'中，这会导致下标超出范围如果目标
'工作簿中没有与原始工作簿中当前工作表名称相同的工作表。 
设置目录= wkbkdestination.Worksheets（ThisWorkSheet.Name）
下一个
  

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

如何检查该表单是否存在？如下所示：

 如果wkbkdestination.Worksheets（ThisWorkSheet.Name）然后
  pre> 
 
解决方案
一些民众不喜欢这种方法，因为不当使用错误处理，但我认为在VBA中可以接受...另一种方法是循环遍历所有的工作表，直到找到匹配。
 功能SheetExists（shtName As String，可选的wb As Workbook）As Boolean 
 Dim sht As Worksheet 
 
如果wb是Nothing然后设置wb = ThisWorkbook 
 On Error Resume Next 
 Set sht = wb.Sheets （shtName）
错误GoTo 0 
 SheetExists =不sht是没有
结束函数
  
 
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 destsheet in the destination workbook to the sheet with the same name as the currently iterated one in the origin workbook.

How can I test if that sheet exists? Something like:
If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then 

解决方案
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.
 Function SheetExists(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
     SheetExists = Not sht Is Nothing
 End Function


                        
这篇关于测试或检查是否存在纸张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！