Excel_VBA开发与实例教学提纲.ppt

VBA控制结构 循环结构 For 〈变量〉=〈初值〉 To 〈终值〉 Step 〈步长〉 〈语句块〉 Next 〈变量〉 For Each 〈对象变量〉 In 〈对象集〉 ? ? 〈语句块〉Next 〈对象变量〉 Do While 〈条件表达式〉 〈语句块〉 Loop 示例：在Excel中打印工资表 Sub InsertTitle() Selection.CurrentRegion.Select Cells(Selection.Row, Selection.Column).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy ActiveCell.Offset(2, 0).Range(A1).Select Do Until ActiveCell = Selection.Insert Shift:=xlDown Range(Selection, Selection.End(xlToRight)).Select Selection.Copy ActiveCell.Offset(2, 0).Range(A1).Select Loop Application.CutCopyMode = False End Sub 示例：对话框与外部应用程序 Private Sub donkey_Click() MsgBox ( I am a donkey ) End Sub Private Sub Wabbit_Click() Call PlaySound(ActiveDocument.Path + \wabbit.wav) End Sub Private Sub turtle_Click() Const Answer = TURTLE Dim Tries As Integer Dim Message, Title, Default0, MyValue As String Tries = 0 Message = What am I? Set prompt. Title = What am I? Set title. Default0 = ****** Set default for input field. Question: Tries = Tries + 1 If Tries = 3 Then Default0 = T***** End If If Tries = 4 Then Default0 = TUR*** End If MyValue = InputBox(Message, Title, Default0) MyValue = UCase(MyValue) Convert to uppercase …… 示例：保存并关闭系统 Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Dim abc abc = MsgBox(您确认要关闭本系统吗？, vbQuestion + vbYesNo + vbDefaultButton2, 确认) If abc = vbYes Then Worksheets(股票收益计算器).Unprotect Password:=1 Worksheets(股票收益计算器).Range(G13).FormulaR1C1 = 0 Worksheets(股票收益计算器).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=1 Sheets(说明).Select ActiveWorkbook.Save Else Cancel = True End If End Sub 程序编写的特殊考虑 如何启动特定版本的应用程序 CreateObject 如何使用当前的应用程序 GetObject 如何结束应用程序 Application.Quit 如何清除内存的占用 Set appObject = Nothing 挑战第三关 优化与部署 创建用户窗体 提示使用者选取、输入或浏览数据 使用控件 文本框输入文字 列表框选取内容 选择框选取一个或多个功能 用户窗体窗口的操作 建立用户窗体 配置ActiveX控件 设置窗