|
判断EXCEL 是否存在某工作表？
VBA插入工作表前判断是否存在
在EXCEL2010的VBA代码窗口中，新建模块1并输入如下代码。
Sub插入前判断()
Dim x As Integer ‘设置循环变量
For x=1 To Sheets.Count ‘设置变量在“1”到“工作表数量”之间循环
If Sheets(x).Name = ”Sheet4” Then ‘判断工作表的名称是否为“Sheet4”
MsgBox”已存在Sheet4工作表”
‘当“Sheet4”工作表存在时，运行该程序会弹出显示“已存在Sheet4工作表”
Exit Sub ‘如果存在“Sheet4”工作表，则退出该过程
End if
Next X
Sheets.Add ‘如果不存在则插入工作表
Activesheet.Name = ” Sheet4”
‘ActiveSheet是指当前活动工作表，所以对当前活动工作表命名即可
End Sub 运行该VBA代码，当“Sheet4”工作表存在时，该VBA程序会出显示“Sheet4”的信息提示框。在插入指定名称的工作表时，如果不进行判断（即取消上述代码中的For循环），则在插入名称相同的EXCEL2010工作表时也会弹出错误提示信息。 方案一： Sub pd()
On Error Resume Next
If Sheets("张三") Is Nothing Then
MsgBox "工作表不存在"
Else
MsgBox "工作表存在"
End If
End Sub 方案二： Public Sub a()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name = "Sheet1" Then
MsgBox "存在"
Exit Sub
End If
Next
MsgBox "不存在"
End Sub 方案三： Public Sub a()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name = "Sheet1" Then
j = j + 1
End If
Next
If j = 1 Then
MsgBox "存在"
Else: MsgBox "不存在"
End If
End Sub
方案四：
Sub Lkup()
Dim sh As Worksheet, k%
k = 0
For Each sh In Sheets
If sh.Name = "AAA" Then MsgBox "此表存在!": k = k + 1: GoTo 1
Next
1
If k = 1 Then
……
End If
End Sub
=============================================================================================
excel－vba应用示例之判断一个工作表(名)是否存在 判断一个工作表(名)是否存在
[示例01]
Sub testWorksheetExists1()
Dim ws As Worksheet
If Not WorksheetExists(ThisWorkbook, "sheet1") Then
MsgBox "不能够找到该工作表", vbOKOnly
Exit Sub
End If
MsgBox "已经找到工作表"
Set ws = ThisWorkbook.Worksheets("sheet1")
End Sub
'- - - - - - - - - - - - - - - - - - -
Function WorksheetExists(wb As Workbook, sName As String) As Boolean
Dim s As String
On Error GoTo ErrHandle
s = wb.Worksheets(sName).Name
WorksheetExists = True
Exit Function
ErrHandle:
WorksheetExists = False
End Function
示例说明：在测试代码中，用相应的工作簿名和工作表名分别代替“ThisWorkbook”和“Sheet1”，来判断指定工作表是否在工作簿中存在。 -------------------------------------------------------------------------------- [示例02]
Sub testWorksheetExists2()
If Not SheetExists("<工作表名>") Then
MsgBox "<工作表名> 不存在!"
Else
Sheets("<工作表名>").Activate
End If
End Sub
'- - - - - - - - - - - - - - - - - - -
Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function
示例说明：在代码中，用实际工作表名代替<>。 -------------------------------------------------------------------------------- [示例03]
Sub TestingFunction()
'如果工作表存在则返回True，否则为False
'测试DoesWksExist1函数
Debug.Print DoesWksExist1("Sheet1")
Debug.Print DoesWksExist1("Sheet100")
Debug.Print "-----"
'测试DoesWks5Exist2函数
Debug.Print DoesWksExist2("Sheet1")
Debug.Print DoesWksExist2("Sheet100")
End Sub
‘- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function DoesWksExist1(sWksName As String) As Boolean
Dim i As Long
For i = Worksheets.Count To 1 Step -1
If Sheets(i).Name = sWksName Then
Exit For
End If
Next
If i = 0 Then
DoesWksExist1 = False
Else
DoesWksExist1 = True
End If
End Function
‘- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function DoesWksExist2(sWksName As String) As Boolean
Dim wkb As Worksheet
On Error Resume Next
Set wkb = Sheets(sWksName)
On Error GoTo 0
DoesWksExist2 = IIf(Not wkb Is Nothing, True, False)
End Function
|
