Excel VBA（工作效率倍增的实用技巧）下篇

ExcelVBA（工作效率倍增的实用技巧）下篇目录\h第8章Window对象操作技巧\h8.1控制窗口\h例141创建窗口\h例142获取窗口状态\h例143拆分窗格\h例144并排比较窗口\h例145排列窗口\h例146窗口显示比例\h8.2控制工作表的显示选项\h例147工作簿显示选项\h例148工作表显示选项\h例149工作表网格线\h例150获取指定窗口选中的信息\h第9章Chart对象操作技巧\h9.1创建图表\h例151创建图表工作表\h例152创建嵌入式图表\h例153转换图表类型\h例154删除图表\h9.2控制图表对象\h例155判断工作表的类型\h例156重排嵌入式图表\h例157调整图表的数据源\h例158为图表添加阴影\h例159显示数据标签\h例160将图表保存为图片\h例161设置图表颜色\h例162按值显示颜色\h9.3图表事件\h例163激活图表工作表\h例164显示图表各子对象名称\h例165捕获嵌入式图表事件\h第10章用户界面设计技巧\h10.1使用内置对话框\h例166显示打开对话框（使用GetOpenFilename方法）\h例167显示保存文件对话框（使用GetSaveAsFilename方法）\h例168显示内置对话框\h例169用VBA调用Excel功能区功能\h10.2创建自定义窗体\h例170制作Splash窗口\h例171控制窗体显示\h例172列表框间移动数据\h例173通过窗体向工作表添加数据\h例174制作多页窗体——报名登记\h例175通过窗体设置单元格格式\h例176用窗体控制工作表显示比例\h例177调色板窗体\h例178在窗体中显示图表\h例179制作向导窗体\h例180拖动窗体上的控件\h例181制作交通信号灯\h例182制作进度条\h第11章使用Excel处理工作表数据技巧\h11.1处理公式\h例183判断单元格是否包含公式\h例184自动填充公式\h例185锁定和隐藏公式\h例186将单元格公式转换为数值\h11.2数据查询\h例187查找指定的值\h例188带格式查找\h例189查找上一个/下一个数据\h例190代码转换\h例191模糊查询\h11.3数据排序\h例192用VBA代码排序\h例193乱序排序\h例194自定义序列排序\h例195多关键字排序\h例196输入数据自动排序\h11.4数据筛选\h例197用VBA进行简单筛选\h例198用VBA进行高级筛选\h例199筛选非重复值\h例200取消筛选\h第12章使用Excel处理数据库技巧\h12.1用ADO访问Excel工作表\h例201使用ADO连接数据库\h例202从工作表中查询数据\h例203汇总数据\h12.2处理数据库中的数据\h例204从Access中获取数据\h例205添加数据到Access\h例206创建Access数据库\h第13章文件和文件夹操作技巧\h13.1用VB语句操作文件和文件夹\h例207显示指定文件夹的文件\h例208判断文件（文件夹）是否存在\h例209新建文件夹\h例210复制文件\h例211重命名文件或文件夹\h例212删除文件\h例213查看文件属性\h13.2用FSO操作文件和文件夹\h例214判断文件是否存在（FSO）\h例215分离文件名和扩展名\h例216新建和删除文件夹（FSO）\h例217复制文件（FSO）\h例218复制文件夹（FSO）\h例219列出文件夹名称\h例220显示文件属性\h例221删除所有的空文件夹\h例222显示驱动器信息\h第14章文本文件操作技巧\h14.1用VB语句操作文本文件\h例223创建文本文件\h例224读取文本文件数据\h例225工作表保存为文本文件\h14.2用FSO操作文本文件\h例226创建文本文件（FSO）\h例227添加数据到文本文件（FSO）\h例228读取文本文件数据（FSO）\h第15章用Excel控制其他程序技巧\h15.1控制Office应用程序\h例229打开Word文档\h例230从Word文档中获取数据\h例231生成成绩通知书\h例232在Excel中打开PPT\h例233在Excel中创建PPT\h15.2调用其他程序\h例234运行系统自带程序\h例235在Excel中打开控制面板\h第16章VBE工程实用操作技巧\h例236列出工程的所有组件\h例237显示工作簿中VBA的过程名\h例238导出VBA过程代码\h例239列出工程引用的外部库第8章Window对象操作技巧Window对象代表一个窗口，能对窗口特性进行设置和操作。许多工作表特征（如滚动条和标尺）实际上是窗口的属性。Window对象是Windows集合的成员。在Excel中，Application对象和Workbook对象都有Windows集合，其中Application对象的Windows集合包含应用程序中的所有窗口，而Workbook对象的Windows集合只包含指定工作簿中的窗口。8.1控制窗口通过Window对象的属性和方法可创建窗口、拆分窗口、设置窗口大小、显示比例、控制窗口显示状态等。例141创建窗口1.案例说明打开本例工作簿，单击工作表中的“创建窗口”按钮，将生成当前工作簿的第2个窗口，窗口名称为“例144创建窗口:2”，再次单击“创建窗口”按钮将创建第3个窗口，如图8-1所示。图8-1创建窗口2.关键技术（1）NewWindow方法在Excel2013中，在“视图”选项卡的“窗口”组中，单击“新建窗口”按钮，可新建一个Excel窗口，该窗口的标题栏名称将显示为“创建窗口:2”，在新建的窗口中将显示活动窗口的副本，如图8-2所示。图8-2第一个窗口副本在VBA代码中，使用Window对象的NewWindow方法，可新建一个窗口或者创建指定窗口的副本。注意：窗口号和窗口索引（Index属性）是两个不同的概念，例如，名称为“创建窗口:2”的窗口，其窗口号为2，而窗口索引为该窗口在Windows集合中的位置，可以是窗口名称或编号。（2）WindowNumber属性Window对象的WindowNumber属性返回窗口号。例如，名称为“创建窗口:2”的窗口，其窗口号为2。大多数窗口的窗口号为1。3.编写代码“创建窗口”按钮的VBA代码如下：

