(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

  1. 调用变量名执行指定的宏过程

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的错误。

(0)

相关推荐