VBA之WrokSheet工作表对象(精品)

WorkSheet工作表对象(一)

如何引用工作表？

通过Sheets(index)或Worksheets(index)可返回一个WorkSheet对象（工作簿中如果包含图表工作表，那Sheets(index)有可能返回的是图表对象）。

Index是工作表索引号或者工作表名称。

索引号表示该工作表在工作簿的标签栏上的位置。所有工作表均包含在索引计数中，即便是隐藏的工作表也是如此。 Worksheets(1)是工作表中第一个（最左边）工作表，而Worksheets（）则是最后一个。

ation 属性

如果不使用对象识别符，则该属性返回一个 Application 对象，该对象表示 Microsoft

Excel 应用程序。

如果使用对象识别符，则该属性返回一个表示指定对象（可对一个 OLE 自动操作对象使用本属性来返回该对象的应用程序）创建者的 Application 对象。

这个属性在之前的工作簿，工作簿集合，工作表集合都包含有，以后再介绍Application对象。

属性

返回一个 Range 对象，它代表工作表中的所有单元格（不仅仅是当前使用的单元格）。

因为 Item 属性是 Range 对象的默认属性（默认属性：可以为控件设置的属性，以使每次创建该类型的新控件时，这个属性的值都相同），所以可以在 Cells 关键字后面紧接着指定行和列索引。

示例：

(5, 3).Value = "VBA零基础入门"

'修改C5单元格的值

'5代表行号，3是列号

arReference 属性

返回一个 Range 对象，该对象表示工作表上包含第一个循环引用的区域，或返回

Nothing（如果工作表上没有循环引用）。在继续执行计算之前，必须删除循环引用。

示例：

Dim rg As Range

Set rg = arReference

If Not rg Is Nothing Then

MsgBox "当前工作表中第一个循环引用的单元格地址为：" & s

End If

me 属性

返回对象的代码名，String类型，只读。

可以在设计过程中通过更改该值来改变对象的代码名。不能在运行过程中更改该属性。

对于一个返回指定对象的表达式，该表达式可使用对象的代码名。

例如，如果第一张工作表的代码名为 Sheet1，则下列表达式是等价的。

Worksheets(1).Range("a1")

("a1")

工作表的名称可以与其代码名不同。

创建一张工作表时，其工作表名称和代码名是相同的，

不过，更改工作表名称时并不影响其代码名，并且，更改工作表代码名（在 Visual Basic

编辑器中使用“属性”窗口）也不影响其名称。

工程资源管理器文件夹

工程资源管理器普通列表

在列表中，工作表对象的名称显示为代码名（工作表名称）。可参考上图中的显示来理解。

在工作表属性窗口中，Name是工作表名称，(名称)属性就是代码名称。

在“属性”窗口中“（名称）”右边的单元格中显示的值是所选对象的代码名。

补充资料：

Is Nothing用法

这时的IS是比较运算符，一个对象变量如果用一个正确对象的引用来赋值的话，其值就为Nothing。

通常要对一个对象变量操作前，可以进行判断其值是否有一个对象引用。

如果其值为Nothing，如果操作该对象引用的话，就会产生一个运行错误：对象变更或With块变量未设置。

另外，当对象引用完毕后，可以执行Set 对象变量=Nothing来释放被引用的对象有关联的内存资源及系统资源。

WorkSheet工作表对象(二)

s 属性

返回一个 Range 对象，它代表指定工作表中的所有列。

如果指定的非工作表，则 Columns 属性失效。

在不使用对象识别符的情况下使用此属性等效于使用 s。

此属性在应用于一个是多重选定区域的 Range 对象时，会只从该区域的第一个子区域中返回列。

例如，如果 Range 对象有两个子区域 A1:B2 和 C3:D4，那么，

的返回值是 2，而不是 4。

若要对一个可能包含多重选定区域的区域使用此属性，请测试 以确定此区域内是否包含多个子区域。

如果包含，请对此区域内的每个子区域进行循环。

示例：

Worksheets("Sheet1").Columns(1). = True

'sheet1工作表的第一列字体加粗

Dim rg As Range

If TypeName(Selection) = "Range" Then

'检测当前所选择的是否单元格对象

s

'在立即窗口输出所选单元格的地址

For Each rg In

'区域中循环（这样避免多选情况下有漏网之鱼）

With rg

. = True

'斜体

.ndex = 3

'字体颜色

End With

s

Next

End If

ts 属性

返回一个 Comments 集合，该集合表示指定工作表的所有注释，只读。

示例：

Dim ct As Comment

If Then

For Each ct In ts

'在立即窗口中显示批注内容

'可以用代码操作，添加批注

Next

End If

Properties 属性

返回一个 CustomProperties 对象，该对象表示与工作表相关的标识符信息。

对于 CustomProperties 属性，工作表的标识符信息可以表示 XML 的元数据。

示例：

With Properties

.Add Name:="hwc2ycy", Value:="VBA入门"

With .Item(1)

MsgBox .Name & vbTab & .Value

End With

End With

'向活动工作表添加标识符信息

用这招可以偷偷在工作表添加些不易被人发现的附加信息:-)

yPageBreaks 属性

如果显示指定工作表中的分页符（包括自动和手动分页符），则该属性值为 True，Boolean 类型，可读写。

如果没有安装打印机，不能设置该属性。

示例：

Worksheets("Sheet1").DisplayPageBreaks = True

此项对应的设置在Excel选项，高级，此工作表的显示选项，显示分页符。