Sub创建窗口()

ActiveWindow.NewWindow

MsgBox"新建窗口的窗口号是："&ActiveWindow.WindowNumber

EndSub

例142获取窗口状态1.案例说明打开本例工作簿，如图8-3所示。单击Excel应用程序右上角的最大化按钮后，单击“获取窗口状态”按钮，首先将弹出对话框显示Excel应用程序窗口的状态，接着弹出对话框显示当前工作簿窗口的状态，如图8-4所示。图8-3获取窗口状态图8-4应用程序和窗口的状态2.关键技术窗口的状态包括两方面的意思，首先是Excel应用程序窗口的状态，另一个是工作簿窗口的状态。窗口状态有三种形式，通过Window对象的WindowState属性可返回或设置窗口的状态。可用以下常量表示窗口的状态。xlMaximized：最大化。xlMinimized：最小化。xlNormal：正常。3.编写代码“获取窗口状态”按钮的VBA代码如下：

Sub获取窗口状态()

Dimstr1AsString

str1="Excel应用程序窗口的状态："

SelectCaseApplication.WindowState

CasexlMaximized

str1=str1&"最大化。"

CasexlMinimized

str1=str1&"最小化。"

CasexlNormal

str1=str1&"正常。"

EndSelect

MsgBoxstr1

str1="当前活动工作簿窗口的状态："

SelectCaseActiveWindow.WindowState

CasexlMaximized

str1=str1&"最大化。"

CasexlMinimized

str1=str1&"最小化。"

CasexlNormal

str1=str1&"正常。"

EndSelect

MsgBoxstr1

EndSub

以上代码首先获取Excel应用程序窗口的状态并显示出来，接着获取工作簿窗口的状态并显示出来。例143拆分窗格1.案例说明打开本例工作簿，如图8-5所示。在工作表中单击选择一个单元格（本例中选择B3），单击“拆分窗格”按钮，窗口将在该处拆分为4个窗格，如图8-6所示。图8-5拆分窗口图8-6拆分后的状态单击“冻结窗格”按钮，上方和左侧的窗格将被冻结，如图8-7所示。图8-7冻结窗口本例的两个按钮都具有开关功能，即单击一次时拆分窗口，再次单击时取消拆分。在未拆分窗口的状态下，选取工作表中的某个单元格后再单击“冻结窗格”按钮，将在所选单元格处冻结窗格。2.关键技术（1）Split属性通过Window对象的Split属性可查询窗口是否被拆分。如果指定窗口被拆分，则该属性值为True。将该属性值设置为False，可取消指定窗口的拆分状态。（2）拆分窗口的属性窗口可以进行水平和垂直两个方向上的拆分，可通过Window对象的以下两个属性进行控制。SplitRow属性：返回或设置将指定窗口拆分成窗格处的行号（拆分线以上的行数）。SplitColumn属性：返回或设置将指定窗口拆分成窗格处的列号（拆分线左侧的列数）。（3）FreezePanes属性通过设置Window对象的FreezePanes属性为True，可冻结窗格，设置其值为False，则取消冻结窗口。3.编写代码（1）“拆分窗格”按钮的VBA代码如下：

Sub拆分窗格()

DimrAsLong,cAsLong

r=ActiveCell.Row

c=ActiveCell.Column

WithActiveWindow

If.SplitThen

.Split=False

Else

.SplitRow=r-1

.SplitColumn=c-1

EndIf

EndWith

EndSub

以上代码中，因为SplitRow和Splitcolumn属性是从指定行数的上方和列数的左侧进行拆分，所以需将活动单元格的行数和列数减1。（2）“冻结窗格”按钮的VBA代码如下：

Sub冻结窗格()

ActiveWindow.FreezePanes=NotActiveWindow.FreezePanes

EndSub

例144并排比较窗口1.案例说明打开本例工作簿，如图8-8所示。为了执行并排比较功能，必须再打开一个进行并排比较的工作簿（本例是打开例143中的文件）。单击图8-8所示工作表中的“并排比较窗口”按钮，两个打开的工作簿将排列成如图8-9所示的形式。图8-8主工作簿此时，在任意一个工作簿窗口中移动活动单元格，对应窗口将随之滚动，使两个窗口显示相同行列的数据，方便进行比较。图8-9并排比较窗口再次单击“并排比较窗口”按钮，可取消窗口的并排状态。2.关键技术通过Windows集合的如下属性和方法可控制两个窗口的并排比较。CompareSideBySideWith方法：以并排模式排列两个窗口。SyncScrollingSideBySide属性：如果为True，在对文档进行并排比较的同时启用窗口内容的滚动功能。若为False，则在对文档进行并排比较的同时禁用窗口内容的滚动功能。BreakSideBySide方法：结束两个窗口的并排模式。3.编写代码“并排比较窗口”按钮的VBA代码如下：

Sub并排比较窗口()

WithWindows

If.SyncScrollingSideBySideThen

.BreakSideBySide

Else

.CompareSideBySideWith"例147拆分窗格.xls"

.SyncScrollingSideBySide=True

EndIf

EndWith

EndSub

例145排列窗口1.案例说明打开本例工作簿，如图8-10所示，单击“排列窗口”按钮，程序将创建当前窗口的一个副本，同时将这两个窗口垂直排列在Excel工作区中，如图8-11所示。图8-10排列窗口图8-11垂直排列窗口2.关键技术当Excel中有多个工作簿（或多个窗口）时，可使用Windows集合的Arrange方法对每个窗口进行排列，其语法格式为：

表达式.Arrange(ArrangeStyle,ActiveWorkbook,SyncHorizontal,SyncVertical)

