VBA常用技巧
目录
VBA常用技巧 (1)
第5章Application对象 (2)
技巧1取得Excel版本信息 (2)
技巧2取得当前用户名称 (3)
技巧3Excel中的“定时器” (3)
技巧4设置活动打印机的名称 (5)
技巧5屏蔽、改变组合键的功能 (6)
技巧6设置Excel窗口标题栏 (7)
技巧7自定义Excel状态栏 (8)
技巧8灵活退出Excel (9)
技巧9隐藏Excel主窗口 (10)
9-1设置Application对象的Visible属性 (10)
9-2将窗口移出屏幕 (11)
9-3设置工作簿作为加载宏运行 (12)
第5章Application对象
技巧1 取得Excel版本信息
Application对象的Version属性可以返回Excel的版本号,如下面的代码所示。#001 Sub AppVersion()
#002 Dim myVersion As String
#003 Select Case Application.Version
#004 Case "8.0"
#005 myVersion = "97"
#006 Case "9.0"
#007 myVersion = "2000"
#008 Case "10.0"
#009 myVersion = "2002"
#010 Case "11.0"
#011 myVersion = "2003"
#012 Case Else
#013 myVersion = "版本未知"
#014 End Select
#015 MsgBox "Excel 版本是: " & myVersion
#016 End Sub
代码解析:
AppVersion过程返回Application对象的Version属性值来取得Excel版本号。应用于Application对象的Version属性返回Excel版本号,语法如下:expression.Version
参数expression是必需的,Application对象。
运行AppVersion过程结果如图1-1所示。
图1-1 取得Excel版本号
技巧2 取得当前用户名称
使用Application对象的UserName属性可以取得当前用户名称,如下面的代码所示。Sub UserName()
MsgBox "当前用户名是: " & https://www.doczj.com/doc/7615625215.html,erName
End Sub
代码解析:
UserName过程使用消息框显示当前用户名称。
Application对象的UserName属性返回或设置当前用户的名称。
运行UserName过程效果如图2-1所示。
图2-1 显示当前用户名称
技巧3 E xcel中的“定时器”
Excel VBA并没有提供定时器控件,但是用户可以通过Application对象的OnTime方
法实现简单的定时器功能,如下面的代码所示。
#001 Sub StartTimer()
#002 Sheet1.Cells(1, 2) = Sheet1.Cells(1, 2) + 1
#003 Application.OnTime Now + TimeValue("00:00:01"), "StartTimer"
#004 End Sub
代码解析:
StartTimer过程,使用Application对象的OnTime方法循环调用StartTimer过程实现每隔一秒钟运行一次StartTimer过程,从而在B1单元格中不断地显示程序累计运行时间,如图3-1所示。
图3-1 简单的定时器
第2行代码将B1单元格的值在原有的数字上加1。
第3行代码使用OnTime方法在1秒后重新调用StartTimer过程,使B1单元格的值不断的加1,从而显示程序累计运行时间。
应用于Application对象的OnTime方法能够安排一个过程在将来的特定时间运行,语法如下:
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
参数expression是必需的,返回一个Application对象。
参数EarliestTime是必需的,设置指定的过程开始运行的时间。使用Now + TimeValue(time)可以安排从现在开始经过一段时间之后运行某个过程,使用TimeValue(time)可以安排在指定的时间运行某个过程。
参数Procedure是必需的,设置要运行的过程名称。
参数LatestTime是可选的,设置过程开始运行的最晚时间。例如将参数LatestTime设置为EarliestTime+10,当时间到了EarliestTime时如果Excel不处于空闲状态,那么Excel 将等待10秒,如果在10秒内Excel不能回到空闲状态,则不运行该过程。如果省略该参数,Excel将一直等待到可以运行该过程为止。
参数Schedule是可选的,如果其值为True(默认值),则安排一个新的OnTime过程,如果其值为False,则清除先前设置的过程。
取消定时的代码如下:
#001 Sub EndTimer()
#002 On Error GoTo Line
#003 Application.OnTime Now + TimeValue("00:00:01"), "StartTimer", , False
#004 Sheet1.Cells(1, 2) = 0
#005 Exit Sub
#006 Line:
#007 MsgBox "请先按[开始]按钮!"
#008 End Sub
代码解析:
EndTimer过程取消StartTimer过程的定时。
第2行代码错误处理语句,因为如果还没有运行StartTimer过程而先运行EndTimer 过程取消定时,程序会提示错误,如图3-2所示,因此使用On Error GoT o Line语句在错误发生时执行第7行代码显示一个如图3-3所示的提示消息框。
图3-2 运行错误
图3-3 提示消息框
第3行代码将StartTimer过程的Schedule参数设置为False,取消定时设置。
技巧4 设置活动打印机的名称
使用Application 对象的ActivePrinter属性可以设置活动打印机的名称,如下面的代码
所示。
#001 Sub myPrinter()
#002 Dim myPrinter As String
#003 myPrinter = "HP LaserJet P1008 在 Ne04:"
#004 Application.ActivePrinter = myPrinter
#005 MsgBox "活动打印机为:" & Left(myPrinter, InStr(myPrinter, "在") - 1) #006 End Sub
代码解析:
myPrinter过程将活动打印机设置为“HP LaserJet P1008”。
第3行代码指定需要设置为活动打印机的名称,第4行代码通过设置Application 对象的ActivePrinter属性将活动打印机设置为“HP LaserJet P1008”。
第5行代码使用消息框显示活动打印机的名称及型号。
运行myPrinter过程结果如图4-1所示。
图4-1 设置活动打印机
技巧5 屏蔽、改变组合键的功能
使用Application 对象的OnKey方法可以屏蔽或改变组合键的默认操作,如下面的代码所示。
#001 Private Sub Workbook_Open()
#002 Application.OnKey "^{c}", "myOnKey"
#003 End Sub
#004 Sub myOnKey()
#005 MsgBox "本工作表禁止复制数据!"
#006 End Sub
代码解析:
第1行到第3行代码工作簿的Open事件,在工作簿打开时使用OnKey方法改变
应用于Application 对象的OnKey方法指定特定键或特定的组合键运行的过程,语法如下:
expression.OnKey(Key, Procedure)
参数expression是必需的,该表达式返回一个Application 对象。
参数Key是必需的,用于表示要按的键的字符串,具体请参阅VBA中的帮助。
参数Procedure是可选的,表示要运行的过程名称的字符串,本示例中将过程名称指定为第4行到第6行代码的“myOnKey”过程,当按下
如果省略Procedure参数,则按下
Application.OnKey "^{c}"
为了不影响其他工作簿的功能,恢复代码就放在工作簿的Deactivate事件中,如下面的代码所示:
#001 Private Sub Workbook_Deactivate()
#002 Application.OnKey "^{c}"
#003 End Sub
代码解析:
当工作簿从活动状态转为非活动状态时恢复
技巧6 设置Excel窗口标题栏
Excel主窗口标题栏默认的名称是“Microsoft Excel”,通过设置Application对象的Caption属性可以改变Excel主窗口的标题栏,如下面的代码所示。
#001 Sub AppCaption()
#002 Application.Caption = "修改标题栏名称"
#003 MsgBox "下面将恢复默认的标题栏名称!"
#004 Application.Caption = Empty
#005 End Sub
代码解析:
第2行代码将Excel窗口标题设置为“修改标题栏名称”,如图6-1所示。
图6-1 设置Excel窗口标题
应用于Application对象的Caption属性设置显示在Microsoft Excel主窗口标题栏中的名称,语法如下:
expression.Caption
第3行代码恢复Microsoft Excel主窗口标题栏中的名称。如果未设置Caption属性("")或将其设置为Empty(表示未初始化的变量值),则本属性返回默认的“Microsoft Excel”。
将Caption属性设置为常数vbNullChar(表示值为0 的字符)可以删除标题栏中的名称,如下面的代码所示。
#001 Sub DleCaption()
#002 Application.Caption = vbNullChar
#003 MsgBox "下面将恢复默认的标题栏名称!"
#004 Application.Caption =Empty
#005 End Sub
代码解析:
第2行代码删除Excel主窗口标题栏,结果如图6-2所示。
图6-2 删除Excel窗口标题栏的名称
技巧7 自定义Excel状态栏
Excel状态栏显示应用程序的当前状态(例如就绪、输入等)或上下文提示信息,通过设置Application对象的Statusbar属性可以修改状态栏,以显示用户自定义的信息,代码如下:
#001 Sub myStatusBar()
#002 Dim rng As Range
#003 For Each rng In Sheet1.Range("A1:D10000")
#004 Application.StatusBar = "正在计算单元格 " & rng.Address(0, 0) & " 的数据..."
#005 rng = 100
#006 Next
#007 Application.StatusBar = False
#008 End Sub
代码解析:
myStatusBar过程在给选定单元格区域赋值的同时,将Excel状态栏中的文字设置为正在赋值的单元格地址。
应用于Application对象的StatusBar属性返回或设置状态栏中的文字,如果需要恢复默认的状态栏文字,将本属性设为False即可。
运行myStatusBar过程Excel状态栏如图7-1所示。
图7-1 自定义Excel状态栏
技巧8 灵活退出Excel
在使用Close方法关闭工作簿时,既使当前只有一个打开的工作簿,也只能关闭工作簿而不能关闭Excel程序,而使用Application对象的Quit方法则会关闭所有打开的工作簿,下面的代码可以做到两者兼顾。
#001 Sub myQuit()
#002 If Workbooks.Count > 1 Then
#003 ThisWorkbook.Close
#004 Else
#005 Application.Quit
#006 End If
#007 End Sub
代码解析:
myQuit过程在关闭Excel程序时根据当前打开的工作簿数量决定采用何种方法关闭工作簿。
第2行代码使用Workbook集合的Count属性判断当前打开的工作簿文件数量。
第3行代码如当前打开两个或两个以上工作簿,使用Close方法关闭代码所在的工作簿。关于Close方法请参阅技巧错误!未找到引用源。。
第5行代码如果当前只有一个打开的工作簿文件则使用Quit方法关闭Excel程序。应用于Application对象的Quit方法退出Excel程序,语法如下:
expression.Quit
参数expression是必需的,返回一个Application对象。
使用Quit方法关闭Excel程序时,如果有未保存的工作簿处于打开状态,则将弹出一个询问是否要保存所作更改的对话框,为避免对话框出现,可在使用Quit方法前保存所有的工作簿,或者将Application对象的DisplayAlerts属性设置为False,在退出Excel程序时,即使有未保存的工作簿,也不会显示对话框,而且不保存就退出。
如果一个工作簿的Saved属性值为True,但是并没有将工作簿保存到磁盘上,则Excel 程序在退出时不会提示保存该工作簿。
技巧9 隐藏Excel主窗口
如果希望在程序启动时或运行过程中隐藏Excel主窗口,有以下几种实现方法。
9-1 设置Application对象的Visible属性
当Application对象的Visible属性设置为False时,Application对象不可见,即能隐藏Excel主窗口,如下面的代码所示。
#001 Private Sub Workbook_Open()
#002 Application.Visible = False
#003 UserForm1.Show
#004 End Sub
代码解析:
代码工作簿的Open事件,在工作簿打开时将Application对象的Visible属性设置为False隐藏Excel主窗口。
显示Excel主窗口的方法是将Application对象的Visible属性重新设置为True。
当工作簿文件打开时,隐藏Excel主窗口,只显示用户登录窗体,如图9-1所示。
图9-1 隐藏Excel主窗口
9-2 将窗口移出屏幕
设置Application对象的Left属性(从屏幕左边界至Microsoft Excel主窗口左边界的距离)和/或Top属性(从屏幕顶端到Microsoft Excel主窗口顶端的距离)将Application对象移出屏幕外,实现隐藏Excel主窗口,如下面的代码所示。
#001 Private Sub Workbook_Open()
#002 Application.WindowState = xlNormal
#003 Application.Left = 10000
#004 UserForm1.StartUpPosition = 2
#005 UserForm1.Show
#006 End Sub
代码解析:
工作簿的Open事件过程,设置Application对象的Left属性为一个大的数值,从而将应用程序窗口移出屏幕。
第2行代码将应用程序窗口设置为正常状态,只有当应用程序窗口正常显示时才能够设置Application对象的Left属性。
第2行代码将Application对象的Left属性设置为一个大的数值,从而隐藏Excel主窗口。
第4行代码设置用户窗体的StartUpPosition属性值为2,使窗体显示在屏幕的中央。StartUpPosition属性返回或设置一个值,用来指定用户窗体第一次出现时的位置,请参阅错误!未找到引用源。。
重新显示Excel主窗口的方法是将应用程序窗口设置为最大化状态代码如下:
Application.WindowState = xlMaximized
当工作簿文件打开时,隐藏Excel主窗口,只显示用户登录窗体,如图9-2所示,与通过设置Visible属性实现的效果不同,设置Left属性在任务栏中仍然会显示应用程序窗口按钮。
图9-2 隐藏Excel主窗口
9-3 设置工作簿作为加载宏运行
利用加载宏不显示工作簿窗口的特点,设置工作簿作为加载宏运行来隐藏工作簿窗口,如下面的代码所示。
#001 Private Sub Workbook_Open()
#002 ThisWorkbook.IsAddin = True
#003 UserForm1.Show
#004 End Sub
代码解析:
工作簿的Open事件,在工作簿打开时设置其IsAddin属性值为True,指定工作簿作为加载宏运行。
当工作簿作为加载宏运行时,将有工作薄窗口不可见的特征,从而实现隐藏工作簿窗口的目的,如图9-3所示。
图9-3 隐藏工作簿窗口
重新显示Excel主窗口的方法是将工作簿的IsAddin属性值设置为False,以显示工作簿窗口。