excel2007VBA教程

Excel 2007 VBA 教程

第七讲 工作表对象

一. 工作表的引用

1. 以名称方式引用：Worksheets (\工作表名称\，Worksheets是工作表的集合，包括所有工作表。

（1） 实例

Sub test1() '这是用名称来引用工作表名

Worksheets(\总表\佛山小老鼠\End Sub

2. 以索引号方式引用：Worksheets(数字)或者Sheets(数字)，这两者的区别在前面的课程里提过，Sheets

也是工作表的集合，把其它的工作表也包括在内，如图表工作表，宏表工作表。而Worksheets只包括我平常操作的工作表。如Sheet1,Sheet2 （1） 实例

Sub test2() '这是用索引号来引用工作表名

On Error Resume Next '目的是为了防止此工作簿中没有“佛山小老鼠”这个工作表报错 Application.DisplayAlerts = False '屏蔽询问对话框

Worksheets(\佛山小老鼠\删除“佛山小老鼠”这个工作表 Application.DisplayAlerts = True '打开“询问对话框”

Sheets.Add After:=Worksheets(Sheets.Count) '在最后一张工作表后插入一张新工作表 Worksheets(Sheets.Count).Name = \佛山小老鼠\把最后一张工作表改为“佛山小老鼠” End Sub

3. 其它方式的引用

（1） 用ActiveSheet引用

Sub test3() '用ActiveSheet引用

MsgBox \活动工作表的名字是：\End Sub

二. 添加工作表

1. 添加工作表的方法是Add,其表达式Add(Before/After,Count,Type)。Before前面，After后面，Count

插入的数量，Type是插入的类型，如正常工作表Xlworksheet,图表工作表Xlchart，还有宏表工作表这里就不多说了。如果省略第二参，第三参数就会插入一张正常工作表 （1） 实例 在第一张工作表前面插入5张工作表

Sub test4() '在第一张工作表前面插入5张工作表 Sheets.Add Before:=Worksheets(1), Count:=5 End Sub

三. 删除工作表

1. 按名称来删除 Worksheets(工作表名称).Delete 记得给工作表名加双引号

2. 按索引号来删除 Worksheets(数字).Delete 这个数字就是我们在工作簿看到工作表位置。 3. 用代码删除工作表，会弹出一个“询问对话框”如图 17

图 17

4. 解决询问对话框的问题

其实前面许多实例里我都用了，只是没有和大家说详细，这次和大家讲清楚，用了这下面的代码，

第 23 页 共 52 页

Excel 2007 VBA 教程

Application.DisplayAlerts = False '屏蔽询问对话框 Worksheets(1).Delete '删除第一个工作表

Application.DisplayAlerts = True '打开“询问对话框” 5. 至少要保留一张可见工作表 6. 实例 删除第一个工作表

Sub test1() '会弹出询问对话框的 Worksheets(1).Delete End Sub

Sub test2() '不会弹出询问对话框的 Application.DisplayAlerts = False Worksheets(1).Delete

Application.DisplayAlerts = True End Sub

四. 实例 提取各工作表名制作目录

Sub 提取工作表名()

Dim i As Integer '定义i为整型变量

On Error Resume Next '防止工作簿里没有目录工作表，在删除时报错 Application.DisplayAlerts = False '关闭询问对话框 Worksheets(\目录\删除目录工作表

Application.DisplayAlerts = True '打开询问对话框

Sheets.Add before:=Sheets(1) '在第一个工作表前插入一张新的工作表 Sheets(1).Name = \目录\把第一个工作表取名为“目录”

Cells(1, 1) = \目录\在目录工作表中的A1单元写于“目录”两字

For i = 2 To Worksheets.Count 'Worksheets.Count统计工作的总数，整句代码的意思遍历每一个作表

Cells(i, 1) = Sheets(i).Name '依次把工作表的名字写A列 Next i '下一个i End Sub Sub 清空()

Worksheets(\目录\ Worksheets(\目录\ End Sub

五. 实例 提取各工作表名制作目录且还要建立超链接

Sub 提取工作表名()

Dim i As Integer '定义i为整型变量

On Error Resume Next '防止工作簿里没有目录工作表，在删除时报错 Application.DisplayAlerts = False '关闭询问对话框 Worksheets(\目录\删除目录工作表

Application.DisplayAlerts = True '打开询问对话框

Sheets.Add before:=Sheets(1) '在第一个工作表前插入一张新的工作表 Sheets(1).Name = \目录\把第一个工作表取名为\目录\

Cells(1, 1) = \目录\在目录工作表中的A1单元写于\目录\两字

For i = 2 To Worksheets.Count 'Worksheets.Count统计工作的总数，整句代码的意思遍历每一个作表

Cells(i, 1) = Sheets(i).Name '依次把工作表的名字写A列

ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=\

第 24 页 共 52 页

Excel 2007 VBA 教程

SubAddress:=Worksheets(i).Name & \ '由代码过长，选打一个_然后加一个空格，这是把代码分行

' 根据录制的代码修改，把选中的Selection改为Cells(i,1),把 \改为Worksheets(i).Name & \，一定要是记得是工作表名

'所以加了一个Name属性，把“佛山小老鼠”改为Cells(i, 1).Value Next i '下一个i End Sub Sub 清空()

Worksheets(\目录\ Worksheets(\目录\ End Sub

'这是我们在A1单元格输入“佛山小老鼠”之后录制的一个给它添加超链接的宏得到的宏代码 'Sub Macro1() ''

'' Macro1 Macro '' ' ''

' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=\' \佛山小老鼠\'End Sub

六. 实例 提取工作表名制目录且要建立超链接还要添加一个“返回目录”的按钮

Sub 提取工作表名()

Dim i As Integer '定义i为整型变量

On Error Resume Next '防止工作簿里没有目录工作表，在删除时报错 Application.DisplayAlerts = False '关闭询问对话框 Worksheets(\目录\删除目录工作表

Application.DisplayAlerts = True '打开询问对话框

Sheets.Add before:=Sheets(1) '在第一个工作表前插入一张新的工作表 Sheets(1).Name = \目录\把第一个工作表取名为\目录\

Cells(1, 1) = \目录\在目录工作表中的A1单元写于\目录\两字

For i = 2 To Worksheets.Count 'Worksheets.Count统计工作的总数，整句代码的意思遍历每一个作表

Worksheets(1).Activate '一定要加上这一句，由于我们为了添加按钮时，把添加按钮的那个工作表设置为活动工作表，添加之后还要把“目录”工作表设置为当前工作表 Cells(i, 1) = Sheets(i).Name '依次把工作表的名字写A列

ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=\

SubAddress:=Worksheets(i).Name & \ '由代码过长，先打一个_然后加一个空格，这是把代码分行

' 根据录制的代码修改，把选中的Selection改为Cells(i,1),把 \改为Worksheets(i).Name & \，一定要是记得是工作表名

'所以加了一个Name属性，把“佛山小老鼠”改为Cells(i, 1).Value ' 添加“返回目录”按钮

'因为2007录制不了艺术字代码，2003和2010可以，下面的代码是我在2003版里录制修改后的 Worksheets(i).Shapes.AddTextEffect(msoTextEffect32, \返回目录\黑体\ msoTrue, msoFalse, 600#, 20.25).Select

第 25 页 共 52 页

Excel 2007 VBA 教程

Sheets(i).Select Sheets(i).Activate

ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:= _ \ Range(\ Next i '下一个i End Sub Sub 清空()

Dim Sp As Shape, sh As Worksheet

Worksheets(\目录\ For Each sh In Worksheets sh.Activate

For Each Sp In ActiveSheet.Shapes Sp.Delete Next Sp Next sh End Sub

第八讲 工作簿对象

一. 工作簿的引用方式

1. 以名称引用

(1). 实例

Sub test2() ' 把“汇总”工作簿打开

MsgBox Workbooks(\汇总.xlsm\End Sub

2. 以索号引用

(1). 实例

Sub test1()

MsgBox Workbooks(1).Name End Sub

3. Thiswokbook 表示代码所在的的工作簿

(1). 实例

Sub test3()

MsgBox ThisWorkbook.Name End Sub (2). 11

4. Activeworkbook 表示当前活动工作簿

(1). 实例

Sub test4()

MsgBox ActiveWorkbook.Name End Sub

二. 保存工作簿

1. 另存为的方法

(1). 实例 在桌面上新建一个工作簿，取名为“汇总”，且工作簿密码是1234

Sub test1()

第 26 页 共 52 页

Excel 2007 VBA 教程

Dim wb As Workbook '定义wb工作簿型对象变量 Set wb = Workbooks.Add '把新建的工作簿赋给wb With wb '处理工作簿wb

.SaveAs Filename:=\我的文档\\桌面\\汇总.xlsx\另存为桌面，取名为“汇总” .Password = \给工作簿 wb设置密码为1234 End With '结束处理 End Sub

三. 打开工作簿

1. 用Open方法，它的参数有许多，我现在只用了第一个参数：要打开的工作簿所在的路径，其它参数没有，

可以自己去看帮助。 (1). 实例 打开“F第八讲VBA对象”文件中的“汇总”工作簿

Sub test1()

Workbooks.Open Filename:=ThisWorkbook.Path & \汇总.xlsm\End Sub

四. 从不打开的工作簿里提取数据

1. 实例

Sub Test1()

Dim Wb As Workbook '定义Wb为工作簿对象型变量 Dim MyPth As String '定义MyPth为文本型变量

Application.ScreenUpdating = False '关闭屏幕刷新

MyPth = ThisWorkbook.Path & \数据源.xlsx\把数据源工作簿路径赋给MyPth Set Wb = GetObject(MyPth) '把返回路径上的文件引用且赋值给Wb

With Wb.Sheets(1).Range(\工作簿里工作表1和A1单元格相连的区域

Range(\把Wb工作簿里的工作表1数据写于活动工作表里以A1单元格为区域

'Rows.Count是2^20行,Columns.Count2^14列,它们的对象是 Wb.Sheets(1).Range(\，也就是起到复制整个工作表的作用 Wb.Close False '关才Wb工作簿，且不保存更改 End With '

Set Wb = Nothing '释放内存

Application.ScreenUpdating = True '打开屏幕刷新 End Sub

第九讲 事件

一. 事件分类

(1). 应用程序事件 经常在类模块中定义这种事件 (2). 工作簿事件 如工作簿的Open事件，一打开工作簿就就执行过程

Private Sub Workbook_Open() …… End Sub

实例 一开工作作簿就问好 Private Sub Workbook_Open() MsgBox \佛山小老鼠你好！\End Sub (3). 工作表事件 如工作表里的Change事件，一改变工作表单元里的内容就触发，工作表事件用的用比

第 27 页 共 52 页