该方法各参数都可省略，其含义如下。ArrangeStyle：用来指定窗口排列方式，可设置为层叠（xlArrangeStyleCascade）、平铺（xlArrangeStyleTiled）、水平排列（xlArrangeStyleHorizontal）和垂直排列（xlArrangeStyleVertical）4种方式之一。ActiveWorkbook：如果为True，则只排列活动工作簿的可见窗口。如果为False，则排列所有的窗口。默认值为False。ActiveWorkbook：如果为False或省略，则忽略参数SyncHorizontal。如果参数SyncHorizontal为True，则在水平滚动时同步活动工作簿的窗口。如果为False，则不同步窗口。默认值为False。ActiveWorkbook：如果为False或省略，则忽略SyncVertical参数。如果SyncVertical参数为True，则在垂直滚动时同步活动工作簿的窗口。如果为False，则不同步窗口。默认值为False。3.编写代码“排列窗口”按钮的VBA代码如下：

Sub排列窗口()

DimhAsLong,wAsLong

ActiveWindow.NewWindow'创建当前窗口的一个副本

Windows.ArrangexlArrangeStyleTiled'平铺窗口

h=Windows(1).Height'获取第1个窗口的高度第8章

w=Application.UsableWidth'获取Excel工作区可用宽度

WithWindows(1)'设置第1个窗口的宽和高

.Width=w

.Height=h/2

.Left=0

EndWith

WithWindows(2)'设置第2个窗口的宽和高

.Width=w

.Height=h/2

.Top=h/2

.Left=0

EndWith

EndSub

以上代码首先创建活动窗口的一个副本，再设置窗口集合（Windows）的排列方式为平铺，最后根据Excel工作区的高度分别设置两个窗口的高度。例146窗口显示比例1.案例说明打开本例工作簿，如图8-12所示，单击工作表中的“窗口显示比例”按钮，弹出“显示比例”对话框，如图8-13所示。在对话框中输入显示比例（如150）后单击“确定”按钮，窗口中显示的内容将放大显示，如图8-14所示。图8-12窗口显示比例图8-13输入显示比例图8-14放大显示的效果2.关键技术在Excel2013的状态栏中，双击右下角的图标，将打开如图8-15所示的“显示比例”对话框。通过该对话框，用户可以调整表格的显示比例，其比例以百分数表示（100表示正常大小，200表示双倍大小，以此类推）。图8-15显示比例在VBA代码中，通过Window对象的Zoom属性来获取或设置窗口的显示比例。将此属性设为True，可将窗口大小设置成与当前选定区域相适应的大小。注意：本功能仅对窗口中当前的活动工作表起作用。若要对其他工作表使用此属性，必须先激活工作表。3.编写代码“窗口显示比例”按钮的VBA代码如下：

Sub窗口显示比例()

DimsAsInteger

s=Application.InputBox(prompt:="请输入窗口的显示比例："&_

vbCrLf&"（100表示正常大小，200表示双倍大小，以此类推）。",_

Title:="显示比例",Default:=100,Type:=1)

Ifs=0ThenExitSub

ActiveWindow.Zoom=s

EndSub

以上代码首先要求用户输入显示比例，如果在输入对话框中单击“取消”按钮，则返回值为0，表示退出子过程的执行。8.2控制工作表的显示选项通过Window对象的属性和方法不仅可以制作窗口，还可对窗口中工作表的相关显示选项进行控制，本例将演示这种使用方法。例147工作簿显示选项1.案例说明打开本例工作簿，如图8-16所示，分别单击工作表中的三个按钮，可隐藏或显示水平滚动条、垂直滚动条和工作表标签，效果如图8-17至图8-20所示。图8-16工作簿显示选项图8-17隐藏水平滚动条图8-18隐藏垂直滚动条图8-19隐藏工作表标签图8-20隐藏滚动条和工作表标签2.关键技术通过Window对象的以下三个属性值可获取或设置工作簿的显示选项。DisplayHorizontalScrollBar：水平滚动条。DisplayVerticalScrollBar：垂直滚动条。DisplayWorkbookTabs：工作表标签。以上三个属性值如果为True，则表示显示相关的元素，如果为False，则表示隐藏相关的元素。3.编写代码本例三个按钮的代码都比较简单，只需对相应的属性值取反，即可在显示或隐藏之间进行切换，具体代码如下：

Sub控制水平滚动条()

WithActiveWindow

.DisplayHorizontalScrollBar=Not.DisplayHorizontalScrollBar

EndWith

EndSub

Sub控制垂直滚动条()

WithActiveWindow

.DisplayVerticalScrollBar=Not.DisplayVerticalScrollBar

EndWith

EndSub

Sub控制工作表标签()

WithActiveWindow

.DisplayWorkbookTabs=Not.DisplayWorkbookTabs

EndWith

EndSub

例148工作表显示选项1.案例说明打开本例工作簿，如图8-21所示，在该图中单击“行号/列标”按钮可显示或隐藏工作表的行号和列标，如图8-22所示，为隐藏行号和列标的效果。图8-21工作表显示选项图8-22隐藏行号/列标单击“公式”按钮，可显示公式计算结果或显示公式的定义，如图8-23所示。图8-23显示公式单击“零值”按钮，可显示或隐藏工作表中的零值，如图8-24所示。图8-24隐藏0值2.关键技术通过Window对象的以下三个属性值可获取或设置工作表的显示选项。DisplayHeadings：显示行号/列标。DisplayFormulas：显示公式。DisplayZeros：显示零值。以上三个属性值如果为True，则表示显示相关的元素，如果为False，则表示隐藏相关的元素。3.编写代码本例三个按钮的代码都比较简单，只需对相应的属性值取反，即可在显示或隐藏之间进行切换，具体代码如下：

Sub行号列标()

WithActiveWindow

.DisplayHeadings=Not.DisplayHeadings

EndWith

EndSub

Sub公式()

WithActiveWindow

.DisplayFormulas=Not.DisplayFormulas

EndWith

EndSub

Sub零值()

WithActiveWindow

.DisplayZeros=Not.DisplayZeros

EndWith

EndSub

