第5章--Application(應(yīng)用程序)對象代碼【超實用VBA】

1、第5章 Application（應(yīng)用程序）對象范例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 禁止使用復(fù)制數(shù)據(jù)!End SubPrivate Sub Workbook_Deactivate() Application.OnKey cEnd Sub范例61 設(shè)置Excel標題欄Sub ModifyTheTitle() Application.Caption = 修改標題

4、欄End SubSub RemoveTheTitle() Application.Caption = vbNullCharEnd SubSub RrestoreTheTitle() Application.Caption = EmptyEnd Sub范例62 自定義Excel狀態(tài)欄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) & 的數(shù)據(jù). Next End With Application.StatusBar = FalseEnd Sub范例63 靈活關(guān)閉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 & 秒后自動關(guān)閉! 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 設(shè)置Visible屬性為FalsePrivate Sub Workbook_Open() Application.Visible = False UserForm1.ShowEnd Sub66-2 將窗口移出屏幕Private Sub Workbook_Open(