yRightToLeft 属性

如果指定工作表是从右到左显示（而非从左到右），则为 True。

如果对象从左到右显示，则为 False。

此项对应的设置在Excel选项，高级，此工作表的显示选项，从右到左显示工作表，可

参考上图。

Mode 属性

如果工作表处于筛选模式，则为 True。只读 Boolean 类型。

示例：

MsgBox & "处于筛选状态：" & Choose(CInt(Mode) +

2, "是", "否")

注意，这个属性和AutoFilter还是有区别的，区别在哪，大家可以细细观察。

WorkSheet工作表对象(三)

属性

返回 Long 值，它代表对象在其同类对象所组成的集合内的索引号。

示例：

MsgBox

jects 属性

返回工作表中 ListObject对象的集合。ListObjects 集合，只读。

在平常操作中，选中数据区按CTRL+T即可创建表格。

示例：

Dim lst As ListObject

Then

For Each lst jects

, s

Next

End If

检测当前活动工作表中是否有表格对象，有的话在立即窗口中输出表名和所对应的区域

属性

返回或设置一个String 值，它代表对象的名称。

注意，当给指定的工作表改名时，不要犯了重名的错误了。

命名重复会产生代码为1004的运行错误。

示例：

Dim i As Byte

For i = 1

Worksheets(i).Name ="A" & i

Next

属性

返回一个 Names 集合，它代表所有特定于工作表的名称（使用“WorksheetName!”前缀定义的名称）。

Names 对象，只读。

在不使用对象识别符的情况下使用此属性等效于使用 。

示例：

Name:="test",RefersToR1C1:="=" & &

"!R2C3"

Name:="test2",RefersToR1C1:=Range("c4")

在当前工作表内定义两个名称test,test2。

称

属性

返回代表下一个工作表的Worksheet 对象。

示例：

If Not IsNothing Then

MsgBox

End If

如果当前活动工作表非最后一个，则会通过消息框显示位于当前工作表之后的工作表名us 属性

返回代表下一个工作表的Worksheet 对象。

和Next相反，返回位于当前工作表前面的工作表对象。

示例：

If Not ActiveSheet. PreviousIs Nothing Then

MsgBox ActiveSheet. Previous

End If

换成用INDEX属性也能实现这个效果。

tup 属性

返回一个PageSetup 对象，它包含用于指定对象的所有页面设置。

PageSetup对象可通过录制宏来熟悉。

属性

返回指定工作表的父对象（即工作簿）。

示例：

MsgBox me

Area 属性

以 A1 样式的区域引用形式返回或设置允许滚动的区域。

用户不能选定滚动区域之外的单元格,String 类型，可读写。

可将该属性设置为空字符串 ("") 以允许对整张工作表内所有单元格的选定。

示例：

Worksheets(1).ScrollArea ="A10:S20"

设置允许滚动区域后，虽然不能用鼠标选之滚动区域之外的单元格，但是可以从地址栏直接跳转。

rdHeight 属性

以磅（磅：指打印的字符的高度的度量单位。1 磅等于 1/72 英寸，或大约等于 1 厘米的 1/28。）为单位返回工作表中所有行的标准高度（默认值）。Double 类型，只读。

rdWidth 属性

返回或设置工作表中所有列的标准列宽（默认值），Double 类型，可读写。

注意标准行高是只读的，标准列宽既能读又能写。

属性

返回一个代表工作表类型的 XlSheetType 值。

XlSheetType的值如下：

xlChart -4109 图表

xlDialogSheet -4116 对话框工作表

xlExcel4IntlMacroSheet 4 Excel 版本 4 国际宏工作表

xlExcel4MacroSheet 3 Excel 版本 4 宏工作表

xlWorksheet -4167 工作表

注意，当特定对象是worksheet对象时，返回的类型只可能有-4167。

当从sheets集合中引用对象时，返回的类型可以为3，4，-4167三种。

WorkSheet工作表对象(四)

e 属性

返回或设置一个 XlSheetVisibility 值，它确定对象是否可见。

XlSheetVisibility可为下列值:

xlSheetHidden 0 隐藏工作表，用户可以通过菜单取消隐藏。

xlSheetVeryHidden 2 隐藏对象，以便使对象重新可见的唯一方法是将此属性设置为

True（用户无法使该对象可见）。

xlSheetVisible -1 显示工作表。

对于一般用户，设置值为xlSheetVeryHidden可以直到深度隐藏的效果。

另外在批量隐藏时，需要注意的是必须保留一个可见的工作表。

属性

返回一个 Range 对象，它代表指定工作表中的所有行。Range 对象，只读。

在不使用对象识别符的情况下使用此属性等效于使用 。

该属性在应用于是多个选定区域的 Range 对象时，只从该区域中第一个子区域内返回行。

例如，如果 Range 对象有两个子区域：A1:B2 和 C3:D4，则 返回 2 而不是 4。

若要在一个可能包含多个选定区域的区域中使用此属性，请测试 以确定该区域是否包含多个选择区域。

示例:

MsgBox

返回当前活动工作表内的行数

Dim rg As Range

Dim lRow As Long

If TypeName(Selection) = "Range" Then

For Each rg In Selection

lRow = lRow +

Next

MsgBox "当前所选区域 " & s & vbCrLf & "一共有 " & lRow & "

行"

End If

统计当前所选区域内一共有多少行

s 属性

返回一个 Range 对象，它代表活动工作表中的所有列。

如果活动文档不是工作表，则 Columns 属性失效。