例149工作表网格线1.案例说明打开本例工作簿，如图8-25所示，单击工作表中的“显示/隐藏网格线”按钮，可隐藏当前工作表的网格线，如图8-26所示。再次单击该按钮，又可显示工作表的网格线。图8-25工作表网格线图8-26隐藏网格线单击“设置网格线颜色”按钮，弹出“网格线颜色”对话框，如图8-27所示，根据对话框中的提示输入1~3的数字，单击“确定”按钮，工作表的网格线将设置为红色、绿色或蓝色。图8-27设置网格线颜色2.关键技术（1）DisplayGridlines使用Window对象的DisplayGridlines属性可设置工作表是否显示网格线，如果该属性值为True，则显示网格线。提示：此属性仅影响显示的网格线，不能控制网格线的打印。此属性仅适用于工作表和宏工作表。（2）网格线颜色网格线颜色可使用Window对象的以下两个属性进行设置。GridlineColor属性：以RGB值返回或设置网格线颜色。GridlineColorIndex属性：返回或设置网格线颜色，其值为当前调色板中的索引。3.编写代码（1）“显示/隐藏网格线”按钮的VBA代码如下：

Sub显示隐藏网格线()

WithActiveWindow

.DisplayGridlines=Not.DisplayGridlines

EndWith

EndSub

（2）“设置网格线颜色”按钮的VBA代码如下：

Sub设置网格线颜色()

DimiAsInteger,rAsInteger,gAsInteger,bAsInteger

i=Application.InputBox(prompt:="请选择网格线的颜色："&_

vbCrLf&"（1.红色2.绿色3.蓝色）",_

Title:="网格线颜色",Default:=1,Type:=1)

SelectCasei

Case1:r=1

Case2:g=1

Case3:b=1

EndSelect

ActiveWindow.GridlineColor=RGB(r*255,g*255,b*255)

EndSub

例150获取指定窗口选中的信息1.案例说明打开本例工作簿，如图8-28所示，按住“Ctrl”键的同时单击三个工作表标签，选中多个工作表。再单击“已选择工作表”按钮，将弹出如图8-29所示的对话框，在该对话框中列出已选择的工作表。图8-28获取窗口选中的信息图8-29选择的工作表在工作表中拖动选择一个单元格区域，单击“已选择区域”按钮，将显示如图8-30所示的对话框，显示出当前选择区域的地址。图8-30选择区域地址单击“可见单元格”按钮，将弹出如图8-31所示的对话框，在对话框中显示当前窗口可见单元格的数量。图8-31可见单元格数量在工作表中单击选择单元格B3，单击“选择单元格尺寸”按钮，将弹出对话框显示所选单元格的尺寸，如图8-32所示。图8-32单元格尺寸2.关键技术本例使用Window对象的相关属性和方法来获取指定窗口选中元素的信息。SelectedSheets属性：返回一个Sheets集合，该集合表示指定窗口中所有选定的工作表。RangeSelection属性：返回一个Range对象，该对象表示指定窗口中工作表上的选定单元格，即使工作表上一个图形对象是活动或选定的。VisibleRange属性：返回一个Range对象，它代表显示在窗口或窗格中的单元格区域。如果列或行只显示了一部分，则说明它是包括在区域内的。PointsToScreenPixelsX方法：将横向度量值由以点为单位转换为以屏幕像素为单位。PointsToScreenPixelsY方法：将纵向度量值由以点为单位转换为以屏幕像素为单位。注意：当工作表中已选定一个图形对象，Selection属性返回的是一个图形对象，而不是一个Range对象；RangeSelection属性将返回在图形对象被选定之前选定的单元格区域。3.编写代码（1）“已选择工作表”按钮的VBA按钮如下：

Sub已选择工作表()

Dimws1AsWorksheet,str1AsString

str1="已选择的工作表："&vbCrLf

ForEachws1InActiveWindow.SelectedSheets

str1=str1&ws1.Name&vbCrLf

Next

MsgBoxprompt:=str1,Title:="选择的工作表"

EndSub

（2）“已选择区域”按钮的VBA代码如下：

Sub已选择区域()

MsgBox"当前窗口选择区域的地址为："&vbCrLf&ActiveWindow.RangeSelection.Address

EndSub

（3）“可见单元格”按钮的VBA代码如下：

Sub可见单元格()

DimcAsLong

c=ActiveWindow.VisibleRange.Cells.Count

MsgBox"当前窗口中共有"&c&"个单元格可见！"

EndSub

（4）“选择单元格尺寸”按钮的VBA代码如下：

Sub选择单元格尺寸()

DimwAsLong,hAsLong

WithActiveWindow

w=.PointsToScreenPixelsX(.Selection.Width)

h=.PointsToScreenPixelsY(.Selection.Height)

EndWith

MsgBox"当前窗口选中单元格的尺寸为(宽×高)："&w&"×"&h

EndSub

第9章Chart对象操作技巧在Excel中对数据进行分析时，使用图表可直观地查看分析结果。Excel提供了上百种图表类型，通过VBA代码可以控制图表的各方面。本章的实例演示使用VBA控制图表的方法。9.1创建图表在Excel中可以快速简便地创建图表，在程序中，通过VBA代码也可方便地创建图表。在Excel中创建的图表，可以嵌入到工作表中数据的旁边，也可插入到一个新的图表工作表中，分别称为嵌入式图表和图表工作表。例151创建图表工作表1.案例说明打开本例工作簿，如图9-1所示，单击工作表中的“成绩分析图表”按钮，将在工作簿中插入一个名为“Chart1”的图表工作表，并在图表工作表中生成簇状柱形图，如图9-2所示。图9-1数据工作表图9-2创建的图表2.关键技术（1）Charts集合Charts集合包含工作簿中所有图表工作表的集合。每个图表工作表都由一个Chart对象来表示，这不包括嵌入在工作表或对话框编辑表上的图表。通过Charts集合的Add方法可向集合中添加新的图表工作表（新建图表工作表）。Add方法的语法格式如下：

表达式.Add(Before,After,Count,Type)

