Excel Application对象应用大全（二）

 

Application对象相关的方法 下面探讨Application对象经常使用的一些方法。 FindFile方法和Dialogs集合 与GetOpenFilename方法不同，FileFind方法显示“打开”对话框并允许用户打开文件。如果成功打开文件，那么该方法返回True；如果用户取消了该对话框，那么该方法返回False。 下面的示例显示一条消息，告诉用户打开一个指定的文件，然后显示“打开”对话框。如果用户不能够打开该文件，则显示一条消息。 Sub OpenFile1() Dim bSuccess As Boolean MsgBox "请定位到MonthlySales.xls文件." bSuccess = Application.FindFile If Not bSuccess Then MsgBox "该文件没有打开." End If End Sub 也可以使用Dialogs集合打开特定的对话框来完成相同的操作。使用Dialogs集合的优势之一是使用Show方法，可以传递参数修改内置对话框的缺省行为。例如，xlDialogOpen的参数为：file_text、update_links、read_only、format、prot_pwd、write_res_pwd、ignore_rorec、file_origin、custom_delimit、add_logical、editable、file_access、notify_logical、converter。 注：要找到特定对话框的参数，在Excel帮助的“内置对话框参数列表”中查找相应的对话框常量。 下面的示例显示在文件名框中带有Book1.xlsm的“打开”对话框，允许用户显示缺省文件而不必选择文件。 Sub OpenFile2() Application.Dialogs(XlBuiltInDialog.xlDialogOpen).Show arg1:="Book1.xlsm" End Sub Dialogs集合的优点在于，可以使用它来显示任何的Excel对话框（大约有250个）。通过下述步骤可以找到对话框完整列表。 （查找对话框集合的成员列表） 1、打开VBE。 2、单击“查看——对象浏览器”或者按F2键，显示“对象浏览器”。 3、在搜索框中输入xlDialog。 4、单击“搜索”按钮。 对Excel 2007而言，可以使用CommandBar对象来执行功能区中的命令，例如，下面的语句显示“定位”对话框： Application.CommandBars.ExecuteMso ("GoTo" ) ExecuteMso方法执行由idMso参数标识的控件。idMso参数的取值可以查找网上资源。 下面的语句显示“设置单元格格式”对话框中的“字体”选项卡： Application.CommandBars.ExecuteMso ("FormatCellsFontDialog" ) GetOpenFilename方法 GetOpenFilename方法显示标准的“打开”对话框并从用户处获取文件名称，但不真正打开任何文件，而是以字符串返回用户选择的文件名及其路径。那么，您可以利用该字符串完成所需要的操作，例如可以传递返回的结果到OpenText方法。下面是GetOpenFilename方法的语法（所有参数都是可选的）： GetOpenFilename(FileFilter,FilterIndex,Title,ButtonText,MultiSelect) 参数FileFilter是一个字符串，规定筛选条件（例如，*.txt，*.xla），在“打开”文件对话框中只显示与筛选条件相匹配的文件，默认为“所有文件(*.*),*.*”。参数FilterIndex指定缺省的文件筛选条件的索引值，从1到参数FileFilter中指定的筛选数，默认使用索引值为1的文件筛选条件。参数Title指定对话框的标题，默认显示“打开”。参数ButtonText仅用于Macintosh计算机。参数MultiSelect是一个Boolean值，指定能否选择多个文件，默认仅能够选择单个文件。 下面的示例显示在文件类型中设置为文本文件（*.txt）的“打开”对话框，然后显示带有用户选择的信息的消息框。注意，文件并没有被打开。 Dim fileToOpen As String fileToOpen = Application.GetOpenFilename("文本文件(*.txt),*.txt" ) If fileToOpen <> "" Then MsgBox "打开" & fileToOpen End If 下面的示例获取多个工作簿： '作者:Steven M. Hansen Sub TestGetFiles() Dim nIndex As Integer Dim vFiles As Variant Dim strFileName As String '获取多个Excel文件 vFiles = GetExcelFiles("测试GetExcelFiles函数" ) '确保没有取消对话框. '如果用户取消对话框,函数返回False,而不是数组 If Not IsArray(vFiles) Then MsgBox "没有选择文件." Exit Sub End If '如果没有取消对话框,则遍历文件 For nIndex = 1 To UBound (vFiles) strFileName = strFileName & vbCrLf & vFiles(nIndex) Next nIndex '显示用户所选择的文件名称 MsgBox "用户已选择的文件如下:" & vbCrLf & strFileName End Sub '允许选择多个文件 '返回含有文件名称的数组 Function GetExcelFiles(sTitle As String ) As Variant Dim sFilter As String Dim bMultiSelect As Boolean sFilter = "Excel工作簿(*.xlsx),*.xlsx" bMultiSelect = True GetExcelFiles = Application.GetOpenFilename(FileFilter:=sFilter, _ Title:=sTitle, MultiSelect:=bMultiSelect) End Function 当将GetOpenFilename方法的参数MultiSelect设置为True时，如果用户选择了文件，那么将返回一个变体类型的数组，且数组索引值基于1而不是0；如果用户取消了选择文件，那么返回False。在TestGetFiles过程的代码中，使用IsArray函数测试返回值是否是数组。如果使用vFiles=False来判断的话，当用户选择了文件时，由于返回的值为数组，则会导致运行时错误：类型不匹配。 GetSaveAsFilename方法 GetSaveAsFilename方法显示“另存为”对话框，允许用户指定一个文件名和需要保存文件的位置，但是实际上并没有保存文件。GetSaveAsFilename方法的语法如下（所有参数都是可选的）： Application.GetSaveAsFilename(InitialFilename,FileFilter,FilterIndex,Title,ButtonText) 参数InitialFilename为指定文件名的字符串，默认为活动工作簿的名称，若不需要指定初始文件名，则将其设置为空字符串（”"）；参数FileFilter是表示筛选条件的字符串，在“另存为”对话框只显示与筛选条件相匹配的文件，默认为“所有文件(*.*),*.*”；参数FilterIndex用来指定缺省的文件筛选条件的索引值，默认使用索引值为1的文件筛选条件；参数Title指定显示对话框标题的字符串文本，默认显示“另存为”；参数ButtonText仅用于Macintosh计算机。 下面介绍一个综合示例，是Steven M. Hansen编写的，从完整的文件名字符串中分解出文件路径和文件名。 Sub TestBreakdownName() Dim sPath As String Dim sName As String Dim sFileName As String Dim sMsg As String sFileName = Application.GetSaveAsFilename BreakdownName sFileName, sName, sPath sMsg = "文件名是:" & sName & vbCrLf sMsg = sMsg & "文件路径是:" & sPath MsgBox sMsg, vbOKOnly End Sub Function GetShortName(sLongName As String ) As String Dim sPath As String Dim sShortName As String BreakdownName sLongName, sShortName, sPath GetShortName = sShortName End Function Sub BreakdownName(sFullName As String , _ ByRef sName As String , _ ByRef sPath As String ) Dim nPos As Integer '找出文件名从哪里开始 nPos = FileNamePosition(sFullName) If nPos > 0 Then sName = Right(sFullName, Len(sFullName) - nPos) sPath = Left(sFullName, nPos - 1) Else '无效的文件名 End If End Sub '返回提供的完整文件名中文件名的位置或首字符索引值 '完整文件名包括路径和文件名 '例如:FileNamePosition("C:/Testing/Test.xlsx")=11 Function FileNamePosition(sFullName As String ) As Integer Dim bFound As Boolean Dim nPosition As Integer bFound = False nPosition = Len(sFullName) Do While bFound = False '确保不是零长度字符串 If nPosition = 0 Then Exit Do '从右开始查找第一个"/" If Mid(sFullName, nPosition, 1) = "/" Then bFound = True Else '从右至左 nPosition = nPosition - 1 End If Loop If bFound = False Then FileNamePosition = 0 Else FileNamePosition = nPosition End If End Function 除了运行TestBreakdownName过程获取文件名和文件路径外，还可以使用GetShortName函数仅获取文件名。此外，在Sub过程BreakdownName中使用了ByRef参数，即通过引用传递参数，这样传递给子过程的参数改变后，调用子过程的主过程中相应的参数也随之改变。 InputBox方法 InputBox方法提供了一种程序与用户之间进行简单的交互的方式，允许我们从用户处获得信息。该方法将显示一个对话框，提示用户输入某值。通过指定希望用户输入的数据类型，InputBox方法能够进行数据验证。InputBox方法的语法如下： InputBox(Prompt,Title,Default,Left,Top,HelpFile,HelpContextID,Type ) 其中：参数Prompt是在对话框中显示的消息。这里，可以提示用户您希望用户输入的数据类型。该参数是唯一的必需参数。 参数Title是对话框顶部显示的标题。缺省使用应用程序名称。 参数Default是对话框最初显示时的缺省值。 参数Left和Top用于指定对话框的位置，这些值相对于屏幕的左上角且以磅为单位。如果忽略，则对话框将水平居中且距屏幕顶约1/3处。 参数HelpFile和HelpContextId指定帮助文件，如果使用了这两个参数，那么在对话框中将出现帮助按钮。 参数Type指定需要返回的数据类型。缺省为文本，允许的类型列于表1。 注意，如果Type为8，那么必须使用Set语句将结果赋值给Range对象，如下面的代码所示： 表1：InputBox方法返回的数据类型 值 类型 0 公式。公式作为字符串被返回。这是仅有的必需的参数。 1 数值。也可以包括返回值的公式。 2 文本（字符串） 4 逻辑值（True或False） 8 单元格引用，作为Range对象 16 错误值，例如#N/A 64 值列表 Set myRange = Application.InputBox(Prompt:="示例" , Type :=8) 如果希望允许输入多种数据类型，那么可以使用上表中的任意数值组合。例如，如果要显示一个可以接受文本或数值的输入框，则可以将type的值设置为3（即1+2的结果）。如果输入了错误类型的数据，则显示错误消息并提示再次输入数据。如果单击“取消”按钮，则返回False。 下面的示例提示用户输入希望打印活动工作表的份数（注意，type指定希望输入的是一个数值）： Sub PrintActiveSheet() Dim TotalCopies As Long , NumCopies As Long Dim sPrompt As String , sTitle As String sPrompt = "您想要多少副本?" sTitle = "打印活动工作表" TotalCopies = Application.InputBox(Prompt:=sPrompt, Title:=sTitle, Default:=1, Type :=1) For NumCopies = 1 To TotalCopies ActiveSheet.PrintOut Next NumCopies End Sub 如果将InputBox方法的返回值赋给一个Variant型变量，则可以检测该值是否为False。如果要返回单元格区域，则使用像下面的代码会更好： Sub GetRange() Dim rng As Range On Error Resume Next Set rng = Application.InputBox(Prompt:="输入单元格区域" , Type :=8) If rng Is Nothing Then MsgBox "操作取消" Else rng.Select End If End Sub 此时，必须使用Set语句将Range对象赋值给某对象变量，如果用户单击“取消”按钮则返回值False，Set语句将失败并提示运行时错误。使用On Error Resume Next语句避免运行时错误，然后检查是否产生了一个有效的区域。如果用户单击“确定”按钮，那么InputBox方法检查内置类型以确保将返回有效的区域，因此空区域表明单击了“取消”按钮。 Run方法 Run方法执行一个宏或调用一个函数。可以使用该方法运行由VBA或Excel宏语言编写的宏，或者运行动态链接库（DLL）里的函数或Excel加载项（XLL）。XLL是使用任何支持创建DLLs的编译器为Excel创建的加载项。Run方法的语法为： Run(Macro,Arg1,…,Arg30) 参数Macro是要执行的宏或函数的名称，参数Arg1至Arg30是需要传递给宏或函数的一些参数。 下面的示例使用Run方法调用一个过程，设置单元格区域中单元格的字体为粗体。当然，也可以使用Call方法获得相同的结果。 Sub UseRunMethod() Dim wks As Worksheet Dim rng As Range Set wks = Worksheets("Sheet2" ) Set rng = wks.Range("A1:A10" ) Application.Run "MyProc" , rng '也能够使用下面的语句完成相同的任务 'Call MyProc(rng) End Sub Sub MyProc(rng As Range) With rng.Font .Bold = True End With End Sub Application对象相关的事件 Application对象也有一些事件，能够用于监视整个Excel应用程序的行为。要使用Application事件，必须启用事件监视。 激活Application事件监视 1、单击“插入——类模块”，创建一个类。 2、在属性中，将类的名称改为AppEventClass。 3、在类的代码窗口，添加下面的代码： Public WithEvents Appl As Application 现在，能够在应用程序中运用应用程序级事件。 4、在代码窗口顶部左侧的对象列表中，选择Appl。 5、在代码窗口顶部右侧的过程列表中，选择WorkbookOpen。此时，将为Appl_WorkbookOpen过程插入一对占位符。 6、在过程中添加下面的语句： Private Sub Appl_WorkbookOpen(ByVal Wb As Workbook) MsgBox "已打开工作簿." End Sub 7、重复上面的步骤插入Appl_WorkbookBeforeClose事件，并添加下面的语句： Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean ) MsgBox "关闭工作簿." End Sub 8、接下来，创建一个变量用于引用类模块中创建的Application对象。在工程资源管理器中，双击ThisWorkbook打开代码窗口。 9、添加下面的语句。 Dim ApplicationClass As New AppEventClass 通过在ThisWorkbook代码窗口添加下面的语句，创建所声明的对象对Application对象的连接： Private Sub Workbook_Open() Set ApplicationClass.Appl = Application End Sub 10、保存并关闭该工作簿。 11、现在，测试代码。打开该工作簿，将触发Appl_WorkbookOpen事件，显示相应的信息框。 12、关闭该工作簿，将触发Appl_WorkbookBeforeClose事件，显示相应的信息框。 13、切换回AppEventClass类模块并单击过程列表显示能够用于监控应用程序行为的一系列事件。 理解这些事件如何被触发以及事件的顺序对理解应用程序是重要的。在类模块中添加其他的事件并插入消息框，然后试验不同的行为来看看何时触发某特定的事件。