在不使用对象识别符的情况下使用此属性等效于使用 s。

此属性在应用于一个是多重选定区域的 Range 对象时，会只从该区域的第一个子区域中返回列。

例如，如果 Range 对象有两个子区域 A1:B2 和 C3:D4，那么，

的返回值是 2，而不是 4。

若要对一个可能包含多重选定区域的区域使用此属性，请测试 以确定此区域内是否包含多个子区域。

如果包含，请对此区域内的每个子区域进行循环。

实际使用时，直接用For each遍历areas比较好，这个方法已经同时兼顾到了只选一个区域和多个区域的情况，不需要先判断再来根据情况执行不同的代码了。

示例：

With Worksheets("Sheet1").Columns(1)

. = True

.AutoFit

End With

设置工作表Sheet1的第一列字体加粗，列宽自动调整。

属性

返回一个 Range 对象，它代表一个单元格或单元格区域。

语法：Range(Cell1, Cell2)

参数：

Cell1 必选 Variant 区域名称。必须为采用宏语言的 A1 样式引用。

可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。

也可包括货币符号，但它们会被忽略掉。您可以在区域中任一部分使用局部定义名称。

如果使用名称，则假定该名称使用的是宏语言。

Cell2 可选 Variant 区域左上角和右下角的单元格。可以是一个包含单个单元格、整列或整行的 Range 对象，或者也可以是一个用宏语言为单个单元格命名的字符串。

如果在没有对象识别符时使用，则该属性是 的快捷方式（它返回活动表的一个区域，如果活动表不是一张工作表，则该属性无效）。

当应用于 Range 对象时，该属性与 Range 对象相关。

例如，如果选中单元格 C3，那么 ("B1") 返回单元格 D3，因为它同

Selection 属性返回的 Range 对象相关。

此外，代码 ("B1") 总是返回单元格 B1。

示例：

Worksheets("Sheet1").Range("A1").Value = 3.14159

Sheet1 上 A1 单元格的值设置为 3.14159。

Dim c As Range

For Each c In Worksheets("Sheet1").Range("A1:D10")

If < 0.001 Then

= 0

End If

Next c

在Sheet1 上的单元格区域 A1:D10 中进行循环。如果某个单元格的值小于 0.001，则此代码将用 0（零）来取代该值

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). = True

Sheet1 中单元格区域 A1:C5 上的字体样式设置为斜体

Range("test").Value = "test"

在名称为test的单元格区域上修改值为test

nge 属性

返回一个 Range 对象，该对象表示指定工作表上所使用的区域，只读。

示例：

MsgBox "工作表使上所使用的区域：" & s

属性

返回一个 Shapes 集合，它代表工作表上的所有形状。只读。

示例：

With e(10, 10, 250, 250).Line

.DashStyle = msoLineDashDotDot

. = RGB(50, 0, 128)

End With

在当前活动工作表中画一条蓝色虚线（左上角为10，10，右下角为250，250）

reaks 属性

返回一个 VPageBreaks 集合，它代表工作表上的垂直分页符，只读

reaks 属性

返回一个 HPageBreaks 集合，它代表工作表上的水平分页符。只读。

WorkSheet工作表对象(五)

tContents 属性

表示指定工作表的内容是否受保护，Boolean 类型，只读。

要打开内容保护，请使用 Protect 方法，并将 Contents 参数设置为 True。

当对受保护的工作表进行写入时操作时，会产生一个1004的运行错误。

示例：

If tContents Then

MsgBox "当前工作表的内容处于保护状态，无法写入，请先解除保护"

Else

Range("a1").Value = 1

End If

这里有个前提，就是单元格的保护选项中锁定需要勾选。

如果锁定没有勾选，还是可以直接写入的。

为了避免不必要的错误，在写入单元格前先检测当前工作表的内容是否受保护。

如果在保护状态，则提示解决保护，否则直接写入单元格。

tDrawingObjects 属性

表示特定工作表的形状是否受保护，Boolean 类型，只读。

要打开形状保护，请使用 Protect 方法，并将 DrawingObjects 参数设置为 True。

当工作表内的形状牌保护时，执行删除方法会产生代码为-214702480的错误，提示内容为“指定的值超出了范围”。

当碰到这个错误提示时就要注意当前工作表的保护了。

解决方法：一是解除保护，二是保护时，编辑对象要勾选（DrawingObjects：=false）。

示例：

If tDrawingObjects Then

MsgBox "当前工作表的形状内容处于保护状态，无法删除，请先解除保护"

Else

For Each shp In

Next

MsgBox "当前工作表内的形状删除完成"

End If

tion 属性

返回一个 Protection 对象，该对象表示工作表的保护选项。

Protection对象的属性如下：

AllowDeletingColumns 是否允许删除受保护工作表上的列，只读。

AllowDeletingRows 是否允许删除受保护工作表上的行，，只读。

AllowEditRanges 返回一个 AllowEditRanges 对象。

AllowFiltering 是否允许用户使用工作表受保护之前设置的“自动筛选”，只读。

AllowFormattingCells 是否允许对受保护的工作表上的单元格进行格式设置，只读。

AllowFormattingColumns 是否允许对受保护的工作表上的列进行格式设置，只读。

AllowFormattingRows 是否允许对受保护的工作表上的行进行格式设置，只读。

AllowInsertingColumns 是否允许在受保护的工作表上插入列，只读。

AllowInsertingHyperlinks是否允许在受保护的工作表上插入超链接，只读。

