(1条消息) Excel中VBA编程学习笔记（六）
1、基本属性设置
Sub main()
Application.ScreenUpdating = False '禁止屏幕更新
Application.ScreenUpdating = True '允许屏幕更新
Application.DisplayAlerts = False '禁止出现提示对话框
Application.DisplayAlerts = True '允许出现提示对话框
Application.DisplayFullScreen = True '1. 设置EXCEL为全屏模式
Application.Visible = False '隐藏excel应用
Application.EnableEvents = False '禁用对象事件(不会触发任何excel事件)
Application.Interactive = False '禁止用户干预宏代码的执行(禁止所有的输入和鼠标操作)
End Sub
注：禁用对象事件后，在代码结束位置应该回复该属性为true.
2、显示活动打印机名称
Private Sub test()
MsgBox ("当前活动打印机名为：" & Application.ActivePrinter)
End Sub
3、调用另存为对话框
Private Sub test()
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Sub test()
Filename = "新的工作薄"
Application.Dialogs(xlDialogSaveAs).Show arg1:=Filename & ".xls" '初始化保存的文件名
End Sub
4、检查并显示编辑栏
Private Sub test()
If Application.DisplayFormulaBar = False Then
Application.DisplayFormulaBar = True
End If
End Sub
5、显示或隐藏Excel的滚动条
Private Sub test()
If Application.DisplayScrollBars = False Then
Application.DisplayScrollBars = True
Else
MsgBox ("已显示滚动栏")
End If
End Sub
6、设置Excel窗口的大小和位置
Sub test()
With Application
.Left = 10
.Top = 0
.Width = 500
.Height = 300
End With
End Sub
7、返回窗口状态并设置窗口是否最小化
Sub test()
Dim tempt
MsgBox ("Excel状态：" & Application.WindowState)
tempt = MsgBox("需要将Excel应用最小化？", vbYesNo)
If tempt = vbYes Then
Application.WindowState = xlMinimized
End If
End Sub
WindowState属性取值
|
值名称 |
值 |
说明 |
|
xlMaxmized |
-4137 |
最大化 |
|
xlminmized |
-4140 |
最小化 |
|
xlNormal |
-4143 |
正常 |
8、在VBA中使用工作表函数
Sub test()
Dim myRange As Range
Set myRange = Worksheets("sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox (answer)
End Sub
9、使用“打开文件”对话框
Sub test()
Dim tmp As Boolean
tmp = Application.FindFile
If tmp = True Then
MsgBox ("成功打开")
Else
MsgBox ("打开失败")
End If
End Sub
10、为宏过程指定快捷键
Private Sub a()
MsgBox ("模块a运行")
End Sub
Private Sub b()
MsgBox ("模块b运行")
End Sub
Private Sub test()
Application.OnKey "^{a}", "a"
Application.OnKey "^{b}", "b"
End Sub
在指定组合键时候，Shift，Ctrl，Alt分别由以下符号代替
|
Shift |
+ |
|
Ctrl |
^ |
|
Alt |
% |
12、在将来特定时间运行模块
Private Sub a()
MsgBox ("模块a运行")
End Sub
Private Sub b()
MsgBox ("模块b运行")
End Sub
Private Sub test()
Dim tmp
Application.OnTime Now + TimeValue("00:00:10"), "a" '10秒后运行模块a
tmp = MsgBox("是否取消设置？", vbYesNo)
If tmp = vbYes Then
Application.OnTime Now + TimeValue("00:00:10"), "a", ,False
End If
End Sub
13、退出Excel
Private Sub test()
Dim tmp
Application.OnTime Now + TimeValue("00:00:10"), "a" '10秒后运行模块a
tmp = MsgBox("是否退出Excel？", vbYesNo)
If tmp = vbYes Then
Application.DisplayAlerts = False '不提示保存对话框
Application.SaveWorkspace '保存工作空间
Application.Quit
End If
End Sub
注：如果将DisplayAlters属性设置为False，则即使有未保存的工作薄，也不会显示对话框，而且不保存就退出。
14、状态栏
Sub main()
With Application
.DisplayStatusBar = True
.StatusBar = "正在保存文件……"
.StatusBar = False '恢复系统默认文本
End With
End Sub
15、定时器
Dim my_datetime As Date
Sub RunTimer()
my_datetime = Now() + TimeValue("00:00:01")
Application.OnTime my_datetime, procedure:="my_procedure" '在指定的时间my_datetime 执行设定的过程my_procedure
End Sub
Sub my_procedure()
ActiveSheet.Range("H1") = Format(Time(), "hh:mm:ss")
RunTimer
End Sub
Sub KillTimer()
Application.OnTime my_datetime, procedure:="my_procedure", schedule:=False '在指定的时刻取消设定的过程
End Sub
注：aplication的ontime最小精度为1s。
16、计时器
Dim my_date As Date
Sub main()
my_date = Now() + TimeValue("00:00:18")
Call test
End Sub
Sub test()
If Now() >= my_date Then
MsgBox "时间到"
'Application.OnTime theTime, "test"
Exit Sub
End If
ActiveSheet.Range("H2") = ActiveSheet.Range("H2") + 1
theTime = Now() + TimeValue("00:00:01")
Application.OnTime theTime, "test"
End Sub
注：执行18秒后停止。
17、宏代码执行的暂停
【例1】窗体3秒后自动关闭
Private Sub UserForm_Activate()
Application.Wait Now() + VBA.TimeValue("00:00:03")
Unload Me
End Sub
- 调用变量名执行指定的宏过程
Sub test()
Application.Run "fun1"
Application.Run "fun2", "2", "30"
result = Application.Run("fun2", "2", "7")
End Sub
Sub fun1()
Debug.Print "Hello"
End Sub
Sub fun2(num1 As Integer, num2 As Integer)
Debug.Print num1 + num2
fun2 = num1 * num2
End Sub
18、捕获用户中断
当用户按下Esc或者Ctrl+Break时会发生中断，可以通过Application的EnableCancelKey属性设置自定义取消处理程序的方法。
Sub test()
On Error GoTo HadleCancel '此句不可少
Application.EnableCancelKey = xlErrorHandler
For i = 1 To 100000
Range("D1") = i
Next
HadleCancel:
If Err.Number = 18 Then
MsgBox "用户终止了代码运行", vbExclamation
End If
End Sub
注：在程序执行的过程中，捕获到用户取消键时将产生一个错误代码为18的错误。