|
VBA菜鸟一枚,从去年开始学习VBA,并且协助部门基于EXCEL开发了一些小工具, 目前运行已经接近尾声, 我想利用这个平台,分享一些通用的代码给大家,本人经验有限,不喜勿喷,恳请各位老师赐教,有好的建议一定感谢。
如下:
BeginCode 为关闭屏幕刷新,自动启动的,换言之就是关闭一些应用,从而提高代码的运行速度
Sub BeginCode()
'关闭屏幕刷新
Application.ScreenUpdating = False
'保存当前事件设置状态
EventState = Application.EnableEvents
'关闭自动启动事件
Application.EnableEvents = False
'保存当前计算模式
CalculationState = Application.Calculation
'将计算模式设置为手动
Application.Calculation = xlCalculationManual
'保存当前显示分页符设置
PageBreakState = ActiveSheet.DisplayPageBreaks
'关闭显示分页符
ActiveSheet.DisplayPageBreaks = False
End Sub
EndCode 表示把前面关闭的项目打开;
Sub EndCode()
'恢复原始设置
ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalculationState
Application.EnableEvents = EventState
Application.ScreenUpdating = True
End Sub
UserForm_Initialize1 & Sub KillForm1() 这2段代码需要结合使用, 可以自动关闭打开的用户窗口。
在我的excel 中设置了2个用户窗口,所以有1&2区分;
timevalue 可以设置代码运行结束后窗口停留的时间,这里设置的是2秒,数值可以任意修改
Sub UserForm_Initialize1()
Application.OnTime Now + TimeValue("00:00:02"), "KillForm1"
End Sub
Sub UserForm_Initialize2()
Application.OnTime Now + TimeValue("00:00:02"), "KillForm2"
End Sub
Sub KillForm1()
Unload UserForm1
End Sub
Sub KillForm2()
Unload UserForm2
End Sub
- Public CalculationState As Long
- Public EventState As Boolean
- Public PageBreakState As Boolean
-
- Sub BeginCode()
- '关闭屏幕刷新
- Application.ScreenUpdating = False
- '保存当前事件设置状态
- EventState = Application.EnableEvents
- '关闭自动启动事件
- Application.EnableEvents = False
- '保存当前计算模式
- CalculationState = Application.Calculation
- '将计算模式设置为手动
- Application.Calculation = xlCalculationManual
- '保存当前显示分页符设置
- PageBreakState = ActiveSheet.DisplayPageBreaks
- '关闭显示分页符
- ActiveSheet.DisplayPageBreaks = False
- End Sub
- Sub EndCode()
- '恢复原始设置
- ActiveSheet.DisplayPageBreaks = PageBreakState
- Application.Calculation = CalculationState
- Application.EnableEvents = EventState
- Application.ScreenUpdating = True
- End Sub
- Sub UserForm_Initialize1()
- Application.OnTime Now + TimeValue("00:00:02"), "KillForm1"
- End Sub
- Sub UserForm_Initialize2()
- Application.OnTime Now + TimeValue("00:00:02"), "KillForm2"
- End Sub
- Sub KillForm1()
- Unload UserForm1
- End Sub
- Sub KillForm2()
- Unload UserForm2
- End Sub
复制代码
|
|