Excel Application对象应用大全（三）

使用Application对象执行其它任务 除了Application对象中最常用的对象外，您可能希望在Excel应用程序中执行一些其他任务。下面我们就来探讨这方面的内容。 删除工作表而显示提示信息（DisplayAlerts属性） 下面的示例首先关闭询问是否保存工作表的任何消息，接着删除工作表并打开警告消息。 Sub DeleteSheet() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub 上述示例代码中使用了DisplayAlerts属性，将其值设置为False以自动执行Excel警告对话框中默认按钮相关的操作。 设置DisplayAlerts属性的意图在于，运行宏时不必响应系统出现的警告而使执行过程中断。当然，在过程结束前，最好将DisplayAlerts属性设置为True。 无须提示用户而保存工作表（DisplayAlerts属性） 下面的示例保存工作表，而没有通知用户是否保存。 Sub SaveWorksheet() Application.DisplayAlerts = False ActiveWorkbook.SaveAs "C:/MonthlySales.xls" Application.DisplayAlerts = True End Sub 此时，如果现有文件与要保存的文件名相同，那么会覆盖该文件而不会弹出任何警告消息。 使用SendKeys方法发送信息到记事本 SendKeys方法允许发送按键到当前活动窗口，用来控制不支持任何其他交互形式的应用程序，例如DDE(动态数据交换)或OLE。 下面的示例使用SendKeys命令从Excel中复制数据区域到记事本，然后保存该文件。 Sub SKeys() Range("A1:D15" ).Copy '复制单元格区域 SendKeys "% n" , True '最小化Excel Shell "notepad.exe" , vbNormalFocus '开启记事本 SendKeys "^V" , True '将数据粘贴到记事本 SendKeys "%FA" , True '指定另存为 SendKeys "SalesData.txt" , True '提供文件名 SendKeys "%S" , True '保存文件 End Sub 本示例首先复制数据区域到剪贴板，然后最小化Excel，开启记事本，接着从剪贴板复制数据到记事本，最后指定文件名并保存文件。 下面的示例打开了“记事本”应用程序(不支持DDE或OLE)，并将数据行写入记事本文档： Sub SKeys() Dim dReturnValue As Double dReturnValue = Shell("NOTEPAD.EXE" , vbNormalFocus) AppActivate dReturnValue Application.SendKeys "Copy Data.xlsx c:/" , True Application.SendKeys "~" , True Application.SendKeys "%FABATCH%S" , True End Sub 注意，应该在Excel应用程序窗口执行上述程序。 下面的过程清除VBE立即窗口中的内容。如果在立即窗口中进行过试验或者使用Debug.Print语句在立即窗口输出数据，那么旧的信息将产生混乱。该过程将焦点转移到立即窗口，发送选择该窗口中的所有文本，然后发送Del键删除文本： Sub ImmediateWindowClear() Application.VBE.Windows.Item("立即窗口" ).SetFocus Application.SendKeys "^a" Application.SendKeys "{Del}" End Sub 注意，要使上述代码运行，必需编程访问Visual Basic工程。从Excel功能区中选择“开发工具”选项卡，选择“宏安全性”，然后勾选“信任对VBA工程对象模型的访问”。 其中，百分比符号(%)用于代表Alt键，波形符号(~) 代表回车键，^符号代表Ctrl键。在花括号{}里放置名称指定其它特别的键，例如{Del}代表Delete键。 安排宏在指定的时间和间隔运行（OnTime方法） 可以使用Application对象的OnTime方法在指定的时间或者在有规律的时间间隔运行某过程。OnTime方法的语法如下： Application.OnTime(EarliestTime,Procedure,LastestTime,Schedule) 参数EarliestTime指明希望何时运行由参数Procedure指定的过程，可选的参数LastestTime和Schedule指明过程运行的最迟时间，以及是否安排运行一个新过程或者删除已经存在的过程。当开始调用某过程而Excel正忙时，则需要使用参数LastestTime指定希望调用该过程的时间区间。如果使用Application对象的Wait方法暂停某宏，所有的Excel行为，包括手工交互操作，都将被挂起。OnTime方法的优势在于，当等待运行安排的宏时，允许返回正常的Excel交互操作，包括运行其他的宏。 下面的示例指定每隔5分钟运行一次名为YourProc的过程： Application.OnTime EarliestTime:=Now + TimeValue("00:05:00" ), Procedure:="YourProc" 下面的示例在每天中午运行过程YourProc： Application.OnTime EarliestTime:=TimeValue("12:00:00" ), Procedure:="YourProc" 下面的示例安排每隔5分钟调用一次AutoSave过程。如果关闭该工作簿，则调用CleanUp过程来执行可能希望的清理以及删除任何额外的调用。 Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:05:00" ), "AutoSave" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean ) On Error Resume Next Application.OnTime Now + TimeValue("00:05:00" ), "CleanUp" , , False End Sub 注意，Workbook_Open事件和Workbook_BeforeClose事件包含在工作簿代码模块中，而AutoSave过程和CleanUp过程则存在于标准代码模块中。 当使用OnTime方法安排在将来的某个时间运行宏时，必须确保Excel一直在内存中运行直至到达安排的时间。但不需要一直打开包含OnTime宏的工作簿。如果需要，Excel将打开该工作簿。 通过上述简介，我们已经了解了OnTime方法的基本用法。下面再详细介绍OnTime方法。 有时，我们可能需要设计Excel工作簿定期并自动地运行一个过程。例如，可能希望每隔几分钟从数据源中更新数据，此时执行Excel应用程序的OnTime方法指令Excel在给定的时间去运行某过程。通过编写代码使程序自已调用OnTime方法，能使VBA代码定期自动执行。 OnTime方法要求指定日期和时间以及要运行的过程作为参数，重要的是要记住具体地告诉Excel什么时候运行这个过程而不是从当前时间开始的偏差。为了取消一个未执行的OnTime过程，必须经过该过程计划要运行的确切的时间，不能够告诉Excel取消下一个计划执行的过程。因此，建议将安排过程开始运行的时间存放在一个公共的（或全局）变量中，该变量作用于所有的代码。然后，能够使用所存储时间的变量去安排运行或取消事件。下面的示例代码在公共的常量中存储了所运行过程的名称和重复执行的时间间隔，当然这不是必需的。 Public RunWhen As Double Public Const cRunIntervalSeconds = 120 ' two minutes Public Const cRunWhat = "The_Sub" 为开始这个过程，使用一个名为 StartTimer的子程序。代码如下： Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _ schedule:=True End Sub 将比当前时间多两分钟的日期和时间存放在RunWhen变量中，然后调用OnTime方法指令Excel何时运行cRunWhat过程。 “The_Sub”是一个字符串变量，Excel将在合适的时间运行该过程。下面是该过程代码示例： Sub The_Sub() ' '这里放置代码 ' StartTimer End Sub 注意，The_Sub过程的最后一行调用了StartTimer过程，再次重复运行这个过程。并且当下次使用OnTime调用The_Sub过程时，将再次调用StartTimer来重复执行它自已。这就是如何执行周期循环的方法。 有时，当关闭工作簿时或者满足某个条件时需要停止定时执行的过程。由于OnTime方法是Application对象的一部分，简单地关闭已创建事件的工作簿不会取消对OnTime的调用。一旦Excel自身保持运行，它将执行OnTime过程，并且在必要时会自动打开该工作簿。 为了停止OnTime过程，必须对OnTime方法指定确切的时间，这就是我们将时间作为公共的变量存放在RunWhen中的原因。否则，没办法知道过程计划执行的确切时间。（所计划的时间像OnTime方法中的一把“钥匙”，如果没有它，就没有通往事件的入口） 下面是一个名为StopTimer的子过程，它将停止要执行的OnTime过程。 Sub StopTimer() On Error Resume Next Application.OnTime earliesttime:=RunWhen, _ procedure:=cRunWhat, schedule:=False End Sub 这个过程使用了和StartTimer过程相同的OnTime语法，将schedule参数设置为False告诉Excel取消该过程的执行。可能希望在Auto_Close宏或Workbook_BeforeClose事件中包括一个对该过程的调用。在StopTimer过程中，使用On Error Resume Next语句忽略当你企图删除一个不存在的过程时可能产生的任何错误。 下面的示例演示当在单元格B1中输入一个值后，如果A1单元格中不为空，那么将在10秒后自动清除单元格A1和B1中的内容。示例代码如下： 在标准模块中输入如下代码： Sub DeleteContents() Worksheets("Sheet1" ).Range("A1:B1" ).ClearContents End Sub Sub MyEntry() Range("B1" ).Value = "Goodbye" End Sub 在工作表sheet1代码模块中输入如下代码： Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$B$1" Then Exit Sub If IsEmpty(Target) Or IsEmpty(Target.Offset(0, -1)) Then Exit Sub Application.OnTime Now + TimeSerial(0, 0, 10), "DeleteContents" End Sub 扩展话题一： 在VBA帮助系统的OnTime方法介绍中，只对其参数EarliestTime, Procedure, LatestTime, Schedule进行了说明，并列举了定时运行某过程和撤销OnTime设置的三个代码示例。对OnAction属性的介绍中，也只简要介绍了单击某图形或菜单项时运行指定宏的示例。均没有介绍当OnTime方法或OnAction属性中设置的所要运行的宏带有参数时，如何传递参数到这些宏程序中。下面是自已总结的一些参数的传递方法，供分享。 因为运用Application.OnTime或Object.OnAction调用宏程序的语法基本相似，因此下面介绍的OnTime方法所使用的语法同样适用于OnAction属性。 为了便于理解，以下介绍均使用一段相似的代码，只不过传递给所调用宏程序MyProcedure的参数不同而已，以此来讲解传递给宏程序不同参数的方法。例如，下面的代码将使MyProcedure宏程序在从现在起的2秒后运行： Application.OnTime Now + TimeValue("00:00:02" ), "MyProcedure" 问题1： 假设MyProcedure宏程序接受参数，如何传递参数到该宏程序中？有下面几种情形： （1）所调用的宏程序接受一个参数 如果是在正常代码过程中传递参数给宏程序，可以使用” MyProcedure (42)”，其中“42”为传递给MyProcedure程序的参数。但如果这样的传递参数方法用在OnTime方法中，该程序将不会运行。 正确的语法是外层为双引号，内层再加上一组单引号，里面是程序名和程序所接受的参数。如下所示： ‘MyProcedure宏程序接受一个数值参数 Application.OnTime Now + TimeValue("00:00:02" ), "' MyProcedure 42'" （2）所调用的宏程序接受多个参数 如果所调用的宏程序接受几个参数，那么在这些参数之间应该用逗号分隔。如下所示： 'MyProcedure宏程序接受两个数值参数 Application.OnTime Now + TimeValue("00:00:02" ), "'MyProcedure 42, 13'" （3）所调用的宏程序接受字符串参数 如果所调用的宏程序所接受的参数是字符串，因为字符串已经带有一对双引号，因此应该将字符串包含在双层双引号中，即字符串参数周围有两对双引号。如下所示： 'MyProcedure宏程序接受一个字符串Hello!作为其参数 Application.OnTime Now + TimeValue("00:00:02" ), "'MyProcedure " "Hello!" "'" 问题2： 当MyProcedure宏程序所接受的参数是变量，如何传递参数到该宏程序中？ （1）该变量为局部变量，用如下所示的方式。 'MyProcedure宏程序接受一个字符串变量strText参数，该变量为局部变量 strText = "Hello!" Application.OnTime Now + TimeValue("00:00:02" ), "'MyProcedure " "" & strText & "" "'" （2）该变量为全局变量，用如下所示的方式，即不必加双层双引号。 'MyProcedure宏程序接受一个字符串变量g_strText参数，该变量必须声明为公有的 g_strText = "Hello!" Application.OnTime Now + TimeValue("00:00:02" ), "'MyProcedure g_strText'" 注意，在这种情况下变量必须声明为公共变量，否则MyProcedure宏程序将不能找到该变量参数。 扩展话题二： 除了Excel的OnTime方法外，还能使用Windows API库提供的Timer函数。在某些情况下，使用API过程比使用OnTime方法更容易：第一，告诉Windows需要计时器发生的时间间隔而不是某天的特定时间；第二，API过程将自动更新，计时器将每隔一段时间发生直到你告诉它停下来为止。 这些过程需要在Office2000或更新的版本中运行，因为我们使用了AddressOf函数，他们不会在Excel97或更早的版本中运行。 为了使用Windows计时器，将下面的代码放在一个标准代码模块中。 Public Declare Function SetTimer Lib "user32" ( _ ByVal HWnd As Long , ByVal nIDEvent As Long , _ ByVal uElapse As Long , ByVal lpTimerFunc As Long ) As Long Public Declare Function KillTimer Lib "user32" ( _ ByVal HWnd As Long , ByVal nIDEvent As Long ) As Long Public TimerID As Long Public TimerSeconds As Single Sub StartTimer() TimerSeconds = 1 ' 指定计时器的间隔. TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc) End Sub Sub EndTimer() On Error Resume Next KillTimer 0&, TimerID End Sub Sub TimerProc(ByVal HWnd As Long , ByVal uMsg As Long , _ ByVal nIDEvent As Long , ByVal dwTimer As Long ) ' '由Windows调用的过程. ' 将与计时器相关的代码放置于此. ' End Sub 执行StartTimer过程开始计时。变量TimerSeconds指明计时器之间间隔有多少秒。这个值可能少于1。注意SetTimer过程在毫秒范围内取值，于是当我们调用SetTimer时我们通过将TimerSeconds乘以1000来增加间隔值。Windows每隔一段计时器发生的时间来调用TimerProc过程。可以将这个过程命名为想要的名字，但必须如示例中所示声明这些参数变量。如果变换了过程的名称，那么要确保也变换了SetTimer中的名字。Windows将传递下面的值到TimerProc过程： HWnd Excel应用程序的Windows句柄。一般可忽略这个参数。 uMsg 值为275。一般可忽略这个参数。 nIDEvent 这个值通过SetTimer获得TimerID变量返回。如果不止一次调用SetTimer，那么能检查nIDEvent参数确定哪次调用SetTimer导致调用该过程。 dwTimer 计算机运行的毫秒数。相同的值通过GetTickCount Windows过程被返回。 调用EndTimer过程来停止计时器循环，这个过程调用KillTimer，通过SetTimer返回的值传递给它。 API计时器和Excel的OnTime方法间有两个重要的区别：第一，API计时器有着更精确的时间间隔（达到1秒或更少）；第二，即便Excel处在编辑模式（也就是说，当正在编辑单元格时），API计时器也将执行。注意，当Excel处于编辑模式时，如果TImerProc企图修改工作表单元格，Excel将会立即退出。