该方法的参数都可省略，各参数的含义如下。Before：指定工作表的对象，新建的工作表将置于此工作表之前。After：指定工作表的对象，新建的工作表将置于此工作表之后。Count：要添加的工作表数。默认值为1。Type：指定要添加的图表类型，可创建的图表类型有很多，具体可参考ExcelVBA的帮助信息。提示：如果Before和After两者都被省略，新建的图表工作表将被插入到活动工作表之前。（2）SetSourceData方法通过Chart对象的SetSourceData方法，可为指定图表设置源数据区域。其语法格式如下：

表达式.SetSourceData(Source,PlotBy)

该方法的两个参数的含义如下。Source：为一个Range对象，用来指定图表的源数据区域。PlotBy：指定数据绘制方式，可使用常量xlColumns（数据系列在行中）和xlRows（数据系列在列中）之一。（3）ChartType属性通过Chart对象的ChartType属性可获取或设置图表类型。（4）ChartTitle对象通过Chart对象的ChartTitle属性，可返回一个ChartTitle对象，该对象表示指定图表的标题。通过该对象的属性可控制图表的标题，如设置标题文本、设置标题的格式等。注意：只有图表的HasTitle属性为True时，ChartTitle对象才存在，从而才能使用该对象。3.编写代码“成绩分析图表”按钮的VBA代码如下：

Sub创建图表()

DimchtAsChart

Setcht=Charts.Add'创建图表对象

Withcht

.SetSourceDataSource:=Sheets("成绩表").Range("B2:E7"),

PlotBy:=xlRows

'指定数据源

.ChartType=xlColumnClustered

.HasTitle=True'添加标题

.ChartTitle.Text="成绩分析图"

EndWith

EndSub

例152创建嵌入式图表1.案例说明打开本例工作簿，单击“成绩分析图表”按钮，将在当前工作表的单元格区域“G2:L15”中生成一个嵌入式图表，如图9-3所示。图9-3创建嵌入式图表2.关键技术图表工作表对象为Chart，而嵌入到工作表中的图表对象为ChartObject对象。ChartObjects集合包含指定工作表上所有的ChartObject对象的集合。每个ChartObject对象都代表一个嵌入式图表。ChartObject对象充当Chart对象的容器。ChartObject对象的属性和方法控制工作表上嵌入式图表的外观和大小。通过ChartObjects集合的Add方法，可向集合中添加嵌入式图表。其语法格式如下：

表达式.Add(Left,Top,Width,Height)

该方法的4个参数指定嵌入式图表的尺寸，分别设置左上角的坐标位置和图表的初始大小。使用ChartObjects集合的Delete方法可删除指定工作表的嵌入式图表。3.编写代码“成绩分析图表”按钮的VBA代码如下：

Sub创建嵌入式图表()

DimchtAsChartObject

OnErrorResumeNext

ActiveSheet.ChartObjects.Delete'删除工作表中已有的嵌入式图表

OnErrorGoTo0

WithRange("G2:L15")

Setcht=ActiveSheet.ChartObjects.Add(_

.Left,.Top,.Width,.Height)'创建新的嵌入式图表

EndWith

Withcht

.Name="Results"'设置嵌入式图表的名称

With.Chart

'指定数据源

.SetSourceDataSource:=Sheets("成绩表").Range("B2:E7"),PlotBy:=xlRows

.ChartType=xlColumnClustered

.SetElementmsoElementChartTitleCenteredOverlay'设置图表标题

.ChartTitle.Text="成绩分析图"

EndWith

EndWith

EndSub

以上代码首先删除当前工作表中的嵌入式图表，如果当前工作表中没有嵌入式图表，执行Delete方法时将出现错误，所以需使用错误捕捉语句获取错误。接着使用ChartObjects集合对象的Add方法添加一个嵌入式图表，最后设置图表对象的相关属性。例153转换图表类型1.案例说明打开本例工作簿，如图9-4所示，在工作表中单击“嵌入式图表转图表工作表”按钮，嵌入式图表将转换为图表工作表，在工作簿中将新增加一个工作表“成绩分析图”，如图9-5所示。同时，数据工作表中的嵌入式图表将消失，如图9-6所示。图9-4转换图表类型图9-5图表工作表图9-6数据工作表在图9-6所示的工作表中单击“图表工作表转嵌入式图表”按钮，名称为“成绩分析图”的图表工作表将被删除，其中的图表将嵌入到当前工作表中，效果如图9-4所示。2.关键技术通过Chart对象的Location方法，可改变图表的放置位置。该方法的语法格式如下：

表达式.Location(Where,Name)

两个参数的含义如下。Where：用来设置图表移动的目标位置。可设置为xlLocationAsNewSheet（将图表移动到新工作表）、xlLocationAsObject（将图表嵌入到现有工作表中）或xlLocationAutomatic（Excel控制图表位置）三个常量之一。Name：如果Where为xlLocationAsObject，则该参数为必选参数。如果Where为xlLocationAsObject，则该参数为嵌入该图表的工作表的名称。如果Where为xlLocationAsNewSheet，则该参数为新工作表的名称。3.编写代码（1）“嵌入式图表转图表工作表”按钮的VBA代码如下：

Sub嵌入式图表转换为图表工作表()

DimchtAsChartObject

OnErrorResumeNext

Setcht=ActiveSheet.ChartObjects(1)

IfchtIsNothingThenExitSub

cht.Chart.LocationxlLocationAsNewSheet,"成绩分析图"

EndSub

以上代码通过工作表的ChartObjects集合返回的是一个ChartObject对象，要改变其位置，需使用该对象的Chart属性返回一个Chart对象，通过Chart对象的Location方法才能改变图表对象的位置。（2）“图表工作表转嵌入式图表”按钮的VBA代码如下：

Sub图表工作表转为嵌入式图表()

DimchtAsChart,chtoAsChartObject

OnErrorResumeNext

Setcht=Charts("成绩分析图")

