第13章　Excel常用对象

 

第13章　Excel常用对象

 

　　上一章介绍了Excel的对象模型，从Excel对象模型可以看出，Excel　2007提供了200多种对象。其实Excel最常用的对象只有几个，如Application对象、Workbook工作簿对象、Worksheet工作表对象、Range单元格区域对象，以及Chart图表对象等。本章介绍这些对象的使用方法。

 

　　13.1　Application对象

　　Application对象代表整个Excel应用程序，在Excel对象模型中处于最顶层。使用Application对象可控制应用程序范围的设置和选项。

　　13.1.1　Application对象的用途

　　Application对象主要用在以下几方面。

　　●控制整个应用程序，对Application对象的相关属性进行设置，如设置应用程序标题、工具栏和菜单栏等的状态。如下面的代码将Excel标题设置为“我的应用程序”：

　　Application.Caption　＝　“我的应用程序”

　　●返回处于活动状态的对象，如ActiveCell，ActiveSheet等。

　　●调用Excel的内部函数。

　　Excel内置了丰富的函数，充分利用这些函数，可减少代码的编写工作量，对这些函数的引用可通过Application对象进行。下面的代码将对指定单元格求和：

　　lngSum = Application.Sum(ActiveSheet.Range("A1:E10"))

　　13.1.2　设置Excel主窗口标题栏

　　使用Application对象的Caption属性，可改变Excel主窗口标题栏中显示的名称。一般将这类代码放在工作簿的Open事件中，打开工作簿即可将其设置为需要的名称。例如，以下代码将标题栏设置为“我的Excel应用程序”：

　　Private　Sub　Workbood_Open()

　　　　Application.Caption　＝　“我的Excel应用程序”

　　End　Sub

　　关闭Excel，再重新打开包含以上代码的Excel工作簿（或在VBE环境中执行以上代码），可得到如图13-1所示的标题栏名称。

　　　　　　

　　注意：Caption属性是针对整个Excel应用程序的设置。关闭包含以上代码的工作簿，Excel的标题栏仍然将显示修改后的标题名称。

　　13.1.3　显示或关闭编辑栏

　　通过以下属性可控制编辑栏、滚动条、状态栏等窗体部件的显示状态：

　　●DisplayFormulaBar属性，如果该属性值为True，则显示编辑栏；

　　●DisplayScrollBars属性，如果该属性值为True，则滚动条在所有工作簿中显示；

　　●DisplayStatusBar属性，如果该属性值为True，则显示状态栏。

　　在“Excel选项”对话框中可设置Excel的常用选项，通过VBA编写代码，也可设置这些选项。例如，以下程序将设置编辑栏、滚动条和状态栏显示与否。

　　

　　13.1.4　关闭屏幕更新

　　在默认情况下，Excel每执行一个操作就会更新一次屏幕的显示，以显示出执行的结果。如果需要用VBA代码一次执行多步操作，关闭屏幕更新可加快程序的执行速度。这样将看不到程序的执行过程，但程序的执行速度加快了。将ScreenUpdating属性设置为True，将启用屏幕更新；设置为False，将关闭屏幕更新。

　　注意：当代码结束运行后，就将ScreenUpdating属性设置为True。

　　例如，以下示例演示将屏幕更新关闭以后，系统如何加快代码的执行速度。本例将工作表Sheet1上的1～10 000行进行隔行隐藏，将程序执行时间保存在数组中。第一次，示例隐藏行时，屏幕更新是打开的；第二次时，屏幕更新是关闭的。

　　

　　

　　程序的执行结果如图13-2所示。由和程序结果可以看出，将ScreenUpdating属性设置为True时，程序执行的时间为41秒；而将ScreenUpdating属性设置为False时，程序执行的时间约为3秒。由此可以看出程序执行速度的差别为10多倍。

　　　　　　　　　　　　　

　　13.1.5　设置状态栏

　　通过StatusBar属性可返回或设置状态栏中的文字，而DisplayStatusBar属性可控制是否显示状态栏。下面的代码首先保存DisplayStatusBar属性的当前状态，然后将该属性设置为True以显示状态栏，最后在状态栏中显示出提示文字。

　　

　　执行以上代码，状态栏的显示如图13-3所示。　

　　　　　　　　　

　　一般在执行完相应的操作后，可使用以下代码将状态栏还原：

　　Application.StatusBar　＝　False

　　Application.DisplayStatusBar　＝　oldStatusBar

　　13.1.6　查看模板文件的路径

　　通过TemplatePath属性可返回Excel模板文件存储的路径。例如以下的代码可返回Excel模板文件保存的位置。

　　Sub　模板位置（）

　　　　MsgBox　Application.TemplatesPath

　　End　Sub

　　过程执行的结果如图13-4所示。

　　

　　13.1.7　最大化Excel窗口

　　通过WindowState属性可查询Excel窗口的状态，也可设置其状态。例如以下代码可将Excel窗口最大化：

　　Sub　最大化（）

　　　　Application.WindowState　＝　xlMaximized

　　End　Sub

　　WindowState属性可设置为以下三个值之一：

　　●xlMaximized,最大化窗口；

　　●xlMinimized,最小化窗口；

　　●xlNormal，正常窗口。

　　13.1.8　获取对象的引用

　　许多Application对象的属性用来返回其他的对象，主要有以下几种。

　　1.　ActiveCell属性

　　返回一个Range对象，它代表活动窗口（最上方的窗口）或指定窗口中的活动单元格。如果窗口中没有显示工作表，此属性无效。

　　注意：请仔细区分活动单元格和选定区域：活动单元格为选定区域内部的一个单元格；而选定区域可以包含多个单元格，但只有一个单元格为活动单元格。

　　下列表达式都返回活动单元格，并且都是等效的。

　　ActiveCell

　　Application.ActiveCell

　　ActiveWindow.ActiveCell

　　Application.ActiveWindow.ActiveCell

