Excel Application对象应用大全（四）

Application对象的其它一些属性和方法 Caller属性 Application对象的Caller属性返回调用或执行宏过程的对象的引用，适用于窗体工具栏控件、指定宏的绘图对象和用户自定义函数。在确定调用用户自定义函数的单元格时特别有用，例如下面的示例使用自定义函数WorksheetName函数显示调用该函数的工作表的名称。 Function WorksheetName() Application.Volatile WorksheetName = Application.Caller.Parent.Name End Function 在工作表单元格中输入“=WorksheetName()”，将显示该工作表的名称。Application.Caller作为一个Range对象返回对调用该函数的单元格的引用，然后使用Range对象的Parent属性产生对包含该Range对象的Worksheet对象的引用，最后将Worksheet对象的Name属性赋给该函数的返回值。当每次重新计算工作表时，Appliaction对象的Volatile方法强制Excel重新计算该函数。因此，如果改变了工作表的名称，那么该函数将显示新的工作表名称。 在WorksheetName函数中，使用下面的代码是错误的： WorksheetName = ActiveSheet.Name 如果激活的工作表不是包含该公式的工作表并发生重新计算，则将在原单元格中返回错误的名称。例如，如果在工作表Sheet1中已使用该函数，那么在工作表Sheet2中再次使用该函数时工作表Sheet1中将返回错误的名称，即工作表Sheet2的名称。 CutCopyMode属性 当我们在Excel工作表中使用剪切或复制时，在单元格区域周边会出现连续移动的虚线，即使执行完复制操作，原单元格区域周边的虚线框仍存在，直到您按Esc键或者开始单元格中输入操作。如果需要在执行完复制操作后，不要在Excel中看到复制时产生的虚线框，那么可以将Excel剪切复制模式关闭，即设置CutCopyMode属性为False： Application.CutCopyMode=False Evaluate方法 Evaluate方法用于将名称转换为一个对象或者一个值，其语法如下： Evaluate("表达式" ) 也可以使用简写格式： [表达式] 表达式可以是任何有效的工作表计算，在其左边有或者没有等号均可，或者是对单元格区域的引用，包括定义的名称或外部引用，或者是图表对象。工作表计算包含在VBA中不能使用的工作表函数，或者是工作表数组公式。 例如，可以在工作表中使用ISBLANK函数，但不能在VBA中使用该函数，因为VBA的等效函数IsEmpty提供了相同的功能。然而，如果需要，仍然可以使用ISBLANK函数。下面的二个示例是等价的，如果单元格A1为空，则返回 True；如果单元格A1不为空，则返回False。 MsgBox Evaluate("=ISBLANK(A1)" ) MsgBox [ISBLANK(A1)] 第一个示例用法的好处是能够非常灵活地使用代码产生字符串值。第二个示例用法更简短，但仅通过编辑代码来修改表达式。下面的过程显示True或者False表明活动单元格是否为空，同时演示了第一个示例用法的灵活性。 Sub IsActiveCellEmpty() Dim sFunctionName As String , sCellReference As String sFunctionName = "ISBLANK" sCellReference = ActiveCell.Address MsgBox Evaluate(sFunctionName & "(" & sCellReference & ")" ) End Sub 注意，使用第二个示例用法不能计算包含变量的表达式。 下面的两行代码演示使用Evaluate方法引用Range对象的两种方式，并且给该对象赋值： Evaluate("A1" ).Value = 10 [A1].Value = 10 第一个表达式是不实用的并且极少使用，而第二个表达式虽然极不灵活，却是引用Range对象更简便的方式。通过省略Value属性进一步简化表达式，因为该属性是Range对象的默认属性： [A1] = 10 总之，使用方括号的优点在于代码简短，而使用Evaluate方法的优点在于参数是字符串，这样即可以在代码中构造该字符串，也可以使用变量。再举一个示例，下面的代码将工作表Sheet1中单元格A1的字体加粗： Worksheets("Sheet1" ).Activate boldCell = "A1" Application.Evaluate(boldCell).Font.Bold = True 此外，Evaluate方法能够返回工作簿名称集合的内容以及有效地产生数组值。下面的代码创建一个隐藏的名称用来保存密码。在“插入”→“名称”→“定义”对话框中不能看到隐藏的名称，因此这是在工作簿里存储信息的便利方式，不会弄乱用户界面： Names.Add Name:= "PassWord" , RefersTo:= "Bazonkas" , Visible:=False 然后，像下面的代码一样在表达式里使用隐藏的数据： sUserInput = InputBox("Enter Password" ) If sUserInput = [PassWord] Then ... End If Evaluate方法同样适用于数组。下面的表达式产生一个二维的Variant型数组，100行1列，包含从101 到 200的值。执行该过程比使用For…Next循环更有效率。 vRowArray = [ROW(101:200)] 下面的代码将101至200的值赋给单元格区域B1:B100，同样比For…Next循环更有效率： [B1:B100] = [ROW(101:200)] OnKey方法 使用OnKey方法按特定的键或组合键运行指定的过程，也可以禁用内置的组合键。其语法为： Application.OnKey(Key,Procedure) 参数Key指定要按的键的字符串。参数Procedure指定要运行的过程名称的字符串，如果为空（”"），则按参数Key指定的键时将不发生任何操作；如果省略该参数，则恢复参数Key指定的键在Excel中的正常操作，同时清除先前使用OnKey方法所做的操作设置。 参数Key可以指定任何与Alt、Ctrl或Shift组合使用的键，还可以指定这些键的任何组合。每一个键可以由一个或多个字符表示，比如”a”表示字符a，”{ENTER}”表示Enter（回车）。若要指定按对应的键（例如Enter或Tab）时的非显示字符，可以使用下表2所列出的代码。表2中的每个代码表示键盘上的一个对应键，按键代码放置在花括号{}中。 还可以指定与Shift和/或Ctrl和/或Alt组合使用的键。若要指定与其他键组合使用的键，可使用下表3。 表2：OnKey方法的按键代码 按键 代码 Backspace {Backspace}或{BS} Break {BREAK} Caps Lock {CAPSLOCK} Clear {CLEAR} Delete或Del {DELETE}或{DEL} 向下箭头 {DOWN} End {END} Enter（在数字小键盘中） {ENTER} Enter ~（波形符） Esc {ESCAPE}或{ESC} Help {HELP} Home {HOME} Ins {INSERT} 向左箭头 {LEFT} Num Lock {NUMLOCK} Page Down {PGDN} Page Up {PGUP} Return {RETURN} 向右箭头 {RIGHT} Scroll Lock {SCROLLLOCK} Tab {TAB} 向上箭头 {UP} F1到F15 {F1}到{F15} 表3：组合按键代码 要组合的键 在按键代码之前添加 Shift +（加号） Ctrl ^（插入符号） Alt %（百分号） 若要为特定字符指定处理过程（如 +、^、% 等等），可以将此字符用花括号括起。 下面的示例为键序列Ctrl+加号分配“InsertProc”过程，并为键序列Shift+Ctrl+向右键分配“SpecialPrintProc”过程。 Application.OnKey "^{+}" , "InsertProc" Application.OnKey "+^{RIGHT}" , "SpecialPrintProc" 下面的示例将Shift+Ctrl+向右键恢复正常操作。 Application.OnKey "+^{RIGHT}" 下面的示例将Shift+Ctrl+向右键键序列设为不发生任何操作。 Application.OnKey "+^{RIGHT}" , "" 又如，下面的代码忽略Alt+F4组合键的操作： Application.OnKey "%{F4}" ,"" 即按下Alt+F4组合键后，Excel没有任何反应，不执行任何操作。 注意，OnKey方法使用的按键将应用到所有打开的工作簿，且仅在当前的Excel会话期间起作用。 下面的示例代码禁用工作表右键快捷菜单： Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object , _ ByVal Target As Range, Cancel As Boolean ) Cancel = True MsgBox "对不起!已禁用右键菜单!" End Sub '禁用Shift+{F10}显示快捷菜单 Sub SetupNoShiftF10() Application.OnKey "+{F10}" , "NoShiftF10" End Sub '恢复Shift+F10组合键的功能 Sub TurnOffNoShiftF10() Application.OnKey "+{F10}" End Sub Sub NoShiftF10() MsgBox "对不起!已禁用右键菜单!" End Sub ThisCell属性 ThisCell属性返回一个单元格，作为Range对象在此调用用户定义的函数。下面的示例在调用函数时将函数所在单元格的地址通知给用户。 Function UseThisCell() MsgBox "该单元格的地址为: " & _ Application.ThisCell.Address End Function 在用户定义的函数中，用户不能访问Range对象上的属性或方法。当完成了重新计算后，用户可以为今后的使用和执行附加操作而存储Range对象。 WorksheetFunction属性 在Excel中可以直接使用两组内置函数，一组函数是VBA语言的组成部分，另一组函数是Excel工作表函数的子集。可以使用Application对象的WorksheetFunction属性来使用VBA中没有相同功能的工作表函数。 通常，如果一个VBA函数与一个Excel函数有着相同的用途，那么该Excel函数就不能直接用于VBA宏（虽然本文前面介绍过可以使用Evalute方法访问任何Excel函数）。但还有一种特殊的情形，关于Excel的Mod函数。Mod函数不能直接用于VBA，但是VBA提供了相同用途的Mod操作符。下面的代码使用Evaluate方法的简洁格式，运用Excel的Mod函数和Today函数以数字显示星期几： MsgBox [MOD (TODAY(),7)] 使用VBA的Date函数和Mod操作符，更简单地获得相同的结果： MsgBox Date Mod 7 Excel的Concatenate 函数同样也不能用于VBA，但可以使用连接操作符(&)代替，就像在Excel工作表公式里一样。如果一定要在VBA中使用Concatenate函数，可以编写如下的代码： Sub ConcatenateExample1() Dim s1 As String , s2 As String s1 = "Jack " s2 = "Smith" MsgBox Evaluate("CONCATENATE(" "" & s1 & "" "," "" & s2 & "" ")" ) End Sub 但下面的代码更简单且结果相同： Sub ConcatenateExample2() Dim s1 As String , s2 As String s1 = "Jack " s2 = "Smith" MsgBox s1 & s2 End Sub VBA函数，例如Date、DateSerial和IsEmpty能够自由地使用，因为它们是<全局>的成员。例如，可以使用下面的代码： StartDate = DateSerial(1999, 6, 1) Excel函数，例如VLookup和SUM是WorksheetFuncion对象的方法，可以使用下面的代码： Total = WorksheetFunction.Sum(Range("A1:A10" )) 为了与Excel 5和Excel 95兼容，可以直接使用Application而无需WorksheetFunction： Total = Application.Sum(Range("A1:A10" )) 在VBE编辑器中，输入下面的代码： application.WorksheetFunction. 将自动显示出能够在VBA中使用的所有工作表函数列表。 改变光标显示（Cursor属性） 通过Cursor属性来设置光标在Excel界面中的显示形状，可以将其设置为xlIBeam（条状）、xlNorthwestArrow（西北向箭头）、xlWait（等待）、xlDefault（恢复为默认值）。 获取或改变Excel窗口的状态或大小（WindowState属性） 通过WindowState属性来获取或改变Excel窗口的状态，包括xlMaximized（最大化）、xlMinimized（最小化）、xlNormal（正常）。 通过Height属性和Width属性改变或获取Excel主应用程序的高度和宽度。注意，只有当Application.WindowState=xlNormal时，才能改变Height属性和Width属性。 通过UsableHeight属性和UsableWidth属性获取主应用程序窗口中一个窗口可用的最大高度和最大宽度。 获取系统信息 下面的示例程序使用了多个Application对象的属性以获取系统信息： Sub GetSystemInfo() MsgBox "Excel版本信息为:" & Application.CalculationVersion MsgBox "Excel当前允许使用的内存为:" & Application.MemoryFree MsgBox "Excel当前已经使用的内存为:" & Application.MemoryUsed MsgBox "Excel可以使用的内存为:" & Application.MemoryTotal MsgBox "本机操作系统的名称和版本为:" & Application.OperatingSystem MsgBox "本产品所登记的组织名称为:" & Application.OrganizationName MsgBox "当前用户名为:" & Application.UserName MsgBox "当前使用的Excel版本为:" & Application.Version End Sub 自动隐藏公式栏（DisplayFormulaBar属性） 在Excel 2003及以前版本的Excel中，当单元格中输入的数据超过一定数量时，公式栏会自动向下扩展，从而遮盖住了工作表区域。下面的示例代码当单元格中的字符数小于50时，显示公式栏，否则隐藏公式栏。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.count > 1 Then Exit Sub If Len(Target.Text) > 50 Or Len(Target.Formula) > 50 Then Application.DisplayFormulaBar = False Else Application.DisplayFormulaBar = True End If End Sub 当然，上述代码在Excel 2007中仍然有用，但Excel 2007已经改进了公式栏的特性，即使输入大量的数据，也不会遮盖工作表区域。 将Excel全屏显示（DisplayFullScreen属性） Sub testFullScreen() MsgBox "运行后将Excel的显示模式设置为全屏幕" Application.DisplayFullScreen = True MsgBox "恢复原来的状态" Application.DisplayFullScreen = False End Sub 获取Excel启动文件夹的路径（StartupPath属性） Sub ExcelStartFolder() MsgBox "Excel的启动文件夹的路径为:" & Chr(10) & Application.StartupPath End Sub 检测Excel的版本（Version属性） Sub CheckVersion() If Val(Application.Version) < 12 Then MsgBox "只在Excel 2007或更高版本中有效" ThisWorkbook.Close End If End Sub Application对象的Version属性返回一个代表当前运行的Excel的版本的字符串，使用Val函数返回该字符串内的数值。该属性通常用于判断当前Excel是否满足程序运行要求。 打开最近使用过的文档（RecentFiles属性） Sub OpenRecentFiles() MsgBox "显示最近使用过的第三个文件的名称,并打开该文件" MsgBox "最近使用过的第三个文件的名称为:" & Application.RecentFiles(3).Name Application.RecentFiles(3).Open End Sub 文件对话框操作（FileDialog属性） Sub UseFileDialogOpen() Dim lngCount As Long '开启"打开文件"对话框 With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show '显示所选的每个文件的路径 For lngCount = 1 To .SelectedItems.count MsgBox .SelectedItems(lngCount) Next lngCount End With End Sub 本示例显示“打开文件”对话框，当用户在其中选择一个或多个文件后，将依次显示每个文件的路径。其中FileDialog属性返回打开和保存对话框中一系列对象的集合，可以对该集合对象的属性进行进一步的设置，例如上例中的AllowMultiSelect属性设置为True将允许用户选择多个文件。 改变Excel工作簿的名称（Caption属性） Sub SetCaption() Application.Caption = "我的工作簿" End Sub 将工作簿中的Microsoft Excel列改为“我的工作簿”。 调用Windows的计算器（ActivateMicrosoftApp方法） Sub CallCalculate() Application.ActivateMicrosoftApp Index:=0 End Sub 运行上述过程后，将调用Windows的计算器。 暂时停止宏运行（Wait方法） Sub Stop5sMacroRun() Dim SetTime As Date MsgBox "按下「确定」,5秒后执行程序「testFullScreen」" SetTime = DateAdd("s" , 5, Now()) Application.Wait SetTime Call testFullScreen End Sub 运行本程序后，按下弹出的提示框中的“确定”按钮，等待5秒后执行另一程序testFullScreen。 重新计算工作簿（Calculate方法） 当工作簿的计算模式被设置为手动模式后，运用Calculate方法可以重新计算所有打开的工作簿、工作簿中特定的工作表或者工作表中指定的单元格区域，如下面的代码： Sub CalculateAllWorkbook() Application.Calculate End Sub 下面的代码先将当前Microsoft Excel的版本与上次计算该工作簿的Excel版本进行比较，如果两个版本不同，则对所有打开工作簿中的数据进行一次完整计算。其中，CalculationVersion属性返回工作簿的版本信息。 Sub CalculateFullSample() If Application.CalculationVersion <> Workbooks(1).CalculationVersion Then Application.CalculateFull End If End Sub 控制函数重新计算（Volatile方法） Function NonStaticRand() '当工作表中任意单元格重新计算时本函数更新 Application.Volatile True NonStaticRand = Rnd() End Function 本示例摸仿Excel中的Rand()函数，当工作表单元格发生变化时，都会重新计算该函数。在例子中，使用了Volatile方法，强制函数进行重新计算，即无论何时重新计算工作表，该函数都会重新计算。 获取重叠区域（Intersect方法） Sub IntersectRange() Dim rSect As Range Worksheets("Sheet1" ).Activate Set rSect = Application.Intersect(Range("rg1" ), Range("rg2" )) If rSect Is Nothing Then MsgBox "没有交叉区域" Else rSect.Select End If End Sub 本示例在工作表Sheet1中选定两个命名区域rg1和rg2的重叠区域，如果所选区域不重叠，则显示一条相应的信息。其中，Intersect方法返回一个Range对象，代表两个或多个范围重叠的矩形区域。 获取路径分隔符（PathSeparator属性） Sub GetPathSeparator() MsgBox "路径分隔符为" & Application.PathSeparator End Sub 本示例使用PathSeparator属性返回路径分隔符（”/”）。 快速移至指定位置（Goto方法） Sub GotoSample() Application.Goto Reference:=Worksheets("Sheet1" ).Range("A154" ), _ scroll:=True End Sub 本示例运行后，将当前单元格移至工作表Sheet1中的单元格A154。可以将本技巧运用到工作簿事件中，即当打开某工作簿时，快速定位到上一次退出时的单元格位置。 关闭Excel（Quit方法） Sub 关闭Excel() MsgBox "Excel将会关闭" Application.Quit End Sub 运行本程序后，若该工作簿未保存，则会弹出对话框询问是否保存。