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

38第5章Application（应用程序）对象范例57取得Excel版本信息SubExcelVersion()DimMyVersionAsStringSelectCaseApplication.VersionCase8.0MyVersion=97Case9.0MyVersion=2000Case10.0MyVersion=2002Case11.0MyVersion=2003Case12.0MyVersion=2007CaseElseMyVersion=未知版本EndSelectMsgBoxExcel的版本是：&MyVersionEndSub范例58取得当前用户名称SubToUserName()MsgBox当前用户是：&Application.UserNameEndSub范例59简单的计时器SubStartTimer()Sheet1.Cells(1,2)=Sheet1.Cells(1,2)+1Application.OnTimeNow+TimeValue(00:00:01),StartTimerEndSub39SubEndTimer()OnErrorResumeNextApplication.OnTimeNow+TimeValue(00:00:01),StartTimer,,FalseSheet1.Cells(1,2)=0EndSub范例60屏蔽、更改组合键功能PrivateSubWorkbook_Activate()Application.OnKey^{c},MyOnKeyEndSubSubMyOnKey()MsgBox禁止使用Ctrl+C复制数据!EndSubPrivateSubWorkbook_Deactivate()Application.OnKey^{c}EndSub范例61设置Excel标题栏SubModifyTheTitle()Application.Caption=修改标题栏EndSubSubRemoveTheTitle()Application.Caption=vbNullCharEndSubSubRrestoreTheTitle()Application.Caption=EmptyEndSub范例62自定义Excel状态栏SubMyStatusBar()DimrAsLongDimiAsLong40WithSheet1r=.Cells(.Rows.Count,1).End(xlUp).RowFori=1Tor.Cells(i,3)=.Cells(i,1)+.Cells(i,2)Application.StatusBar=正在计算&.Cells(i,3).Address(0,0)&的数据...NextEndWithApplication.StatusBar=FalseEndSub范例63灵活关闭ExcelSubFlexibleClose()IfWorkbooks.Count1ThenThisWorkbook.CloseElseApplication.QuitEndIfEndSub范例64暂停代码的运行PrivateSubUserForm_Activate()DimiAsIntegerFori=1To10Label1.Caption=这是个演示窗体,将在&11-i&秒后自动关闭!Application.WaitNow()+TimeValue(00:00:01)DoEventsNextUnloadMeEndSubPrivateDeclareSubSleepLibkernel32(ByValdwMillisecondsAsLong)PrivateSubUserForm_Activate()DimStrAsStringDimiAsIntegerStr=这是一个模拟打字效果的演示。Fori=1ToLen(Str)TextBox1=Left(Str,i)Sleep400DoEvents41NextEndSub范例65防止用户中断代码运行SubProhibitionEsc()DimiAsIntegerApplication.EnableCancelKey=xlDisabledFori=1To10000Cells(1,1)=iNextEndSub范例66隐藏Excel主窗口66-1设置Visible属性为FalsePrivateSubWorkbook_Open()Application.Visible=FalseUserForm1.ShowEndSub66-2将窗口移出屏幕PrivateSubWorkbook_Open()Application.WindowState=xlNormalApplication.Left=10000UserForm1.StartUpPosition=2UserForm1.ShowEndSub