IfchtIsNothingThenExitSub

cht.LocationxlLocationAsObject,ActiveSheet.Name

Setchto=ActiveSheet.ChartObjects(1)

WithRange("G2:L15")

chto.Top=.Top

chto.Left=.Left

chto.Width=.Width

chto.Height=.Height

EndWith

EndSub

以上代码中，首先获取图表工作表的引用，再通过Location方法改变其位置。将图表工作表改为嵌入式图表后，嵌入式图表将使用默认的位置。为了不使嵌入式图表遮掩数据，程序最后修改了嵌入式图表的位置。例154删除图表1.案例说明打开本例工作簿，如图9-7所示，在当前工作表中有三个嵌入的图表，单击“删除图表”按钮，三个图表中的一个将被显示在最前面，并弹出对话框询问用户是否删除该图表，如图9-8所示。在对话框中单击“是”按钮，将删除显示在最前面的图表，单击“否”按钮，该图表不被删除。图9-7删除图表图9-8将图表显示在前面程序循环将三个图表显示在最前面，让用户决定是否删除。2.关键技术本例使用了ChartObject对象的三个方法来完成相应的功能，各方法的含义如下。BringToFront：将图表放到Z-次序前面，即将图表显示在最前面。Activate：使当前图表成为活动图表（激活图表）。Delete：删除图表。3.编写代码“删除图表”按钮的VBA代码如下：

Sub删除图表()

DimchtoAsChartObject

ForEachchtoInActiveSheet.ChartObjects

chto.BringToFront

chto.Activate

IfMsgBox("删除激活的嵌入式图表？",vbQuestion+vbYesNo,"删除图表")=vbYesThen

chto.Delete

EndIf

Next

EndSub

9.2控制图表对象创建图表后，还可以使用VBA代码控制图表，如修改图表的数据源、图表的类型，以及对图表内部的子对象进行格式化等操作。本节实例将演示这些方法。例155判断工作表的类型1.案例说明打开本例工作簿，如图9-9所示，单击“工作表类型”按钮，将显示“工作表类型”对话框，如图9-10所示。在对话框中显示了当前工作簿各工作表的类型。图9-9判断工作表类型图9-10工作表类型2.关键技术本例使用TypeName函数判断工作表的类型，该函数的语法格式如下：

TypeName(varname)

参数varname包含用户定义类型变量之外的任何变量。TypeName函数的返回值为一个字符串，该字符串可能是数据类型名称，或者是对象名称。例如，在本例中，图表工作表的返回值为“Chart”，普通工作表的返回值为“Worksheet”。提示：如果varname是一个数组，则返回的字符串可以是添加了空括号的字符串（或Variant）。例如，如果varname是一个整数数组，则TypeName返回"Integer()"。3.编写代码“工作表类型”按钮的VBA代码如下：

Sub判断工作表类型()

DimiAsInteger,str1AsString

Fori=1ToActiveWorkbook.Sheets.Count

str1=str1&vbCrLf&vbCrLf&"工作表名称："&Sheets(i).Name&vbTab&_

"工作表类型："&TypeName(Sheets(i))

Next

MsgBoxstr1,,"工作表类型"

EndSub

例156重排嵌入式图表1.案例说明打开本例工作簿，如图9-11所示，在当前工作表中有三个重叠的嵌入式图表，单击选择一个图表后，再单击“重排图表”按钮，三个图表将按选中的图表大小逐个水平排列在工作表中，如图9-12所示。图9-11重排图表图9-12图表重排后的效果2.关键技术（1）控制嵌入式图表的大小对于Chart对象，没有设置对象大小的相关属性。若要改变嵌入式图表的大小，需要获取嵌入式图表对象ChartObject。通过设置该对象的左上角坐标，以及设置其高度和宽度，可实现本例的要求。（2）ActiveChart属性Application和Window对象都提供ActiveChart属性，通过该属性可返回一个Chart对象，它代表活动图表（嵌入式图表或图表工作表）。嵌入式图表在被选中或激活时被认为是活动的。当没有图表处于活动状态时，此属性返回Nothing。在本例中，使用以下语句获得活动图表所在的ChartObject对象。

Setchto=ActiveChart.Parent

3.编写代码“重排图表”按钮的VBA代码如下：

Sub重排图表()

DimchtoAsChartObject

DimwAsLong,hAsLong,tAsLong,lAsLong

IfActiveChartIsNothingThen

MsgBox"请先单击选择一个图表作为基准！"

ExitSub

EndIf

Setchto=ActiveChart.Parent

w=chto.Width

h=chto.Height

l=chto.Left

t=chto.Top

ForEachchtoInActiveSheet.ChartObjects

chto.Left=l

chto.Top=t

chto.Width=w

chto.Height=h

l=l+w

Next

EndSub

以上代码中，首先获取当前活动图表的左上角位置和图表的大小，再对当前工作表中的嵌入式图表进行循环处理，逐一调整其大小和位置，使各图表等高等宽，并依次水平排列。例157调整图表的数据源1.案例说明打开本例工作簿，如图9-13所示，单击选中嵌入式图表，再单击“调整图表数据源”按钮，弹出“输入”对话框，如图9-14所示。在该对话框中可输入新数据源区域的地址，也可用鼠标在工作表中拖动选取一片单元格区域，该区域的地址将自动填充到“输入”对话框中。图9-13调整数据源图9-14选择数据区域输入或拖动选择单元格区域后，单击“输入”对话框中的“确定”按钮，选中图表的数据源将改变，从而更新图表显示的内容，如图9-15所示。图9-15调整数据源后的图表2.关键技术（1）InputBox方法在本书前面章节的实例中，反复使用到了Application对象的InputBox方法，该方法显示一个接收用户输入的对话框，并返回对话框中用户输入的信息。InputBox方法可设置接收数据的类型，设置参数Type为1，表示输入的是数字。在本例中，设置参数Type为8，此时输入对话框将把用户输入的字符串作为单元格区域的地址，在这种模式下，用户还可直接在工作表中拖动鼠标选取相应的单元格区域，对话框中将自动填充相应的单元格地址。（2）SetSourceData方法使用Chart对象的SetSourceData方法，可为指定的图表设置源数据区域。该方法的语法格式如下：