AllowInsertingRows 是否允许用户在受保护的工作表上插入行，只读。

AllowSorting 是否允许在受保护的工作表上使用排序选项，只读。

AllowUsingPivotTables 是否允许用户在受保护的工作表上处理数据透视表，只读。

其中每一个设置都对应着保护工作表中的某个选项。

tionMode 属性

表示是否启用了用户界面专用保护，Boolean 类型，只读。

要打开用户界面保护，请使用 Protect 方法，并将 UserInterfaceOnly 参数设置为 True。

关于Protect方法的UserInterfaceOnly属性描述如下：

UserInterfaceOnly如果为 True，则保护用户界面，但不保护宏。

如果省略此参数，则既保护宏也保护用户界面。

说得直接点就是，当为True时，用VBA是可以修改单元格的，但是在工作表操作是不能修改的。

这个属性设置只适用于当前会话，工作簿关闭后再打开属性又会是False。

如果每次都想保护时能够用代码操作的话，可以在OPEN事件里在调用protect方法传递UserInterfaceOnly:=True

示例：

If tContents Then

ect

End If

'直接调用ect也是可以的。

'调用Unprotect方法时，如果工作表并没有保护，则方法不起作用。

t userinterfaceonly:=True

If tionMode Then

Range("a1").Value = Now

End If

ect

tuserinterfaceonly:=False

Range("a1").Value = Now

'这里会报1004错误

'如果在上一行的protect方法中省略userinterfaceonly:=False，还是能写入。

'如果在调用protect方法时有在参数里明确设置过userinterfaceonly:=true的情况下

'如果要取消这项，建议在再次调用Protect方法时还是显式加上userinterfaceonly:=false

'虽然帮助里说这个参数省略时为False，但从刚刚的测试发现，还是能写入。

'似乎有记忆效应。

tScenarios 属性

WorkSheet工作表对象(六)

工作表对象的属性告一段落，从今天开始我们讲讲工作表对象的方法。

te 方法

使当前工作表成为活动工作表。

调用此方法等同于单击工作表的标签。

示例：

Worksheets("Sheet1").Activate

‘激活Sheet1工作表

Workbooks(2).Worksheets(3).Activate

‘激活工作簿集合中的第2个工作簿内的第3个工作表

ate 方法

计算特定工作表的单元格。

相当于按F9重新计算一次。

Worksheets("Sheet1").Calculate

Calculate方法在工作簿，工作表，单元格甚至应用对象中都有存在。

所有打开的工作簿 ate（或只是 Calculate）

指定工作表 Worksheets(1).Calculate

指定区域 Worksheets(1).Rows(2).Calculate

方法

将工作表复制到工作簿的另一位置。

语法：Copy(Before, After)

Before 可选 Variant 将要在其之前放置所复制工作表的工作表。如果指定了 After，则不能指定 Before。

After 可选 Variant 将要在其之后放置所复制工作表的工作表。如果指定了 Before，则不能指定 After。

说明：如果既不指定 Before 也不指定 After，则 Microsoft Excel 将新建一个工作簿，其中包含复制的工作表。

工作表复制后，复制后的新工作表自动成为活动工作表。

示例：

Worksheets("Sheet1").CopyAfter:=Worksheets("Sheet3")

‘Sheet1工作表复制到SHEET3工作表之后。

Dim wb As Workbook

Set wb = Workbooks(2)

Worksheets(3).Copyafter:=eets()

‘把当前活动工作簿内的第3个工作表复制到工作簿集合中的第2个工作簿中，放置在最后一个工作表之后。

方法

删除工作表对象。

返回值：Boolean，表示删除成功与否。

如果用户在对话框中单击“取消”，则返回 False，如果用户单击“删除”，则返回 True。

在删除 Worksheet 时，此方法显示一个对话框，用于提示用户确认是否删除。默认情况下显示此对话框。

如果不想显示确认删除的对话框，可以设置Application对象的DisplayAlerts属性为False来实现。

示例：

yAlerts = False

Worksheets(1).Delete

yAlerts = True

在删除时，必须保留一个可见的工作表对象，否则会产生代码为1004的错误提示。

方法

将工作表移到工作簿中的其他位置。

语法：Move(Before, After)

Before 可选 Variant 在其之前放置移动工作表的工作表。如果指定了 After，则不能指定 Before。

After 可选 Variant 在其之后放置移动工作表的工作表。如果指定了 Before，则不能指定 After。

如果既不指定 Before 也不指定 After，Microsoft Excel 将新建一个工作簿，其中包含所移动的工作表。

移动后的工作表将成为活动工作表

示例：

Worksheets(1).Moveafter:=Workbooks(2).Worksheets(1)

fore:=Workbooks(1).Worksheets(1)

‘移来移去，又还原了。

同理，在跨工作表移动的时候，要注意工作簿中至少要保留一个可见的工作表，否则会产生1004错误（中间会提示是否关 闭工作簿，点否后接着是1004错误）。

AsFixedFormat 方法

将指定工作表导出为指定格式的文件。

语法：

ExportAsFixedFormat(Type, Filename,Quality, IncludeDocProperties, IgnorePrintAreas,

From, To, OpenAfterPublish)

Type 必选 XlFixedFormatType 要导出为的文件格式类型，可为xlTypePDF和xlTypeXPS。

Filename 可选 Variant 要保存的文件的文件名。可以包括完整路径，否则 Excel 会将文件保存在当前文件夹中。

Quality 可选 Variant 可选XlFixedFormatQuality。指定已发布文件的质量。