　　2.　ActiveChart属性

　　返回一个Chart对象，它代表活动图表（嵌入式图表或图表工作表）。嵌入式图表在被选中或激活时被认为是活动的。当图表的处于活动状态时，此属性返回“Nothing”。如果不指定对象识别符，此属性返回活动工作簿中的活动图表。

　　3.　ActiveSheet属性

　　返回一个对象，它代表活动工作簿中或指定的窗口或工作簿中的活动工作表（最上面的工作表）。如果没有活动的工作表，则返回“Nothing”。如果不指定对象识别符，则此属性返回活动工作簿中的活动工作表。如果某个工作簿出现在若干个窗口，那么该工作簿的ActiveSheet属性在不同窗口中可能不同。

　　4.ActiveWindow属性

　　返回一个Window对象，该对象表示活动窗口（顶部窗口）。如果没有打开的窗口，则返回“Nothing”。

　　5.ActivWorkbook属性

　　返回一个Workbook对象，该对象表示活动窗口（顶部窗口）中的工作簿。如果没能打开的窗口，以及“信息”窗口或“剪贴板”窗口为活动窗口，则返回“Nothing”。

　　6.　Cells属性

　　返回一个Range对象，它代表活动工作表中的所有列。如果活动文档不是工作表，则此属性无效。因为Item属性是Range对象的默认属性，所以可以在Cells关键字后面紧接着指定行和列的索引。在不使用对象识别符的情况下，使用此属性将返回一个Range对象，它代表活动工作表中所有的单元格。

　　7.　Selection属性

　　Selection属性为Application对象返回活动窗口中选定的对象。返回的对象类型取决于当前所选内容（例如，如果选择了单元格，此属性将返回Range对象）；如果未选择任何内容，Selection属性将返回“Nothing”。

　　8.　Sheets属性

　　返回一个Sheets集合，它代表活动工作簿中所有的工作表。

　　9.　Workbooks属性

　　返回一个Workbooks集合，该集合表示所有打开的工作簿。

　　10.　WorksheetFunction属性

　　可用作从Visual　Basic中调用的Excel工作表函数的容器。

　　例如，下列代码显示给区域“A1：C10”应用Min工作表函数的结果。

　　Set myRange = Worksheets("Sheet1"),Range("A1:C10")

　　answer = Application.WorksheetFunction.Min(myRange)

　　MsgBox answer

　　程序中也可省略WorksheetFunction，直接写为Application.Min的形式。

　　13.1.9　重新计算

　　使用Calculate方法可计算所有打开的工作簿、工作簿的某个特定工作表或工作表指定区域中的单元格，例如以下代码：

       

　　注意：使用CalculateFull方法将强制对所有打开工作簿中的数据进行完整计算。

　　

　　13.1.10　将R1C1转换为A1样式

　　使用ConvertFormula方法可在A1和R1C1引用样式之间转换，在相对和绝对引用之间转换公式中的单元格引用，或者同时进行这两种转换。其语法格式如下：

　　Application.ConvertFormula（公式字符串，原引用样式，返回引用样式，转换类型，关联单元格）

　　下面的代码把包含R1C1样式引用SUM公式转换为包含A1样式引用的等价公式，然后显示转换结果。

　　

　　程序执行结果如图13-5所示。

　　　　　　　　

　　13.1.11　快速跳转

　　使用Goto方法可选定任意工作簿中的任意区域，并且如果该工作簿未处于活动状态，就激活该工作簿。通过该方法的Scroll属性可让窗口滚动到目标位置。例如以下代码将选择“Sheet2”工作表中的“A1：A10”区域，并将该区域滚动到当前窗口中显示。

　　Sub　快速跳转（）

　　　　Application.Goto　　　Reference:=Worksheets("Sheet2").Range("A1：A10")、Scroll:=True

　　End　Sub

　

　　13.1.12　定时执行过程

　　使用OnTime方法可安排一个过程在将来的特定时间运行（即可以是具体指定的某个时间，也可以是指定的一段时间之后）。如设置20秒后运行过程“Test1”可使用以下代码：

　　Application.OnTime　Now　+　TimeValue("00:00:20")、"Test1"

　　早上8点整运行过程“Test2”：

　　Application.OnTime　TimeValue("08:00:00")、"Test2"

　　要撤销运行OnTime设置的过程，需要将Schedule参数设置为Flase。如撤销前一个表达式对OnTime的设置的代码如下：

　　Application.OnTime　EarliestTime:＝TimeValue("08:00:00")、Procedure:="Test2"、Schedule:=False

　　技巧：如果不一个参数在调用方法库中预先定义了其名称，那么这个参数称为命名参数。对命名参数，不必拘泥于语法所规定顺序来提供值，而可按任何顺序用命名参数分配值。例如，OnTime方法接受4个参数，其格式如下：

　　OnTime(EarliestTime,Procedure,LatestTime,Schedule)

　　在对命名参数赋值时，可使用前面的语句取消OnTime设置的过程，也可以使用以下语句完成同样的功能（将Schedule参数放在最前面）：