表达式.SetSourceData(Source,PlotBy)

两个参数的含义如下。Sourceo指定源数据区域的Range对象。PlotBy设置数据绘制方式，可为常量xlColumns（数据系列在行中）或xlRows（数据系列在列中）之一。3.编写代码“调整图表数据源”按钮的VBA代码如下：

Sub调整图表数据源()

DimmyCellAsRange

IfActiveChartIsNothingThen

MsgBox"请选择需要调整数据源的图表！"

ExitSub

EndIf

'选择需要制作图表的区域

SetmyCell=Application.InputBox(prompt:="请选择调整的数据源区域。",Type:=8)

ActiveChart.SetSourceDataSource:=myCell

EndSub

以上代码首先判断用户是否选中图表，接着弹出对话框让用户输入或选择新的数据源区域，最后使用SetSourceData方法为图表设置新的数据源。例158为图表添加阴影1.案例说明打开本例工作簿，如图9-16所示，单击工作表中的“为图表添加阴影”按钮，右侧的嵌入式图表外部和图表中的绘制区域将显示阴影，使图表具有立体感，如图9-17所示。图9-16无阴影图表图9-17有阴影图表单击“取消图表阴影”按钮，可取消图表外部及绘图区域的阴影。2.关键技术（1）获取对图表子对象的引用图表对象又包含许多子对象，通过这些子对象可访问或设置图表相关元素的格式。如本例中设置阴影就使用了多个子对象，各子对象的作用如下。ShadowFormat对象：代表形状的阴影格式，通过该对象的相应属性可设置阴影的效果。ChartFormat对象：提供对图表元素艺术字格式的访问。ChartArea对象：代表图表的图表区。PlotArea对象：代表图表的绘图区。知道以上各子对象的作用后，理解以下代码就比较容易了：

ActiveChart.ChartArea.Format.Shadow

以上代码引用了多个子对象，从左到右的引用过程如下。ActiveChart：引用当前活动图表。ActiveChart.ChartArea：通过Chart对象的ChartArea属性，获取对ChartArea对象的引用。ActiveChart.ChartArea.Format：通过ChartArea对象的Format属性，获取对ChartFormat对象的引用。ActiveChart.ChartArea.Format.Shadow：通过ChartFormat对象的Shadow属性，获取对ShadowFormat对象的引用。（2）设置阴影效果通过上面的语句获取对ShadowFormat对象的引用后，通过该对象的属性就可控制图表对象的阴影效果。本例使用了以下属性设置阴影效果。Visible属性：设置阴影是否可见。Blur属性：返回或设置指定底纹的模糊度。Transparency属性：返回或设置指定填充的透明度，取值范围为0.0（不透明）到1.0（清晰）之间。OffsetX属性：以磅为单位返回或设置指定形状的阴影的水平偏移量。正偏移值将阴影向右偏移，负偏移值将阴影向左偏移。OffsetY属性：以磅为单位返回或设置指定形状阴影的垂直偏移量。正偏移值将阴影向下偏移，负偏移值将阴影向上偏移。3.编写代码（1）“为图表添加阴影”按钮的VBA代码如下：

Sub为图表添加阴影()

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

WithActiveChart.ChartArea.Format.Shadow

.Visible=msoTrue

.Blur=15

.Transparency=0.3

.OffsetX=5

.OffsetY=5

EndWith

WithActiveChart.PlotArea.Format.Shadow

.Visible=msoTrue

.Blur=5

.Transparency=0.4

.OffsetX=2

.OffsetY=2

EndWith

EndSub

以上代码首先检查是否选中了图表，接着设置图表外部区域的阴影效果，最后设置图表中绘制区域的阴影效果。（2）“取消图表阴影”按钮的VBA代码如下：

Sub取消图表阴影()

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

ActiveChart.ChartArea.Shadow=False

ActiveChart.PlotArea.Format.Shadow.Visible=msoFalse

EndSub

例159显示数据标签1.案例说明打开本例工作簿，如图9-18所示，单击“显示标签”按钮，各柱状图上方将显示具体的数字标签，如图9-19所示。单击“隐藏标签”按钮，各柱状图上方显示的数字标签将隐藏，如图9-18所示。图9-18未显示标签图9-19显示标签2.关键技术（1）SeriesCollection集合对象该集合对象包含指定的图表或图表组中所有Series对象的集合。可用Chart对象的SeriesCollection方法返回SeriesCollection集合。（2）Series对象该对象代表图表上的系列，是SeriesCollection集合的成员。使用该对象的属性和方法可控制图表中的每个系列。如本例使用了以下方法和属性。ApplyDataLabels方法：向系列应用数据标签。Points方法：返回一个对象，该对象表示数据系列中单个数据点（Point对象）或所有数据点的集合（Points集合）。HasDataLabels属性：如果数据系列具有数据标签，则该属性值为True。（3）Point对象Point对象代表图表系列中的单个数据点。通过该对象可控制图表中数据系列的每一个数据点，如本例使用DataLabel设置数据点的标签的显示内容。3.编写代码（1）“显示标签”按钮的VBA代码如下：

Sub显示标签()

DimiAsInteger,nAsInteger,serAsSeries

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

ForEachserInActiveChart.SeriesCollection

ser.ApplyDataLabelsType:=xlDataLabelsShowValue,_

AutoText:=True,LegendKey:=False

n=ser.Points.Count

Fori=1Ton

ser.Points(i).DataLabel.Text=ser.Values(i)

Next

Next

EndSub

以上代码通过一个循环嵌套，对图表中的系列逐个进行处理。每个系列都有多个数据点，内循环完成显示数据的操作。（2）“隐藏标签”按钮的VBA代码如下：