IncludeDocProperties 可选 Variant 若要包括文档属性，则为True；否则为 False。

IgnorePrintAreas 可选 Variant 若要忽略发布时设置的任何打印区域，则为 True；否则为 False。

From 可选 Variant 发布的起始页码。如果省略此参数，则从起始位置开始发布。

To 可选 Variant 发布的终止页码。如果省略此参数，则发布至最后一页。

OpenAfterPublish 可选 Variant 若要在发布文件后在查看器中显示文件，则为 True；否则为 False。

FixedFormatExtClassPtr 可选 Variant 指向FixedFormatExt 类的指针。

示例：

AsFixedFormatType:=xlTypePDF, Filename:="d:"

‘将当前活动工作表输出为PDF文件。

如果工作表为空白工作表，输出时会产生1004错误，显示“示发现可以打印的内容”。

WorkSheet工作表对象(七)

方法

将“剪贴板”中的内容粘贴到工作表上。

语法：Paste(Destination, Link)

Destination 可选 Variant 一个 Range 对象，指定用于粘贴剪贴板中内容的目标区域。

如果省略此参数，就使用当前的选定区域。仅当剪贴板中的内容能被粘贴到某区域时，才能指定此参数。

如果指定了此参数，则不能使用 Link 参数。

Link 可选 Variant 如果为 True，则链接到被粘贴数据的源。

如果指定此参数，则不能使用 Destination 参数，默认值是 False。

如果不指定 Destination 参数，则在使用该方法之前必须选择目标区域。

该方法可能会修改工作表的选定区域，这取决于剪贴板中的内容。

示例：

Range("a33").Select

Workbooks(1).Worksheets(1).Paste

'复制活动工作表内使用的单元格区域，粘贴在活动工作表的A33单元格区域

orkbooks(2).Worksheets(1).Range("b30")

'复制活动工作表内使用的单元格区域到剪贴板，粘贴到第2个工作簿中的第一个工作表内的B30

Workbooks(2).Worksheets(2).Paste link:=True

'复制活动工作表内使用的单元格区域，粘贴到第2个工作簿对象中的第2个工作表内（粘贴链接）

pecial 方法

以指定格式将剪贴板中的内容粘贴到工作表上。

可用本方法从其他应用程序中粘贴数据，或以特定格式粘贴数据。

语法：

PasteSpecial(Format, Link, DisplayAsIcon,IconFileName, IconIndex, IconLabel,

NoHTMLFormatting)

Format 可选 Variant 指定数据的剪贴板格式的字符串。

Link 可选 Variant 如果为 True，则链接到被粘贴数据的源。

如果源数据不适于链接，或源应用程序不支持链接，将忽略此参数。默认值是 False。

DisplayAsIcon 可选 Variant 如果为 True，则将粘贴内容显示为图标。默认值是 False。

IconFileName 可选 Variant 如果DisplayAsIcon 为 True，则指定包含所用图标的文件名。

IconIndex 可选 Variant 图标文件内的图标索引号。

IconLabel 可选 Variant 图标的文本标签。

NoHTMLFormatting 可选 Variant 如果为 True，则从 HTML 中删除所有的格式设置、超链接和图像。

如果为 False，则完整粘贴 HTML。默认值是 False。

说明：仅当 Format =“HTML”时，NoHTMLFormatting 才起作用，所有其他情况下将忽略

NoHTMLFormatting。

必须在使用本方法之前选定目标区域。

该方法可能会修改工作表的选定区域，这取决于剪贴板中的内容。

示例：

Worksheets("Sheet1").Range("D1").Select

pecial format:= _

"Microsoft Word 8.0 Document Object"

‘此示例将剪贴板中的 MicrosoftWord 文档对象粘贴到工作表 Sheet1 上的单元格 D1

中。

Worksheets("Sheet1").Range("F5").Select

pecial _

Format:="Microsoft Word 8.0 Document Object", _

DisplayAsIcon:=True

‘此示例粘贴相同的 MicrosoftWord 文档对象，并将其显示为图标。

ut 方法

打印特定的工作表对象。

语法：

PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile,Collate, PrToFileName,

IgnorePrintAreas)

From 可选 Variant 打印的开始页号。如果省略此参数，则从起始位置开始打印。

To 可选 Variant 打印的终止页号。如果省略此参数，则打印至最后一页。

Copies 可选 Variant 打印份数。如果省略此参数，则只打印一份。

Preview 可选 Variant 如果为 True，MicrosoftExcel 将在打印对象之前调用打印预览。

如果为 False（或省略该参数），则立即打印对象。

ActivePrinter 可选 Variant 设置活动打印机的名称。

PrintToFile 可选 Variant 如果为 True，则打印到文件。

如果没有指定 PrToFileName，Microsoft Excel 将提示用户输入要使用的输出文件的文件名。

Collate 可选 Variant 如果为 True，则逐份打印多个副本。

PrToFileName 可选 Variant 如果PrintToFile 设为 True，则该参数指定要打印到的文件名。

IgnorePrintAreas 可选 Variant 如果为 True，则忽略打印区域并打印整个对象。

示例：

ut

打印当前活动工作表。

review 方法

按对象打印后的外观效果显示对象的预览。

语法：PrintPreview(EnableChanges)

EnableChanges 可选 Variant 传递 Boolean 值，以指定用户是否可更改边距和打印预览中可用的其他页面设置选项。

示例：

Worksheets("Sheet1").PrintPreviewfalse

