第5章--Application(应用程序)对象代码【超实用VBA】

1、第5章 Application（应用程序）对象范例57 取得Excel版本信息Sub ExcelVersion() Dim MyVersion As String Select Case Application.Version Case 8.0 MyVersion = 97 Case 9.0 MyVersion = 2000 Case 10.0 MyVersion = 2002 Case 11.0 MyVersion = 2003 Case 12.0 MyVersion = 2007 Case Else MyVersion = 未知版本 End Select MsgBox Excel 的版本是

2、： & MyVersionEnd Sub范例58 取得当前用户名称Sub ToUserName() MsgBox 当前用户是： & Application.UserNameEnd Sub范例59 简单的计时器Sub StartTimer() Sheet1.Cells(1, 2) = Sheet1.Cells(1, 2) + 1 Application.OnTime Now + TimeValue(00:00:01), StartTimerEnd SubSub EndTimer() On Error Resume Next Application.OnTime Now + TimeValue(0

3、0:00:01), StartTimer, , False Sheet1.Cells(1, 2) = 0End Sub范例60 屏蔽、更改组合键功能Private Sub Workbook_Activate() Application.OnKey c, MyOnKeyEnd SubSub MyOnKey() MsgBox 禁止使用复制数据!End SubPrivate Sub Workbook_Deactivate() Application.OnKey cEnd Sub范例61 设置Excel标题栏Sub ModifyTheTitle() Application.Caption = 修改标题

4、栏End SubSub RemoveTheTitle() Application.Caption = vbNullCharEnd SubSub RrestoreTheTitle() Application.Caption = EmptyEnd Sub范例62 自定义Excel状态栏Sub MyStatusBar() Dim r As Long Dim i As Long With Sheet1 r = .Cells(.Rows.Count, 1).End(xlUp).Row For i = 1 To r .Cells(i, 3) = .Cells(i, 1) + .Cells(i, 2) Ap

5、plication.StatusBar = 正在计算 & .Cells(i, 3).Address(0, 0) & 的数据. Next End With Application.StatusBar = FalseEnd Sub范例63 灵活关闭ExcelSub FlexibleClose() If Workbooks.Count 1 Then ThisWorkbook.Close Else Application.Quit End IfEnd Sub范例64 暂停代码的运行Private Sub UserForm_Activate() Dim i As Integer For i = 1 To

6、 10 Label1.Caption = 这是个演示窗体,将在 & 11 - i & 秒后自动关闭! Application.Wait Now() + TimeValue(00:00:01) DoEvents Next Unload MeEnd SubPrivate Declare Sub Sleep Lib kernel32 (ByVal dwMilliseconds As Long)Private Sub UserForm_Activate() Dim Str As String Dim i As Integer Str = 这是一个模拟打字效果的演示。 For i = 1 To Len(

7、Str) TextBox1 = Left(Str, i) Sleep 400 DoEvents NextEnd Sub范例65 防止用户中断代码运行Sub ProhibitionEsc() Dim i As Integer Application.EnableCancelKey = xlDisabled For i = 1 To 10000 Cells(1, 1) = i NextEnd Sub范例66 隐藏Excel主窗口66-1 设置Visible属性为FalsePrivate Sub Workbook_Open() Application.Visible = False UserForm1.ShowEnd Sub66-2 将窗口移出屏幕Private Sub Workbook_Open(