Sub隐藏标签()

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

ForEachserInActiveChart.SeriesCollection

ser.HasDataLabels=False

Next

EndSub

例160将图表保存为图片1.案例说明打开本例工作簿，如图9-20所示，单击选中工作表中的图表，再单击左侧的“生成图片”按钮，将在当前工作表中（单元格I1）插入一个图片，该图片显示的内容与图表的内容相同。图9-20图表保存为图片2.关键技术使用ChartObjects对象的CopyPicture方法，可将选中的图表作为图片复制到剪贴板。该方法的语法格式如下：

表达式.CopyPicture(Appearance,Format)

参数Appearance用于设置图片的复制方式，可设置为以下两个常量之一。xlScreen：图片尽可能按其屏幕显示进行复制，这是默认值。xlPrinter：图片按其打印效果进行复制。参数Format设置图片的格式，可设置为以下两个常量之一。xlBitmap：位图（.bmp、.jpg、.gif）。xlPicture：绘制图片（.png、.wmf、.mix）。3.编写代码“生成图片”按钮的VBA代码如下：

Sub保存为图片()

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

ActiveChart.CopyPictureAppearance:=xlScreen,Format:=xlBitmap

ActiveWindow.Visible=False

Range("I1").Select

ActiveSheet.Paste

EndSub

例161设置图表颜色1.案例说明打开本例工作簿，如图9-21所示，单击选中工作表中的图表，再单击“设置图表颜色”按钮，图表各子对象的颜色将使用随机颜色填充，如图9-22所示。因为使用的是随机颜色，因此每次单击“设置图表颜色”按钮时，图表各子对象的填充颜色都不相同。图9-21默认颜色图9-22使用随机色2.关键技术本例通过图表的相关属性获取对各子对象的引用，然后逐个设置子对象的填充颜色。本例使用的子对象如下。ChartArea：图表区域。PlotArea：绘图区。Legend：图例。ChartTitle：图表标题。Axes(xlValue)：数值轴。Axes(xlCategory)：分类轴。SeriesCollection：序列。3.编写代码（1）“设置图表颜色”按钮的VBA代码如下：

Sub设置图表颜色()

DimiAsInteger

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

WithActiveChart

.ChartArea.Format.Fill.ForeColor.RGB=F_Color'图表区域

.PlotArea.Format.Fill.ForeColor.RGB=F_Color'绘图区

.Legend.Font.Color=F_Color'图例

.ChartTitle.Font.Color=F_Color'图表标题

.Axes(xlValue).MajorGridlines.Border.Color=F_Color'数值轴网格线

.Axes(xlValue).TickLabels.Font.Color=F_Color'数值轴刻度

.Axes(xlValue).Border.Color=F_Color'数值轴线

.Axes(xlCategory).TickLabels.Font.Color=F_Color'分类轴刻度

.Axes(xlCategory).Border.Color=F_Color'分类轴线

Fori=1To.SeriesCollection.Count

.SeriesCollection(i).Format.Fill.ForeColor.RGB=F_Color'序列

Next

EndWith

EndSub

（2）以上过程调用了自定义函数F_Color来获取一个随机颜色，该函数的VBA代码如下：

FunctionF_Color()

DimrAsInteger,gAsInteger,bAsInteger

Randomize

r=Int(255*Rnd+1)

g=Int(255*Rnd+1)

b=Int(255*Rnd+1)

F_Color=RGB(r,g,b)

EndFunction

例162按值显示颜色1.案例说明打开本例工作簿，如图9-23所示，单击选中图表，再单击“按值着色”按钮，图表将根据每个数据点的值显示颜色。图9-23按值显示颜色2.关键技术本例通过Interior对象获取每个数据点对象（Point）的对象内部，再通过Interior对象的属性ColorIndex设置每个数据点的填充颜色。3.编写代码“按值着色”按钮的VBA代码如下：

Sub按值着色()

DimSerAsSeries,iTempAsInteger

DimiAsLong,lColorAsLong

IfActiveChartIsNothingThen

MsgBox"请选择需要设置格式的图表！"

ExitSub

EndIf

ForEachSerInActiveChart.SeriesCollection

Fori=1ToSer.Points.Count

Ser.Points(i).Interior.ColorIndex=xlNone

iTemp=Ser.Values(i)

SelectCaseiTemp

CaseIs<60

lColor=RGB(iTemp*1.5,iTemp*0.5,iTemp*0.5)

CaseIs>=90

lColor=RGB(iTemp*0.5,iTemp*1.5,iTemp*0.5)

CaseElse

lColor=RGB(iTemp*0.5,iTemp*0.5,iTemp*1.5)

EndSelect

Ser.Points(i).Interior.Color=lColor

Nexti

NextSer

EndSub

以上代码根据每个序列不同数据点的值生成一个颜色值，再将该值赋值给每个数据点作为颜色值。9.3图表事件图表作为一种对象，也支持事件驱动。例如，激活图表时，就会触发Activate事件，图表接收到新的数据后，就会触发Calculate事件。对这些事件过程编写代码，即可完成事件驱动过程。图表工作表的事件与嵌入式图表的事件类似，但嵌入式图表的事件驱动需要编写一个类模块来完成。本节介绍这些事件过程的编写方法。例163激活图表工作表1.案例说明打开本例工作簿，单击图表工作表“Chart1”，将弹出如图9-24所示的对话框。如果打开工作簿就显示图表工作表“Chart1”，这时需要单击其他工作表标签转换到其他工作表，然后单击图表工作表“Chart1”，将显示如图9-24所示的对话框。2.关键技术与激活工作表相同，当激活图表工作表时将产生Activate事件，有关该事件的描述，参见工作表事件中的相关内容。图9-24激活图表工作表3.编写代码本例在图表工作表的Activate事件过程中编写以下代码：

PrivateSubChart_Activate()

Dimstr1AsString

str1=Application.UserName&"：你好！"&vbCrLf&vbCrLf

str1=str1&"