　　Application.OnTimeSchedule:=False,EarliestTime:=TimeValue("08:00:00:),Procedure:="Test2"

 

　　13.1.13　合并区域

　　使用Union方法可返回两个或多个区域的合并区域。其语法格式如下：

　　表达式.Union(Argl,Arg2,……，Arg30)

　　使用该方法时，最少需要两个Range对象区域作为参数，最多可以合并30个Range对象区域。例如：

　　Worksheets("Sheet1").Activate

　　Set　unRange = Application.Union(Range("A1:B5"),Range(D1:B5"))

　　unRange.Formula　＝　"=RAND()"

　　以上代码首先将单元格区域“A1：B5”和“D1:E5”合并为1个Range对象，再设置该区域对象中各单元格的公式为一个随机函数。执行该部分代码后，将在这20个单元格区域（单元格区域“A1：B5”和“D1:E5”共20个单元格）填充随机数。

 

　　13.2　Workbook对象

　　Workbook工作簿对象位于Application对象的下一层次。一个工作簿对象就是一个Excel文件。工作表对象Worksheet，单元格区域对象Range和图表对象Chart等都位于工作簿对象中。多个Workbook对象组成Workbooks集合。

　　13.2.1　新建工作簿

　　Workbooks集合包含了Excel应用程序中当前打开的所有WorkBook对象。可以使用Workbooks集合创建新的工作簿，或关闭工作簿等。

　　在VBA中创新的工作簿，可以使用Workbooks集合对象的Add方法。下面的代码用于创建一个新工作簿。Excel自动将该工作簿命名为“BookN”，其中“N”是下一个可用的数字。新工作簿将成为活动工作簿。

　　Sub　AddOne

　　　Workbooks.Add

　　End　Sub

　　创建新工作簿更好的方法是将其分配给一个对象变量，在程序中可通过该对象变量对工作簿进行设置。使用对象变量可以很容易地控制新工作簿。例如，以下代码就可完成工作簿的创建，并设置工作簿的相关属性。

 　　Sub　AddNew（）

　　　　n=Workbooks.Count

              Set NewBook = Workbooks.Add

 　　　With NewBook

 　　　　　.Title = "新工作簿" & n

　　　　　.SaveAs Filename:="新工作簿" & n & ".xlsx"

　　　End With

　　End　Sub

 

　　13.2.2　打开工作簿

　　用Open方法打开一个工作簿时，该工作簿将成为Workbooks集合的成员。下述代码用于打开“D:\Excelvba2007\实例＼第13章＼常用对象.xlsm”工作簿：

　　Sub OpenUp（）

　　　　Workbooks.Open("D:\Excelvba2007\实例＼第13章＼常用对象.xlsm")

　　End　Sub

　　在更多的时候，打开工作簿时需要查找文件所在位置，这时可通过“打开”对话框来进行查找，引用“打开”对话框的语法格式如下：

　　Application.GetOpenFilename(FileFilter,FilterIndex,Title,ButtonText,MultiSelect)

　　使用GetOpenFilename方法可返回选定的文件名或用户输入的名称，返回的名称可能包含路径说明。如果用户取消了对话框，则该值为False。使用实例如下：

　　

　　另外，Workbooks集合还提供了OpenDatabase，OpenText和OpenXML方法，分别用来打开数据库、文本文件和XML数据文件。

　　对于打开的工作簿，可使用Close方法关闭，且不退出Excel程序。如果某个打开的工作秒表改动，Excel将显示底部是否保存更改的对话框和相应提示。

 

　　13.2.3　查看文档属性

　　在Excel中，要查看和修改文档的属性可按以下步骤操作。

　　步骤1　单击“Office按钮”打开下拉菜单。

　　步骤2　选择“准备”│“属性”命令，在Excel的功能区下方打开如图13-6所示的“文档属性”面板，供用户查看和修改。

　　　　　　　　　　　　

　　步骤3　单击左上角“文档属性”标签，打开下拉菜单选择“高级属性”，打开如图13-7所示的属性窗口，在其“自定义”选项卡中可定义各种属性值。

　　以上是在Excel操作环境下操作的结果。在Excel程序开发中，也可使用VBA代码控制并设置文档的属性值。使用Workbook对象的BuiltinDoc

umentProperties属性返回一个DocunentProperties集合，该集合表示指定工作母虎所有内置文档属性。

　　　　　　　　　　　　　　

　　例如，以下代码在BuiltinDocumentProperties属性的DocumentProperties集合中逐个读出属性，并将其属性名称和属性值填写到“Sheet1”工作表的第A，B两列中。

　　程序运行结果如图13-8所示。

　　　　　　

 

　　13.2.4　处理工作簿文件名

　　使用Workbook对象的FullName属性可返回工作簿的名称（用字符串表示），包括其磁盘路径。例如，以下代码将显示当前活动工作簿的全名，挂靠该行代码有可能显示如图13-9所示的对话框。

　　如果只需要工作簿的文件名称，而不需要磁盘路径，可使用Workbook对象的Name属性。

　　

　　13.2.5　处理命名单元格区域

　　在Excel中，可使用行号列标来引用单元格（如A1），也可对单元格区域进行命名，然后在公式或VBA代码中使用名称来引用相关单元格。

　　单元格区域的名称定义保存在Name对象中。名称可以是内置名称（如“Database”，“Print_Area”和“Auto_Open”）或自定义名称。

　　Name对象是Application，Workbook和Worksheet对象的Names集合的成员。使用Names（index)(其中index是名称索引号或定义名称）可返回一个Name对象。

　　注意：这时的Name对象不是Workbook对象的Name属性。

　　可用Add方法创建名称并将其添加到集合中。下面的语句创建一个新名称，指向工作表Sheet1上单元格区域A1：C20。

　　Name.Add　Name:="test"，RefersTo:="=sheet1!$a$1:$c$20"

　　RefersTo参数必须以A1样式表示法指定，包括必要时使用的美元符号（＄）。例如，如果在工作表Sheet1上选定了单元格A10:B10，然后又将RefersTo参数指定为“＝Sheet1!A1:B1”而定义了一个名称，那么该名称实际上指向单元格区域A10:B10（因为指定的是相对引用）。若要指定绝对引用，应当为“＝Sheet1!$A$1:$B$1"。

　　例如，在一个“库存管理系统”中，“商品信息”表中的数据不断变化，如果需要对商品信息区域进行命名，供其他表格使用，可使用以下代码进行定义：

　　

　　对于已定义Name的名称，也可通过VBA代码进行修改。例如，在工作簿中已经定义了一个Name，名称为“NameTest1”，在“名称管理器”中查看结果如图13-10所示。

　　执行以上过程后，再在“公式”选项卡的“定义的名称”组中单击“名称管理器”按钮，打开“名称管理器”对话框，可看到改名后的结果如图13-11所示。

　　　　　　　　　　

　　13.2.6　设置工作簿密码

　　为了安全起见，有时需要为工作簿设置打开权限密码。使用Workbook对象的Passwore属性可获取或设置该密码。例如，以下代码将提示用户为工作簿设置一个密码，然后保存并退出。

　　Sub　设置密码（）

　　　　ActiveWorkbook.Passwore　＝　InputBox("输入密码：")

　　　　ActiveWorkbook.Close

　　End　Sub

　　执行以上代码后，将关闭当前Excel工作簿。下次打开此工作簿时，将弹出如图13-12所示的“密码”对话框，要求用户输入正确的密码才能打开工作簿。

　　要取消工作簿的密码，可设置其Password属性为空字符串，代码如下：

　　ActiveWorkbook.Password　＝　""

也可通过单击“Office”按钮打开下拉菜单，从下拉菜单中选择“准备”│“加密文档”命令，打开如图13-13所示对话框，然后删除其中的“密码”字符即可。

　　　　　　　　　　　　　　　　

　　13.2.7　更名保存工作簿

　　保存工作簿有两种方式：一种是保存修改到原工作簿中；另一种是保存工作母虎另一个副本。使用Same方法可保存指定工作簿所做的更改。以下代码用于保存所有打开的工作簿，然后关闭Excel。

　　For　Each　ｗ　In　Application.Workbooks

　　　　ｗ．Save

　　Next　ｗ

　　Application.Quit

　　使用SaveAs方法可以将工作簿换名保存。例如，以下代码用于新建一个工作簿，提示用户输入文件名，然后保存该工作簿。

　　Set NewBook = Workbooks.Add

        Do

              fName = Application.GetSaveAsFilename

　　Loop Until fName <> False

　　NewBook.SaveAs Filename:=fName

　　13.2.8　关闭前自动保存工作簿

　　关闭Excel时，如果对工作簿进行了修改且没有存盘，系统将打开提示对话框，提醒用户保存修改。在实际的VBA程序中，可能需要强制保存（即在退出时不询问用户是否存盘，而是直接保存修改结果）。

　　这时可在Workbook对象的BeforeClose事件中编写代码。在关闭工作簿之前，将先产生此事件。如果该工作簿已经更改过，则本事件在底部用户是否保存更改之前产生。例如，下面的代码将BeforClose事件的响应设置为保存工作母虎任何更改。

　　Private Sub workbook_BeforeClose(Cancel as Boolean)

             If Me.Saved = False Then Me.Save

　　End Sub

　　13.3　Worksheet对象

　　Worksheet对象代表Excel的工作表。通过Worksheet对象，可以在程序中完成各种针对工作表的操作，如设置单元格格式、插入工作及给工作表取名等。多个Worksheet对象组成的Worksheets集合。

　　13.3.1　新建工作表

　　工作表对象（Worksheet）属于Worksheets集合中的成员，每个Worksheet对象都代表一个工作表。Excel中还提供了一个Sheets集合，该集合中的每个成员都是Worksheet对象或Chart对象，其属性和方法都相同。

　　在Worksheets集合中，除了一般集合对象具有的属性外，还有一个Visible属性，通过该属性可控制集合中的Worksheet对象是否可见。通过Worksheet集合的方法，可以对工作进行控制，下面介绍其中常用的方法。

　　1.　Add方法

　　新建工作表、图表或宏表。新建的工作表将成为活动工作表，其语法格式如下：

　　Worksheets.Add(Before,After,Count,Type)

　　其中各参数的意义为：

　　●Before,指定工作表的对象，新建的工作表将置于此工作表之前。

　　●After，指定工作表的对象，新建的工作表将置于此工作表之后。

　　●Count，设置要添加的工作表数，默认值为1。

　　●Type，指定工作表类型。工作表类型可以为xlWorksheet,xlChart,xlExcel4MacroSheet或xlExcel4IntlMacroSheet（默认值为xlWorksheet)。如果同时省略Before和After，则新工作表插入到活动工作表之前。例如，执行以下代码，在工作簿中新增两个工作表。

　　Sub　新建工作表（）

　　　　Worksheets.Add　Count:=2

　　End　Sub

　　2.　Copy方法

　　将工作表复制到工作簿的另一位置。其语法格式如下：

　　Worksheets.Copy(Before,After)

　　若不指定Before和After，则Excel将新建一个工作簿，其中包含复制的工作表。

　　3.Delete方法

　　删除集合中的指定对象——工作表（Worksheet)对象。

　　4.　Move方法

　　将工作表移到工作簿中的其他位置，代码如下：

　　Worksheets.Move(Before,After)

　　如果既不指定Before也不指定After，那么Excel将新建一个工作簿，其中包含所移动的工作表。例如，下面的代码将当前活动工作簿的Sheet1移到Sheet2之后。

　　Worksheets("Sheet1").Move　after:=Worksheets("Sheet2")

　　13.3.2　管理批注

　　在“审阅”选项卡的“批注”组中，单击“新建批注”按钮，可为当前单元格插入批注。在Excel环境下，可通过该组中的相关按钮处理批注，也可以通过VBA代码对批注进行管理。

　　在VBA中，将每一个批注作为一个Comment对象来处理。每个工作表的Comment对象组成一个Comments集合，如果工作表中没有批注，这个集合就为空。

　　1.　添加批注

　　使用AddComment方法可在区域内添加批注。以下代码表示在第一张工作表的单元格E5中添加批注。

　　Sub　添加批注（）

　　　　With　Worksheets(1).Range("e").AddComment

　　　　　　.Visible　＝　False

　　　　　　．Text　＂批注日期：＂　＆　Date

　　　　End　With

　　End　Sub

　　Worksheets(1)表示当前工作簿中排在第一个位置的工作表，并不一定是名称为“Sheet1”的表，如果要引用“Sheet1”表，则应使用

Worksheets("Sheet1")的方式来引用。

　　Text是Comment对象的方法，所以不使用等号设置其批注值。

　　注意：如果单元格中已经添加了批注，使用AddComment方法再插入批注时将产生错误。

　　2.　查看批注数量

　　使用Comments集合对象的Count属性可查看指定工作表的批注数量。例如以下代码将显示当前工作簿的第一个工作表中的批注数量：

　　MsgBox　Worksheets(1).Comments(1).Count

　　3.　查看批注

　　工作表中的批注全部Comments集合中，所以可以用For Each循环将其逐个显示出来。例如，以下代码显示第一个工作表中各批注的作者及批注的内容：

　　Sub 查看批注（）

　　　Dim cm As Comment

　　　Dim i As Integer，j As Integer

　　　ｉ＝Worksheets(1).Comments.Count

　　　For　Each　cm In Worksheets(1).Comments

　　　　　ｊ＝　ｊ+1

　　　　　MsgBox "第" & j & "条/共" & i & "条批注" & vbCrLf & "作者：" & cm.Author　＆　"批注内容：" & cm.Text

            Next

　　End Sub

　　执行以上代码之前，先在第一个工作表中插入几个批注。程序执行结果如图13-14所示。在Excel显示的提示对话框中单击“确定”按钮，可显示下一个批注。

　　　　　　　

　　13.3.3　设置工作表滚动区域

　　与以往版本相比，Excel　2007工作表非常庞大，为1 048 576行乘以16 384列，在屏幕上一次显示不完所有单元格，可由用户托动水平和垂直滚动条来显示屏幕以外的单元格。在程序中，可通过对工作表或窗口的相关属性进行设置来控制显示的单元格位置

Excel 20007主要通过Scroll Area属性来设置窗口的滚动区域。

　　使用工作表的ScrollArea属性可设置工作表中允许滚动的区域，用户不能选定滚动区域之外的单元格。例如，以下代码设置用户操作的区域“A1：C20”：

　　Sub 设置滚动区域（）

 　　　Worksheets(1).ScrollArea = "A1:C20"

　　End　Sub

　　执行以上代码后，用户将不能再选择“A1：C20”区域以外的单元格了。可将ScrollArea属性设置为空字符串（""）,以允许选定整张工作表内所有单元格。

　　13.3.4　在工作表中绘制图形

　　在Excel环境中，可在工作表中插入各种剪贴画、形状及SmartArt图形。通过VBA可更精确地在工作表中绘制图形，也可控制工作表中的每一个图形。

　　Shape对象代表工作表绘图层中的对象，例如自选图形、任意多边形、OLE对象或图片等。工作表中的多个Shape对象构成Shapes集合对象。通过Shapes集合对象中的方法可向工作表中添加线形标注、图表、连接符、图片、艺术字及各种图形等形状。例如，使用AddPolyline方法可创建一个不封闭的连续线段或一个封闭的多边形。使用该方法之前，需首先定义一个包含多边形各点坐标的二维数组，然后使用该方法即可绘制出需要的多边形。

 

　　对于工作表中的图形，还可对其进行编辑。例如，以下代码对前面生成的多边形进行编辑，首先修改其填充色为红色，再将多边形进行垂直翻转，结果如图13-16所示。

　　　　　　　　　　　　　

　　Sub 修改图形（）

　　　　With Worksheets(1).Shapes(1)

　　　　．Fill.ForeColor.RGB = RGB（255，0，0）

　　　　．Flip msoFlipVertical

　　　End　With

　　End　Sub

　　13.3.5　隐藏工作表

　　在很多情况下，使用Excel构台应用程序都需要授权才能使用。对用户的授权一般采用用户名加密码的方式来进行验证。在用户还没有进行登录验证时，打开的系统界面将只显示一个工作表，其他工作表都处于隐藏状态。这样可保护第三数据使其不被外泄。

　　当授权用户操作结束后，在退出系统之前，应该将工作表隐藏起来。在Workbook对象的BeforClose事件中编写如下代码即可完成该功能：

　　Private　Sub　Workbook＿BeforeClose(Cancel　As　Boolean)

　　　For　ｉ＝1　To　Sheets.Count　　　　　　　　　　　　　　　　＇循环隐藏各工作表

　　　　　If　Sheets(ｉ）．Name＜＞＂登录＂Then　Sheets(ｉ）．Visible　＝　False

　　　Next

　　End　Sub

　　13.3.6　在工作表中校验数据

　　在Excel工作表中输入数据时，有时希望将某些列的值限定在一定的范围内，这时可通过Worksheet对象的Change事件对单元格中录入的数据进行校验。例如，以下代码将检查工作表的第3列，如果输入的内容不是“男”或“女”，则将显示错误伯信息。

　　13.3.7　自动生成下拉数据

　　在Excel工作表中录入数据，可以使用上面介绍的方法对录入的数据进行校验，如果不符合要求就显示错误提示信息，让用户重新录入。

　　如果某列数据有多个选项，那么使用这种方法时，其判断代码将会变得很长。另外，用户录入数据时也不知道是否正确。这时可以使用下拉列表的方式，将多个选项列出来，让用户直接选择，这样可提高数据录入的效率，也不会产生错误的数据。

　　下面用一个实例来介绍这种方法的使用步骤。该例制作一个员工花名册录入表，对于表中的职务一项，则显示一个列表来让用户选择。具体制作步骤如下。

　　步骤1　启动Excel，将新工作簿保存为“员工花名册．xlsm”。

　　步骤2　在第一个表中制作表头，并设置名称为“员工花名册”，如图13-17所示。

　　步骤3　将另一个工作表名为“职务”，并输入如图13-18所示的数据。

　　

　　步骤4　按快捷键“Alt+F11”进入VBE环境，在“工程资源管理器”中双击“Sheets（员工花名册）”，打开代码窗口，输入以下代码，如图13-19所示。

 　　　　　

　　　　　

　　　　　

　　本段程序的功能是当选定区域发生改变时，对新选定的区域进行格式的设置。该程序的关键之处在于当工作表上的选定区域发生改变时（用鼠标单击某个单元格或移动光标键改变选择的单元格），将产生SelectionChange事件（即执行本段程序）。

　　程序首先从“职务”表中逐行读取数据，并将其串在一起放在一个字符串中，代后面的代码使用。

　　该程序中的Target表示新选定的区域，Target.Column为选定区域的列序号。

　　当选定区域为第1列（“花名册”工作表第1列为“编号”）时，通过语句：

　　Target.NumberFormatLocal　＝　＂＠＂

　　将该列设为文本格式。如果不设置为文本格式，在输入长度大于11位的数据时，系统默认为常规格式，将用科学计数法表示为数值。

　　当选定区域为第3列（“性别”）时，通过语句：

　　Target.Validation.Add　xlValidateList, Formula1:="男，女"

设置该单元格为一个校验列表，列表的选项为“男”或“女”。当每次将录入焦点移到该列时，都将增加这个校验列表，这将引起程序出错，所以在增加校验列表之前，先执行一个删除校验列表的语句：

　　Target.Validation.Delete

　　当选定区域为第4列（“职务”）时，设置该单元格的校验列表，列表的选项为一个字符串Zw。字符串Zw的值在本段程序开始处已准备好。

　　13.4　Range对象

　　通过以下属性可以获取一个Range对象，然后使用Range对象的属性和方法对单元格内容、格式等进行控制。

　　1.Range属性

　　工作表（Worksheet）对象或Range对象的Range属性返回一个Range对象，它代表一个单元格或单元格区域。常见的表示方法有：

　　　

　　

　　其中MyRange,YourRange,HIsRange为单元格区域的名称。

　　2.　Cells属性

　　Worksheet对象或Range对象的Cells属性也可返回一个Range对象。常见的表示方法有：

　　

　　使用Cells属性可以方便地使用变量来指定行列，下列代码用于在Sheet1工作表上创建行号和列标：

　　

 

　　程序运行结果如图13-20所示。

　　　

　　3.　Offset属性

　　该属性返回Range对象，它代表位于指定单元格区域的一定的偏移量位置上的区域。使用格式为：

　　Offset(RowOffset,ColumnOffset)

其中RowOffset为行偏移量，而ColumnOffset为列偏移量。例如，下列代码将激活Sheet1工作表活动单元格向右偏移三列、向下偏移三行处的单元格。

　　Worksheets("Sheet1").Activate

　　ActiveCell.Offset(rowOffset:＝3，columnoffset:=3).Activate

　　4.Columns属性

　　返回一个Range对象，它代表指定区域的列。此属性在应用于一个多重选定区域的Range对象时，只会从该区域的第一个子区域中返回列。例如，如果Range对象有两个子区域A1:B2和C3:D4，那么Selection.Columns.Count的返回值是2，而不是4。

　　5.　Rows属性

　　返回一个Range对象，它代表指定单元格区域中的行。例如，以下代码将删除表格中的第5行：

　　Worksheets（＂Sheet1").Rows(5).Delete

　　6.　CurrentRegion属性

　　返回一个Range对象，该对象表示当前区域。当前区域是以空行与空列的组合为边界的区域。例如，以下代码将选定工作表Sheet1上的当前区域：

　　Worksheets("Sheet1").Activate

　　ActiveCell.CurrentRegion.Select

　　7.End属性

　　返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按组合键＜End+↑＞，＜End+↓＞,<End+←>或<End

+→>。其格式为：

　　Range.End(Direction)

其中Direction指定移动的方向，可为４个值：xlDown,xlToLdft,xcToRight,xlUp分别表示向下、向左、向右、向上4个方向。

　　下面列举部分示例代码：

 

　　8.EntireColumn属性

　　返回一个Range对象，该对象包含指定区域的整列（或多列）。

　　9.　’EntireRow属性

　　返回一个Range对象，该对象包含指定区域的整行（或多行）。

　　10.　Union方法

　　使用Union（range1,range2……）可返回多块区域，即该区域由两个或多个连续的单元格区域组成。下面的代码创建由单元格区域A1:B2和C3:D4组合定义的对象，然后选定该定义区域。

　　11.　ActiveCell属性

　　ActiveCell属性返回一个Range对象，代表活动窗口（最上方的窗口）或指定窗口中的活动单元格。若窗口中没有显示工作表，此属性无效。

　　活动单元格为选定区域内部的一个单元格。而选定区域可以包含多个单元格，但只有一个单元格为活动单元格。

　　下列表达式都返回活动单元格，并且都是等效的。

　　13.4.2　设置边框线

　　Excel单元格区域具有4个分立的边框：左边框、右边框、顶部边框和底部边框。VBA通过Border对象来控制边框，各边框组成Borders集合。这4个边框既可单独返回，也可作为一个组同时返回。以下代码将第一个工作表上单元格区域“A1:C3”添加双边框。

　　Worksheets(1).Range("A1:C3").Borders.LineStyle　＝　　xlDouble

　　以上代码同时设置了区域内每个单元格上下左右4个边框线，如图13-21所示。

　　　　　　　　　　　　　　　　

　　如果要分别控制边框线，可使用Borders(index)的方式来设置，index为以下常量之一：

　　▲xlDiagonalDown,区域中每个单元格的左上角至右下角的边框；

　　▲xlDiagonalUp，区域中每个单元格的左下角至右上角的边框；

　　▲xlEdgeBottom,区域底部的边框；

　　▲xlEdgeLeft，区域左边的边框；

　　▲xlEdgeRight，区域右边的边框；

　　▲xlEdgeTop，区域顶部的边框；

　　▲xlInsideHorizontal，区域中所有单元格的水平边框（区域以外的边框除外）；

　　▲xlInsideVertical，区域中所有单元格的垂直边框（区域以外的边框除外）。

　　以下代码设置单元格区域“A1:C3”的底部边框的颜色为“红色”。

　　

　　边框线的线型（LineStyle）可以设置为以下几种之一：

　　●xlContinuous，实线；

　　●xlDash，虚线；

　　●xlDashDot，点划相间线；

　　●xlDashDotDot，划线后跟两个点；

　　●xlDot，点式线；

　　●xlDouble，双线；

　　●xlLineStyleNone，无线条；

　　●xlSlantDashDot，倾斜的划线。

　　13.4.3　设置单元格部分字符格式

　　如果要设置单元格中部分字符的格式，可以使用Range对象的Characters属性，该属性返回Characters对象。使用Characters对象可修改包含在全文本字符串中的任意字符序列。

　　仅当需要更改对象中文本的一部分而不影响其余部分时，才有必要使用Characters对象。要同时更改所有文本，通常可以对该对象直接应用某一适当的方法或属性。例如，以下代码将单元格A5的内容设置为斜体：

　　Worksheets("Sheet1").Range("A5").Font.Italic　＝　True

　　使用Characters对象可为文本字符串内的字符设置格式。其格式如下：

　　Range.Chracters(Start,Length)

其中参数的含义如下：

　　★Start，设置要返回的第一个字符的位置。如果此参数是1或被省略，则此属性返回一个以第一个字符为开头的字符区域。

　　★Length，设置要返回的字符数。如果省略此参数，则此属性返回字符串的后半部分（Start字符之后的所有字符）。

　　下面的代码向单元格B1中添加文本，并将第二个单词设置为加粗：

　　With　Worksheets（"Sheet1").Range("B1")

 　　　　．Value = "New Title"

　　　　．Characters(5,5).Font.Bold = True

 

　　13.4.4　操作当前区域

　　当前区域是指以空行与空列的组合为边界的区域。使用CurrentRegion属性可返回指定单元格所在的当前区域。该属性对于很多操作来说是很有用的，能自动将选定区域扩展到包含整个当前区域，例如以下代码：

　　ActiveCell.CurrentRegion.Rows.Count

　　ActiveCell.CurrentRegion.Columns.Count

将获得与活动单元格连续的单元格区域的行数和列数。

　　假设在Sheet1中有一个包含标题行的表，那么以下代码仅选定该表，但不选定标题行。执行代码之前，活动单元格必须处于该表中。

　　Set tbl = ActiveCell.CurrentRegion

　　tbl.Offset(1,0).Resize(tbl.Rows.Count - 1，tbl.Columns.Count).Select

　　13.4.5　修改列宽

　　在Excel中，通过拖动列标即可方便直观地调整列的宽度。在VBA中通过ColumnWidth属性也可获取或设置指定区域中所有列的列宽。

　　一个列宽单位等于“常规”样式中一个字符的宽度。对于比例字体，则使用字符0（零）的宽度。例如，以下代码使工作表Sheet1上B列的列宽加倍。

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

　　　　　．ColumnWidth　＝　ColumnWidth*2

　　End　With

　　13.4.6　定义条件格式

　　Excel中定义的条件格式为FormatCondition对象，多个条件格式组成FormatConditions集合。通过Range对象的FormatConditions属性可获取FormatConditions集合的引用。例如，以下代码为“E1:E10”单元格区域添加条件格式，其条件是在单元格区域“E1:E10”中，如果某个单元格的值大于“A1”单元格的值，则该单元格的边框线为黄色的细实线，且字体为红色的粗体字。

　　使用FormatConditions集合的Add方法添加新的条件格式的语法格式如下。

　　表达式．Add（Tybe，Operator, Formula1,Formula2)

其中，参数Type指定条件格式是基于单元格值还是基于表达式的，可为以下值之一：

　　§x.AboveAverageCondition，高于平均值条件；

　　§xlBlanksCondition，空值条件；

　　§xlCellValue，单元格值；

　　§xlColorScale，色阶；　　

　　§xlCompareColumns，比较列；

　　§xlDatabar，数据条；

　　§xlErrorsCondition，错误条件；

　　§xlExpression，表达式；

　　§xlIconSet，图标集；

　　§xlNoBlandsCondition，无空值条件；

　　§xlNoErrorsCondition，无错误条件；

　　§xlTextString，文本字符串；

　　§xlTimePeriod，时间段；

　　§xlTop10，前10个值；

        §xlUniqueValues，唯一值。

　　参数Operator是条件格式运算符，可为以下常量之一：

　　◎xlBetween,介于。只在提供了两个公式的情况下才能使用。

　　◎xlEqual，等于。

　　◎xlGreater，大于。

　　◎xlGreaterEqual，大于或等于。

　　◎xlLess，小于。

　　◎xlLessEqual，小于或等于。

　　◎xlNotBetween，不介于。只在提供了两个公式的情况下才能使用。

　　◎xlNotEqual，不等于。

　　13.4.7　设置单元格

　　使用NumberFormat属性可为单元格区域设置格式代码。格式代码与如图13-22所示的“设置单元格格式”对话框中的“格式代码”选项是同一个字符串。

 

　　13.5　Chart对象

　　在Excel中进行数据分析时，使用图表可为用户提供直观的分析结果。Excel提供上百各图表类型，用户几乎可以控制图表的各个方面。

　　13.5.1　图表分类

　　根据在工作簿中位置的不同，图表可分为图表工作表和嵌入工图表两种。

　　在VBA中，用Chart对象表示每个图表工作表，所有图表工作表组成Charts集合。使用Charts(index)可以返回单个Chart对象，其中index为图表的索引号或名称。

　　Sheets集合对象包含工作簿中所有的工作表（工作表和图表工作表）。使用Sheets(index)可以返回单个工作表或图表工作表。

　　嵌入到工作表的图表包含在ChartObject对象中，ChartObject对象的属性和方法控制工作表上嵌入式图表的外观和大小。ChartObject对象是ChartObjects集合的成员。ChartObjects集合包含单一工作表上的所有嵌入式图表。

　　使用ChartObjects(index)可以返回单个ChartObject对象，其中index是嵌入式图表的索引号或名称。

　　13.5.2　添加图表工作表

　　使用Charts集合对象的Add方法可创建一个新的图表工作表并它添加到工作簿中。结合使用Charts集合对象的Add方法和Chart对象的ChartWizard方法可添加包含工作表数据的新图表，例如下面的代码将工作表“Sheet1”的单元格区域“A1:A10”中的数据添加为一个新的拆线图：

　　Sub　添加图表工作表（）

　　　　With　Charts.Add

　　　　．ChartWizard　Source:=Worksheets("Sheet1").Range("A1:A10"),＿

　　　　　　Gallery:=xlLine，Title:="拆线图示例"

　　　　End　With

　　End　Sub

　　执行以上代码，将插入一个名为“Chart1”的图表工作表，并根据工作表“Sheet1”中“A1:A10”单元格区域中的数据生成拆线图，如图13-23所示。

　　在Chart对象的ChartWizard方法中，参数Gallery用于指定图表的类型，VBA中对各种图表类型都定义了常量，如本例的xlLine常量表示拆线图。常用的图表类型如下：

　　●xlColumnClustered，簇状柱形图。

　　●xlPie，饼图。

　　●xlColumnStacked，堆积柱形图。

　　●xl3DbarClustered，三维簇状条形图。

　　●xl3Dpie，三维饼图。

　　●xl3DcolumnStacked，三维堆积柱形图。

　　因为图表类型非常多，这里就不全部列出了。读者可通过帮助系统查看“xlChartType”枚举，其中列出了全部的图表类型常量。

　　15.3.3　添加嵌入式图表

　　使用ChartObjects集合对象的Add方法可创建一个新的空嵌入图表并将它添加到集合中，再结合XhartObject对象的ChartWizard方法添加数据并设置新图表的格式。以下代码将在工作表“Sheet1"k创建一个新嵌入式图表，然后以拆线图形式添加单元格“A1:A10

”中的数据。

　　执行以上代码，在工作表“Sheet1”中插入嵌入式图表，并根据工作表“Sheet1”中“A1:A10”单元格区域中的数据生成拆线图，如图13-24所示。

　　有关图表的操作将在22章中进行详细介绍。

　　　　　　　　　　　　　　　　

　　13.6　Window对象

　　Window对象代表一个窗口，能对窗口特性进行设置和操作。许多工作表特征（如滚动条和标尺）实际上是窗口的属性。

　　Window对象是Windows集合的成员。在Excel中，Applicaion对象和Worbook对象都有Windows集合，其中Applicaion对象的Windows集合包含应用程序中的所有窗口；而Workbook对象的Windows集合只包含指定工作簿中的窗口。

　　13.6.1　创建窗口

　　在Excel　2007操作环境中的“视图”选项卡的“窗口”组中，单击“新建窗口”按钮，可新建一个Excel窗口，该窗口的标题栏名称显示为“Book1.xlsm:2”，同时新建的窗口中也将显示活动窗口的副本，如图13-25所示。

　　在VBA代码中，使用NewWindow方法也可完成该功能：

　　Sub　创建窗口副本（）

　　　　ActiveWindow.NewWindow

　　　　MsgBox“新建窗口的窗口号是：”＆ActiveWindow.WindowNumber

　　End　Sub

　　该例在图13-25所示窗口（已新建一个窗口）的基础上运行，将创建第二个副本，窗口编号为3。运行后新创建的窗口标题栏如图13-26所示。

　　注意：窗口号和窗口索引（Index属性）是两个不同的概念，例如名称为“Book1.xlsm:2”的窗口，其窗口号为２；而窗口索引为该窗口在Window集合中的位置，可以是窗口名称或编号。

　　13.6.2　命名活动窗口

　　Application对象的Caption属性可改变Excel主窗口标题栏的显示内容。每个工作簿窗口还有一个名称，该名称可通过Workbook的Window对象进行修改。例如，以下代码将设置当前活动工作簿窗口名称：

　　13.6.3　控制窗口状态

　　窗口状态包括两个层面的意思，一是整个Excel应用程序窗口的状态；另一个是工作簿窗口的状态。窗口状态有三种形式，其常量表示为：

　　●xlNormal，正常；

　　●xlMaximized，最大化；

　　●xlMinimized，最小化。

　　以下代码用于控制Excel窗口和工作簿窗口的状态：

　　Sub　控制窗口状态（）

　　执行以上代码时，首先显示即将进行的操作。单击对话框中的“确定”按钮，将控制窗口的状态。

　　13.6.4　激活窗口

　　当打开多个Excel工作簿时，WindowsXP的任务栏中将出现多个任务按钮，单击按钮可激活对应的窗口。在VBA代码中，也可根据需要激活相应的窗口，例如，以下代码将当前Excel工作簿逐个激活：

　　13.6.5　控制工作表显示选项

　　通过“Excel选项”可设置工作表的显示选项，如图13-28所示。在该对话框中，通过选中或取消复选框中的标记可设置对应的选项。

　　有时需要在VBA程序中进行类似的设置。图13-28所示的各选项都与Window对象的某一属性相联系，只需要设置该属性的值，即可调整窗口的属性。这些属性都是逻辑值，可以使用“Not”运算对其进行取反，使用户每次选择都得到反向的设置，例如：

　　ActiveWindow.DisplayHeadings　＝　NOt　ActiveWindow.DisplayHeadings

　　以上代码用来切换是否显示工作表中的行列标号。运行后，工作表中的行标号和列标号将消失；再次运行后，行列标号重新出现，如此反复。当然也可以将该属性设置一个常量，如False，以取消显示行列标号。例如，以下代码显示或隐藏工作表中的相关元素。