打印和打印预览这两个方法可以参考之前有讲过其他对象中的这两个方法，基本无差，只是打印的对象略有不同。

WorkSheet工作表对象(八)

t 方法

保护工作表使其不能被修改。

语法：

Protect(Password, DrawingObjects, Contents,Scenarios, UserInterfaceOnly,

AllowFormattingCells, AllowFormattingColumns,AllowFormattingRows,

AllowInsertingColumns, AllowInsertingRows,AllowInsertingHyperlinks,

AllowDeletingColumns, AllowDeletingRows, AllowSorting,AllowFiltering,

AllowUsingPivotTables)

Password 一个字符串，该字符串为工作表或工作簿指定区分大小写的密码。

如果省略此参数，不用密码就可以取消对工作表或工作簿的保护。

否则，必须指定密码才能取消对工作表或工作簿的保护。

如果忘记了密码，就无法取消对工作表或工作簿的保护。

DrawingObjects 如果为 True，则保护形状。默认值是True。

Contents 如果为 True，则保护内容。对于图表，这样会保护整个图表。

对于工作表，这样会保护锁定的单元格。默认值是 True。

Scenarios 如果为 True，则保护方案。此参数仅对工作表有效。默认值是 True。

UserInterfaceOnly 如果为 True，则保护用户界面，但不保护宏。如果省略此参数，则既保护宏也保护用户界面。

AllowFormattingCells 如果为 True，则允许用户为受保护的工作表上的任意单元格设置格式。默认值是 False。

AllowFormattingColumns 如果为 True，则允许用户为受保护的工作表上的任意列设置格式。默认值是 False。

AllowFormattingRows 如果为 True，则允许用户为受保护的工作表上的任意行设置格式。默认值是 False。

AllowInsertingColumns 如果为 True，则允许用户在受保护的工作表上插入列。默认值是 False。

AllowInsertingRows 如果为 True，则允许用户在受保护的工作表上插入行。默认值是

False。

AllowInsertingHyperlinks 如果为 True，则允许用户在受保护的工作表中插入超链接。默认值是 False。

AllowDeletingColumns 如果为 True，则允许用户在受保护的工作表上删除列，要删除的列中的每个单元格都被解除锁定。 默认值是 False。

AllowDeletingRows 如果为 True，则允许用户在受保护的工作表上删除行，要删除的行中的每个单元格都被解除锁定。默认 值是 False。

AllowSorting 如果为 True，则允许用户在受保护的工作表上进行排序。

排序区域中的每个单元格必须是解除锁定的或取消保护的。

默认值是 False。

AllowFiltering 如果为 True，则允许用户在受保护的工作表上设置筛选。

用户可以更改筛选条件，但是不能启用或禁用自动筛选功能。

用户也可以在已有的自动筛选功能上设置筛选。默认值是 False。

AllowUsingPivotTables 如果为 True，则允许用户在受保护的工作表上使用数据透视表。

默认值是 False。

大部分的选项都在上图中能找到对应的设置。

上图中的选定锁定单元格与未锁定单元格是通过Selection属性来设置的。

该属性的默认值为0（xlNoRestrictions），可为下列值：

xlNoSelection防止在工作表上进行任何选择操作

xlUnlockedCells 仅允许 Locked 属性为 False 的单元格才能被选定

xlNoRestrictions 允许选定任意单元格。

其值只有在工作表处于保护状态时才有效。

如果要想在保护的情况下，但是VBA代码又能操作工作表，可以采取以下两种方法：

一是在调用Protect方法时传递参数UserInterfaceOnly:=True；

二是先取消保护，修改完再加上保护。

示例：

tPassword:="123", userinterfaceonly:=True

Range("a1:c5").Formula ="=randbetween(1,50)"

‘保护模式下代码可以操作工作表

tPassword:="123", userinterfaceonly:=False

ectPassword:="123"

Range("a1:c5").Formula ="=randbetween(1,50)"

tPassword:="123", userinterfaceonly:=False

‘先取消保护，再操作，然后再重新加上保护。

ect 方法

取消工作表或工作簿的保护。

如果工作表或工作簿不是受保护的，则此方法不起作用。

语法：Unprotect(Password)

Password指定用于解除工作表或工作簿保护的密码，此密码是区分大小写的。

如果工作表或工作簿不设密码保护，则省略此参数。

如果对工作表省略此参数，而该工作表又设有密码保护，Microsoft Excel 将提示您输入密码。

如果对工作簿省略此参数，而该工作簿又设有密码保护，则该方法将失效。

如果您忘记了密码，将不能取消工作表或工作簿的保护。

示例：

ect

ect "123"

WorkSheet工作表对象(九)

lData 方法

使当前筛选列表的所有行均可见。

如果正在使用自动筛选，则本方法将下拉列表框内容改为“（全部）”。

示例：

If Mode Then

lData

Else

If lterMode Then

MsgBox "当前没有任何数据的筛选"

Else

MsgBox "没有打开自动筛选"

End If

End If

选检测当前是否有进行数据的筛选，有筛选则显示所有内容。

否则再判断是否启用自动筛选与没有进行数据筛选的两种状态。

如果没有进行数据筛选，调用ShowAllData方法是产生1004错误，提示“方法无效”。

kgroundPicture 方法

为工作表设置背景。

语法：SetBackgroundPicture(Filename)

Filename 必选 String 图形文件名。

示例：

Dim strFile As String

strFile = "C:UsersbbPictures我的图片"& ""

