vba 如何检查Excel-VBA中是否存在某些工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6838437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check whether certain sheets exist or not in Excel-VBA?
提问by Vivian
Does anyone know how to check whether certain sheets exist or not in an Excel document using Excel VBA?
有谁知道如何使用 Excel VBA 检查 Excel 文档中是否存在某些工作表？
回答by Tiago Cardoso
Although (unfortunately) such method is not available, we can create our own function to check this..
虽然（不幸的是）这种方法不可用，但我们可以创建自己的函数来检查这个..
Hope the code below fits your needs.
希望下面的代码符合您的需求。
Edit1:Added also delete statement...
编辑1：还添加了删除语句...
Sub test()
If CheckSheet(Sheets(3).Name) then
Application.DisplayAlerts = False
Sheets(Sheets(3).Name).Delete
Application.DisplayAlerts = True
End If
End Sub
The solution I'd go for...
我想要的解决方案......
Function CheckSheet(ByVal sSheetName As String) As Boolean
Dim oSheet As Excel.Worksheet
Dim bReturn As Boolean
For Each oSheet In ActiveWorkbook.Sheets
If oSheet.Name = sSheetName Then
bReturn = True
Exit For
End If
Next oSheet
CheckSheet = bReturn
End Function
Alternatively, if you don't mind to use code that actively raise errors (which is not recommended by common coding best practices) you could use this 'Spartan Programmingwannabe' code below...
或者，如果您不介意使用主动引发错误的代码（常见编码最佳实践不建议这样做），您可以使用下面的“斯巴达编程崇拜者”代码......
Function CheckSheet(ByVal sSheetName As String) As Boolean
Dim oSheet As Excel.Worksheet
Dim bReturn As Boolean
For Each oSheet In ActiveWorkbook.Sheets
If oSheet.Name = sSheetName Then
bReturn = True
Exit For
End If
Next oSheet
CheckSheet = bReturn
End Function
Function CheckSheet(ByVal sSheetName As String) As Boolean
On Error Resume Next
Dim oSheet As Excel.Worksheet
Set oSheet = ActiveWorkbook.Sheets(sSheetName)
CheckSheet = IIf(oSheet Is Nothing, False, True)
End Function
回答by Paul McLain
Something like this will get you started:
像这样的事情会让你开始：
On Error Resume Next
Dim wSheet as Worksheet
Set wSheet = Sheets(1) ' can also be a string, such as Sheets("Sheet1")
If wSheet Is Nothing Then
MsgBox "Worksheet not found!"
Set wSheet = Nothing ' make the worksheet point to nothing.
On Error GoTo 0
Else
MsgBox "Worksheet found!"
Set wSheet = Nothing ' set the found Worksheet object to nothing. You can use the found wSheet for your purposes, though.
End If
This code was based on http://www.ozgrid.com/VBA/IsWorkbookOpen.htm. Look for the DoesSheetExist() sub.
此代码基于http://www.ozgrid.com/VBA/IsWorkbookOpen.htm。寻找DoesSheetExist() 子。
Hope this helps!
希望这可以帮助！
回答by John D
I adapted this code for use in LotusScript, one of the languages used by IBM Notes (formerly Lotus Notes) as shown below.
我修改了这段代码以用于 LotusScript，IBM Notes（以前称为 Lotus Notes）使用的语言之一，如下所示。
Public Function ExcelSheetExists( _
xlBook As Variant, _ ' Excel workbook object
ByVal strSheetName As String _
) As Boolean
On Error GoTo errHandler
ForAll xlSheet In xlBook.Sheets
If xlSheet.Name = strSheetName Then
ExcelSheetExists = True
Exit Forall
End If
End ForAll
GoTo Done
errHandler:
' Call MyCustomErrorHandler()
Resume Done
Done:
End Function
回答by Rens Slenders
On Error GoTo Line1
If Sheets("BOX2").Index > 0 Then
Else
Line1: MsgBox ("BOX2 is missing")
end if
I did it this way:)
我是这样做的：）
