(1条消息) Excel中VBA编程学习笔记（九）
1、获取工作表数量及选中、激活工作表
Private Sub test()
Dim count As Integer
count = Worksheets.count '获取工作表数量
Dim name As String
name = Worksheets(1).name '获取第一个工作表名称
Worksheets(count).Select '选中最后一个工作表
Worksheets(Array("StudentAttendence", "studentsScore")).Select '选中多张工作表,可以同时对这些工作表进行操作
Worksheets("sheet1").Activate '激活表名为sheet1的工作表
End Sub
注：对于激活的工作表可以使用ActiveSheet表示。选中的工作表与激活的工作表不同，选中的工作表可以有多个，但是激活的工作表只有一个。一般点击工作表的标签时候就会激活该工作表。
2、删除批注
【例1】删除工作表中的所有批注
Private Sub test()
Dim tmp As Comment
For Each tmp In ActiveSheet.Comments
tmp.Delete
Next
End Sub
【例2】删除选中区域的所有批注
Sub test2()
For Each rng In Selection
If Not rng.Comment Is Nothing Then
rng.ClearComments
End If
Next
End Sub
3、添加批注及批注属性设置
Range.AddComment添加批注。
If Worksheets(1).Cells(1, 1).Comment Is Nothing Then '判断是否存在批注
Worksheets(1).Cells(1, 1).AddComment '添加批注
Worksheets(1).Cells(1, 1).Comment.Visible = True '设置批注可见
Worksheets(1).Cells(1, 1).Comment.Text Text:="Hello"
Worksheets(1).Cells(1, 1).Comment.Shape.AutoShapeType = 9 '设置为椭圆形
End If
注：批注中的text必须设置为字符串类型，否则不显示批注内容。
4、添加超链接
Hyperlinks.Add语法结构：
Hyperlinks.Add(Anchor,Address,SubAddress,ScreenTip,TextToDisplay)
对Hyperlinks.Add方法的参数说明
Anchor，必选。超链接的位置可为Range或Shape对象；
Address，必选。超链接的地址；
SubAddress，可选。超链接的子地址；
ScreenTip，可选。当鼠标放停留在超链接上时所显示的屏幕提示；
TextToDisplay，可选。要显示的超链接文本。
Private Sub test()
Dim index As Integer
For index = 1 To Worksheets.count
ActiveSheet.Hyperlinks.Add anchor:=Worksheets("sheet1").Cells(index, 1), Address:="", SubAddress:=Worksheets(index).name & _
"!A1", TextToDisplay:=Worksheets(index).name
Next
End Sub
注：上面的自地址选取的是指向各个链接的表的A1单元格。
5、统计超链接数量及删除所有超链接
Worksheets(2).Hyperlinks.Delete
Count = Worksheets(2).Hyperlinks.Count
6、隐藏工作表
Private Sub test()
Dim index As Integer
For index = 2 To Worksheets.count
Worksheets(index).Visible = xlSheetVeryHidden
Next
End Sub
Worksheet.Visible可以取值如下
|
名称 |
值 |
说明 |
|
xlSheetHidden |
0 |
隐藏工作表，用户可以通过菜单取消隐藏 |
|
xlSheetVeryHidden |
2 |
隐藏对象，是对象重新可见的唯一方法是将此属性值设置为true(用户无法使该对象可见) |
|
xlSheetVisible |
-1 |
显示工作表 |
8、创建工作目录表
下例为用各个工作表名称制作目录，并以超链接的形式指向合目标。
Hyperlinks.Add语法结构：
Hyperlinks.Add(Anchor,Address,SubAddress,ScreenTip,TextToDisplay)
对Hyperlinks.Add方法的参数说明
Anchor，必选。超链接的位置可为Range或Shape对象；
Address，必选。超链接的地址；
SubAddress，可选。超链接的子地址；
ScreenTip，可选。当鼠标放停留在超链接上时所显示的屏幕提示；
TextToDisplay，可选。要显示的超链接文本。
Private Sub test()
Dim index As Integer
For index = 1 To Worksheets.count
ActiveSheet.Hyperlinks.Add anchor:=Worksheets("sheet1").Cells(index, 1), Address:="", SubAddress:=Worksheets(index).name & _
"!A1", TextToDisplay:=Worksheets(index).name
Next
End Sub
注：上面的自地址选取的是指向各个链接的表的A1单元格。
9、删除工作表中的空行
Private Sub test()
Dim index As Integer
For index = Worksheets(1).UsedRange.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(index)) = 0 Then
Rows(index).Delete
End If
Next
End Sub
10、限制工作表的滚动区域
Private Sub test()
Worksheets(1).ScrollArea = "A1:F10"
End Sub
注：Excel不会记忆该设置，当再次打开时候该属性值将被重置。
11、删除和插入行列
Rows(2).Insert '在第2行上方插入一行
Columns(1).Insert '在第1列前面插入1列
Rows(1).Delete '删除第1行
Columns(2).Delete '删除第2列
12、复制工作表
语法如下：
WorkSheet.Copy(Before,After)
参数Before及After均是可选，用来确定新工作表放的位置，如果都不指定则新建一个工作薄。不同同时指定Before及After，只能指定其中一个。
Private Sub test()
Worksheets("sheet2").Copy before:=Worksheets("sheet1") '复制后的工作sheet2得到的新工作表被命名为Sheet2 (2)
Worksheets("Sheet2 (2)").Select
Worksheets("Sheet2 (2)").name = "新工作表" '重命名复制后的工作表
End Sub
13、删除空工作表
Private Sub test()
Dim index As Integer
For index = 1 To Worksheets.count
If Worksheets(index).UsedRange.count = 1 And Worksheets(index).Cells(1, 1) = "" Then
Worksheets(index).Delete
End If
Next
End Sub
14、导出为PDF文档
Private Sub test()
Worksheets("StudentAttendence").Activate
Activasheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="example.pdf"
End Sub
参数type可取值为xlTypePDF及xlTypeXPS.
15、在现有工作表之后插入工作表
语法格式：
Worksheets.Add(Before,After,Count,Type)
参数说明：
Count为要插入的工作表数量，Type可以取值有：xlWorksheet，xlChart，xlExcel4MacroSheet，xlExcel4IntMacroSheet。如果同时省略before及after则插入到活动工作表之前。
Private Sub test()
Worksheets.Add after:=Worksheets(Worksheets.count)
End Sub
Sub test()
Sheets.Add.name = "new"
Sheets("new").Delete
Worksheets.Add after:=Worksheets(Sheets.Count), Count:=2 '新增两个工总表
End Sub
16、复制工作表的数据内容
Private Sub test()
Worksheets("StudentAttendence").Select
Worksheets("StudentAttendence").UsedRange.Select
Selection.Copy
Worksheets("sheet1").Paste Destination:=Worksheets("sheet1").Cells(1, 1)
End Sub
17、移动工作表
将sheet3,sheet5插入到sheet1的前面
Worksheets(array(“sheet3”,”sheet5”)).Move before:=Sheets(1)
18、各种事件响应消息
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox ("工作表内容改变了")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ("选中工作表行数：" & Target.Row & xlctrlf & "列号为：" & Target.Column)
End Sub