kgroundPicture strFile

当指定的文件非有效的图形文件或不存在的文件时，会产生1004运行错误。

在10里这个方法对应的操作为： 页面布局 选项， 页面设置 组， 背景。

如果要用代码删除工作表的背景，还是用这个方法，不过参数是一个长度为零的字符串。

kgroundPicture""

llPageBreaks 方法

重新设置指定工作表上的所有分页符。

在分页模式下，页面的水平和垂直分页符可随意拖动，导致分页混乱后，可以用这个方法复拉原先的分页布局。

示例：

llPageBreaks

bjects 方法

返回一个对象，它代表工作表上的嵌入式图表的集合（ChartObject 对象）

示例：

Dim cht As ChartObject

For Each cht In bjects

= "TEST"

Next

在很多集合中，用Name属性可以唯一的标识集合中的一个成员（也就是Name唯一性）。

在ChartObjects集合中，这一规则无效。

上面的代码示例中，所有的ChartObject对象都改名为Tetst。

如果用Test去引用时，返回的总是第一个名字为Test的ChartObject对象。

ects 方法

返回一个对象，它代表图表或工作表上的所有 OLE 对象的集合（OLEObjects 集合）。

语法：OLEObjects(Index)

Index 可选，OLE 对象的名称或编号。

返回值：Object

示例：

Dim ole As Object

For Each ole In ects

,TypeName()

Next

在Excel 2010里，这个属性只会返回位于指定工作表上的ActiveX控件有效。

如果是表单控件，请用Shapes集合属性。

Dim shp As Shape

For Each shp In

,TypeName(gObject)

Next

通过Shapes属性也可以引用到位于指定工作表里的ActiveX控件。

ables 方法

返回一个对象，该对象表示工作表上的所有数据透视表的集合（PivotTables 对象）。

语法：PivotTables(Index)

Index 可选，报表的名称或编号。

返回值：Object

示例：

MsgBox "当前工作表中共有 " & & " 个数据透视表"

ableWizard 方法

创建一个新的数据透视表。

此方法不显示“数据透视表向导”，它不适用于 OLE DB 数据源。

请使用 Add 方法添加数据透视表缓存，然后创建基于该缓存的数据透视表。

语法：

PivotTableWizard(SourceType,SourceData,TableDestination, _

TableName,RowGrand,ColumnGrand,SaveData, _

HasAutoFormat,AutoPage,Reserved,BackgroundQuery, _

OptimizeCache,PageFieldOrder,PageFieldWrapCount, _

ReadData,Connection)

返回值:一个代表新数据透视表的 PivotTable 对象。

由于参数比较多，不一一介绍，会数据表的童鞋可以试试用代码生成。或者用录制宏的方法了解。

示例：

ableWizardxlDatabase,Range("A1:C100")

使用过 Microsoft Excel 数据库（包含在单元格区域 A1:C100 中）创建数据透视表。

关于工作表的方法就告一段落。

WorkSheet工作表对象(十)

今天聊聊工作表对象的事件。

前面讲工作簿对象Workbook时也有讲到事件。

工作簿事件是写在工程内的Thisworkbook当中。

而工作表事件的代码则是放在特定的工作表对象之中，而且只有特定工作表触发了事件后，相关的事件过程才会被执行。

首先我们看看工作表的事件代码放哪？

在工程资源管理器中，定位到特定的VBA工程当中，在文件夹模式中，可以看到有Microsof Excel 对象节点，展开后下面就有工程所在的工作簿内的所有工作表和ThisWorkbook。

双击特定的工作表对象，就会跳转到该对象的代码窗口中。

和之前的工作簿事件一样，在代码窗口的顶端左边是对象列表。

下拉列表，从中选择“WorkSheet”。

顶端右边的是过程/事件列表。

对象列表中选择Worksheet后，事件列表中就有工作表对象的所有事件过程。

只需选择相应的事件过程，过程描述就会自动插入到代码窗口中。

当代码窗口中已经有相应的事件过程后，选择相应的事件过程会直接跳转到代码窗口中的对应的过程当中。

关于事件过程的声明不需要记，系统已经帮我们定义好了，我们只需在过程/事件下拉列中选择即可添加好事件过程的声明部分，余下的部分只需要完善代码。

te 事件

激活工作表、图表工作表时发生此事件。

说明：新建窗口时不发生此事件。

切换两个显示同一工作簿的窗口时，将发生 WindowActivate 事件，但不发生工作簿的

Activate 事件。

示例：

Private Sub Worksheet_Activate()

Range("a1:a10").Sort Key1:=Range("a1"),Order:=xlAscending

End Sub

vate 事件

图表、工作表被停用时发生此事件。

示例：

Private Sub Workbook_Deactivate()

e xlArrangeStyleTiled

End Sub

此示例在工作簿被停用时对所有打开的窗口进行排列。

DoubleClick 事件

当双击工作表时发生此事件，此事件先于默认的双击操作。

语法：BeforeDoubleClick(Target, Cancel)

Target 必选，双击发生时最靠近鼠标指针的单元格。

Cancel 必选，事件发生时为 False。如果事件过程将此参数设为True，则在完成此过程后，不执行默认的双击操作。

说明：

使用 DoubleClick 方法并不触发此事件。

用户双击单元格的边框时不触发此事件。

通过设置Cancel为True，可以双击单元格不进入编辑模式。

RightClick 事件

右键单击工作表时发生此事件，此事件先于默认的右键单击操作。

语法：BeforeRightClick(Target, Cancel)

Target 必选，右键单击发生时最靠近鼠标指针的单元格。

Cancel 必选，事件发生时为 False。

如果事件过程将此参数设为True，则在完成此过程后，不执行默认的右击单击操作。

与其他工作表事件相似，当指针在形状或命令栏（工具栏或菜单栏）上时，右键单击不触发此事件。

示例：

Private SubWorksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim icbc As Object

For Each icbc In dBars("cell").Controls

If = "brccm" Then

Next icbc

If Not ect(Target, Range("b1:b10")) Is Nothing Then

With dBars("cell").Controls _

.Add(Type:=msoControlButton,before:=6, _

temporary:=True)

.Caption = "New Context Menu Item"

.OnAction = "MyMacro"

.Tag = "brccm"

End With

End If

End Sub

示例为 B1:B10 单元格添加新的快捷菜单项。

WorkSheet工作表对象(十一)

事件

当用户更改工作表中的单元格，或外部链接引起单元格的更改时发生此事件。

语法：Change(Target)

Target 必选，代表更改的区域，可以是多个单元格。

当单元格在重新计算过程中更改时，将不会发生该事件。使用 Calculate 事件可以捕获工作表重新计算。

关于Change事件何时触发何时不触发，下面几种情况有必要了解：

设置单元格的格式不会触发，但是清除格式，粘贴格式，格式刷会触发。

合并单元格，取消合并单元格不会触发事件

针对单元格的批注操作是不会触发事件的，象新建，删除，修改批注。

双击单元格（或F2）后进入编辑模式后，不输入任何内容回车，或直接跳转到其他单元格均会触发

单元格内的公式自动重新计算（或者按F9手工重新计算）不会触发此事件

直接删除一个空的单元格也会触发事件

有的Excel命令操作单元格会触发，有的不会触发，象排序，合并不会触发，但清除格式会。

用VBA代码操作单元格时，是否会触发参考以上几条。

ionChange 事件

当工作表上的选定区域发生改变时发生此事件。

语法：SelectionChange(Target)

Target 必选，代表新选定的区域。

示例：

Private Sub Worksheet_SelectionChange(ByValTarget As Range)

With ActiveWindow

.ScrollRow =

.ScrollColumn =

End With

End Sub

本示例滚动工作簿窗口，让选定区域位于窗口的左上角。

Hyperlink 事件

当单击工作表上的任意超链接时，发生此事件。

对于应用程序级或工作簿级的事件，请参阅 SheetFollowHyperlink 事件和

SheetFollowHyperlink Event 事件。

语法：FollowHyperlink(Target)

Target 必选，代表超链接目标的 Hyperlink对象。

示例：

Private Sub Worksheet_FollowHyperlink(ByValTarget As Hyperlink)

With UserForm1

.m s

.Show

End With

End Sub

本示例对在活动工作簿中访问过的所有链接保留一个列表或历史记录。

ate 事件

对于 Worksheet 对象，在对工作表进行重新计算之后发生此事件。

示例：

Private Sub Worksheet_Calculate()

Columns("A:F").AutoFit

End Sub

每当重新计算工作表后，此示例会调整 A 列到 F 列的宽度。

工作表事件基本上就这么多，常用的也就几个。

对于数透表来说，也有提供了相关的数据透视表事件，会数据透视表的童鞋没事可以去折腾下。

ableAfterValueChange 事件

在编辑或重新计算（对于包含公式的单元格）数据透视表中的单元格或单元格区域后发生。

ableBeforeAllocateChanges 事件

在将更改应用到数据透视表前发生。

ableBeforeCommitChanges 事件

在向数据透视表的 OLAP 数据源提交更改之前发生。

ableBeforeDiscardChanges 事件

在放弃对数据透视表所做的更改之前发生。

ableChangeSync 事件

在更改数据透视表之后发生。

ableUpdate 事件

工作簿中的数据透视表更新后发生此事件。

补充资料：

关于在事件过程代码内避免再次触发事件（事件循环）。

先看以下代码：

Private Sub Worksheet_Change(ByVal TargetAs Range)

If Len() Then

(1, 1) = + 1

End If

End Sub

当在事件过程所在的工作表内的A1单元格内输入1回车后，会触发Change事件。

执行到事件代码后，先检测触事件的单元格内是否有输入任何内容。

如果有输入，就在单元格的右下角内填入2。

Target往下偏移一行，往右移一列所在的单元格，即A1的右下角为B2。

代码修改了A2单元格内的值，再次触发事件，如此反复运行。

我的是WIN7系统+OFFICE 2010，最后填充到了CR96单元格（单元格内的值为96）.

此次事件循环触发了多少次已经尽显。

代码原有的本意是只运行一次，即在当前了数值的单元格右下角填入数据。

为了解决这个事件触发循环的问题，可以用Application对象的EnableEvents属性来解决。

通过修改EnableEvents属性为False，可以实现禁用事件。

以上面代码举例就是修改Target右下角的单元格不会再次触发Change事件，这样可以避免事件循环。

当代码操作完后，记得再次启用事件，否则事件代码是不会被触发的。

很多新手在用这个属性时，经常会忘了在代码结束前启用事件。

上面的代码改完后如下：

Private Sub Worksheet_Change(ByVal TargetAs Range)

If > 1 Then Exit Sub

'当一次改变多个单元格的值时忽略

If Len() Then

'检测单元格内是否有输入内容

Events = False

(1, 1) = Val() + 1

Events = True

End If

End Sub