王佩丰教学课件

高效办公自动化教程Excel VBA为什么选择？Excel VBA提升工作效率Excel VBA能将繁琐重复的工作自动化，将数小时的手动操作缩短至几秒钟，大幅提升办公效率通过简单的编程，可实现数据处理、报表生成、信息分析等任务的自动化，让您的工作效率提升数倍专业讲师引导课程结构总览高级技巧与优化实战案例与应用学习高级编程技巧和性能优化方法，开发专基础语法与入门通过真实工作场景中的案例学习，理论结合业级Excel自动化解决方案从零开始学习VBA编程基础，掌握核心语法实践，提升问题解决能力和基本操作，建立坚实的编程基础第一章基础入门VBA是什么？VBAVisual Basicfor ApplicationsVBA是微软Office套件中内置的编程语言，专为自动化Excel等Office应用程序而设计通过VBA，您可以•自动处理大量数据•创建自定义函数和程序•设计交互式用户界面•与其他应用程序交互即使没有编程基础，您也可以通过录制宏功能，让Excel自动生成VBA代码，这是零基础学习的理想起点第一个程序循环详解VBA For代码示例循环作用上述代码实现在A1:B10区域自动填充数据Sub循环遍历单元格Dim iAs IntegerFor i=1•A列填充数据1到数据10To10Cellsi,

1.Value=数据i•B列填充10到100的数值Cellsi,

2.Value=i*10Next iEnd Sub•自动处理多行数据，避免手动操作逻辑判断语句的使用If多条件判断实例Sub成绩评级Dim iAs IntegerFor i=2To100If Cellsi,

2.Value=90Then Cellsi,

3.Value=优秀ElseIf Cellsi,

2.Value=75Then Cellsi,

3.Value=良好决策逻辑应用ElseIf Cellsi,

2.Value=60Then Cellsi,

3.Value=及格Else Cellsi,If语句在工作流程中的应用

3.Value=不及格End IfNext iEnd Sub•数据分类与筛选•异常数据标记•自动化决策流程•条件格式应用工作簿与工作表操作工作簿操作工作表操作Sub工作簿操作示例新建工作簿Workbooks.Add打开工Sub工作表操作示例激活工作表Sheets销售数据.Activate作簿Workbooks.Open C:\数据\月报.xlsx保存工作簿添加新工作表Sheets.Add After:=SheetsSheets.Count ActiveWorkbook.Save另存为ActiveWorkbook.SaveAs C:\重命名工作表ActiveSheet.Name=汇总报表工作表复制数据\月报副本.xlsx关闭工作簿ActiveWorkbook.CloseEnd Sheets销售数据.Copy After:=SheetsSheets.Count删除Sub工作表Application.DisplayAlerts=FalseSheetsSheet

1.Delete Application.DisplayAlerts=TrueEndSub单元格对象操作

（一）读取与写入数据格式设置Sub单元格数据操作读取单元格值Dim销售额As Double销售额=RangeB

5.Value写入单元格RangeC

5.Value=销售额*

1.1清除内容RangeD

5.ClearContents多种引用方式Cells5,

2.Value=100第5行第2列RangeB5:D

5.Value=200区域赋值End Sub单元格对象操作

（二）合并单元格偏移定位动态范围RangeA1:D

1.Merge取消合并RangeB

2.Offset1,

0.ValueDim lastRowAs LonglastRow=RangeA1:D

1.UnMerge下移一行RangeB

2.Offset0,CellsRows.Count,

1.Value右移一列

1.EndxlUp.RowRangeA1:A RangeB

2.Offset-1,-

1.Value lastRow.Select合并单元格常用于创建标题和表头，使报表更左上一格加美观通过确定数据末尾位置，创建可适应数据量变化的动态范围Offset方法可以灵活定位相对位置的单元格，实现动态操作VBA事件与典型应用工作表事件Private Sub Worksheet_ChangeByVal TargetAs Range当单元格内容改变时触发If Target.Address=$A$1Then MsgBoxA1单元格已修改为:Target.Value End IfEnd SubPrivate SubWorksheet_SelectionChangeByVal TargetAs Range当选择的单元格改变时触发RangeB

1.Value=当前选中:Target.AddressEnd Sub自动化数据校验Private SubWorksheet_ChangeByVal TargetAs Range监控特定区域的数据输入If NotIntersectTarget,RangeB2:B100IsNothing Then检查输入是否为数字If NotIsNumericTarget.Value AndTarget.ValueThen MsgBox请输入有效的数字！,vbExclamation Application.EnableEvents=False Target.Value=Application.EnableEvents=True End If End IfEnd Sub公式在中的应用VBA调用Excel公式Sub使用公式计算直接插入公式到单元格RangeC

1.Formula==SUMA1:B1R1C1引用样式RangeC

2.FormulaR1C1==SUMRC[-2]:RC[-1]跨工作表公式RangeD

1.Formula==Sheet2!A1使用WorksheetFunction对象计算Dim求和结果As Double求和结果=WorksheetFunction.SumRangeA1:B10MsgBox求和结果:求和结果End Sub自定义函数Function计算增值税价格As DoubleAs Double自定义函数，可在工作表中直接使用计算增值税=价格*

0.13EndFunctionSub带参数过程Optional姓名As String=默认用户可选参数示例MsgBox欢迎，姓名！End Sub多文件数据合并实战Sub合并多个Excel文件Dim文件路径As String,文件名As StringDim目标工作簿As Workbook,源工作簿AsWorkbook Dim最后一行As Long,数据行数As Long设置文件夹路径文件路径=C:\月度报表\创建目标工作簿Set目标工作簿=Workbooks.Add最后一行=1获取第一个文件文件名=Dir文件路径*.xlsx循环处理每个文件Do While文件名打开源文件Set源工作簿=Workbooks.Open文件路径文件名获取数据行数数据行数=源工作簿.Sheets

1.UsedRange.Rows.Count复制数据（不包括标题行，从第2行开始）If数据行数1Then源工作簿.Sheets

1.RangeA2:E数据行数.Copy目标工作簿.Sheets

1.RangeA最后一行.PasteSpecial最后一行=最后一行+数据行数-1EndIf关闭源文件源工作簿.Close False获取下一个文件文件名=Dir LoopMsgBox数据合并完成！End Sub实战应用场景•合并多地区销售数据报表•整合不同部门的预算文件•汇总按月划分的财务数据•收集并整理调查问卷结果VBA数组与高级数据结构数组定义与操作性能优化技巧Sub数组示例定义固定大小数组Dim数字1To5As Integer定义动态数组Dim姓名As StringSub优化性能示例关闭屏幕更新Application.ScreenUpdating=False关闭自动计算ReDim姓名1To10赋值操作数字1=100姓名1=张三数组与单元格区域互相转换Dim数据Application.Calculation=xlCalculationManual使用数组处理大量数据Dim数据As VariantDim iAs Long,范围As Variant数据范围=RangeA1:C

10.Value使用For循环遍历二维数组Dim iAs Integer,j AsInteger jAs LongDim开始时间As Double开始时间=Timer从单元格读取到数组数据=For i=1To10For j=1To3Debug.Print数据范围i,j Next j Next iEnd SubRangeA1:C

10000.Value在数组中处理数据For i=1To10000For j=1To3数据i,j=数据i,j*

1.1NextjNexti将数组写回单元格RangeD1:F

10000.Value=数据恢复设置Application.Calculation=xlCalculationAutomatic Application.ScreenUpdating=True MsgBox处理完成，耗时:Timer-开始时间秒End Sub控件使用ActiveX控件属性与事件PrivateSubCommandButton1_Click按钮点击事件MsgBox您点击了按钮！End SubPrivateSubTextBox1_Change文本框内容变化事件Label

1.Caption=当前输入:TextBox

1.TextEnd SubPrivateSubComboBox1_Change组合框选择变化事件Dim选中项As String选中项=ComboBox

1.ValueRangeA

1.Value=选中项End Sub常用ActiveX控件•CommandButton（命令按钮）用户窗体设计创建用户窗体控件布局与属性用户窗体UserForm是VBA中创建专业界面的主要工具，可以实现窗体设计关键点•数据录入与编辑界面•合理规划控件位置与大小•查询与筛选功能•使用标签Label提供说明文字•多步骤操作向导•设置TabIndex属性控制Tab键顺序•自定义对话框•通过GroupBox组织相关控件通过插入用户窗体创建新窗体，然后从工具箱添加各种控件•设置窗体初始位置StartUpPosition事件响应与数据交互用户信息交换技巧变量传递方式Public全局变量As String模块级变量Sub主程序设置全局变量值全局变量=共享数据调用其他过程调用过程通过参数传递Dim本地变量AsString本地变量=参数数据参数传递过程本地变量End SubSub调用过程使用全局变量MsgBox全局变量End SubSub参数传递过程数据As String使用参数值MsgBox数据End Sub实现复杂业务逻辑在大型VBA项目中，合理设计数据传递与通信机制非常重要•使用模块级变量在不同过程间共享数据•利用参数传递实现过程间通信•通过窗体属性传递用户输入信息•使用工作表作为数据中转区域•设计自定义类型存储复杂数据结构ADO操作外部数据连接数据库基础SQL执行与数据导入导出Sub连接数据库示例引用:Microsoft ActiveXData Objectsx.x LibraryDim连接As ADODB.Connection Dim记录集As ADODB.Recordset Dim连接字符串As String设置连接字符串连接字符串=Provider=Microsoft.ACE.OLEDB.

12.0;_Data Source=C:\数据\数据库.accdb创建连接Set连接=New ADODB.Connection连接.Open连接字符串创建记录集Set记录集=New ADODB.Recordset记录集.Open SELECT*FROM客户表,连接清理资源记录集.Close连接.Close Set记录集=Nothing Set连接=NothingEnd Sub图形与图片控件应用动态插入图形对象Sub插入图形示例Dim图形As Shape插入矩形Set图形=ActiveSheet.Shapes.AddShape_msoShapeRectangle,100,100,150,75设置图形属性With图形.Fill.ForeColor.RGB=RGB0,176,

240.Line.ForeColor.RGB=RGB0,112,

192.Line.Weight=

2.TextFrame.Characters.Text=销售报表.TextFrame.Characters.Font.Size=

14.TextFrame.Characters.Font.Bold=True.TextFrame.HorizontalAlignment=xlHAlignCenter EndWith插入图片ActiveSheet.Shapes.AddPicture_Filename:=C:\图片\公司标志.png,_LinkToFile:=False,_SaveWithDocument:=True,_Left:=400,Top:=100,_Width:=100,Height:=50EndSub报表美化与交互设计图形对象应用场景•创建带有公司标志的专业报表•设计交互式仪表板•绘制业务流程图•制作自定义按钮与导航栏•创建数据可视化图表类模块与面向对象编程123类的定义类的实例化面向对象编程优势在VBA中创建类模块（Class Module）在标准模块中使用自定义类•代码模块化，更易维护•提高代码复用性在类模块员工类中定义属性Private p姓名As Sub使用类示例创建类的实例Dim员工1As•数据封装，提升安全性StringPrivate p部门As StringPrivatep薪资As New员工类Dim员工2As New员工类设•更符合现实世界的建模方式Double定义Property Get/Let方法Public Property置属性员工

1.姓名=张三员工

1.部门=销•便于团队协作开发Get姓名As String姓名=p姓名End售部员工

1.薪资=8000员工

2.姓名=PropertyPublic PropertyLet姓名值As String李四员工

2.部门=技术部员工

2.薪资=p姓名=值End Property定义方法Public Function10000调用方法MsgBox员工

1.姓名计算年薪As Double计算年薪=p薪资*12End的年薪是:员工

1.计算年薪MsgBox员工

2.姓Function名的年薪是:员工

2.计算年薪End SubVBA字典对象详解字典基本操作Sub字典基础示例引用:Microsoft ScriptingRuntime Dim词典As Scripting.Dictionary Set词典=NewScripting.Dictionary添加键值对词典.Add Key:=苹果,Item:=Apple词典.Add Key:=香蕉,Item:=Banana词典.Add Key:=橙子,Item:=Orange检查键是否存在If词典.Exists苹果Then MsgBox找到了苹果对应的值:词典苹果EndIf修改值词典香蕉=Yellow Banana遍历所有键值对Dim键As VariantFor Each键In词典.Keys Debug.Print键=词典键Next键删除项词典.Remove橙子清空字典词典.RemoveAllEnd Sub系统开发Excel+AccessAccess数据库基础Excel前端界面Access作为后端数据库的优势Excel作为系统前端的优势•结构化数据存储•熟悉的用户界面•支持复杂查询•强大的数据分析功能•数据完整性保障•灵活的报表生成•多用户访问控制•丰富的图表展示用户界面设计VBA连接与集成创建专业的系统界面通过VBA实现Excel与Access的无缝集成•自定义Ribbon界面•ADO连接技术•交互式窗体设计•SQL语句操作数据•导航系统与权限控制•事务处理与错误处理典型项目案例分享自动报表生成系统数据清洗与分析自动化案例背景某企业每月需要生成30多份不同部门的销售报表，手动操作耗案例背景市场调研公司每周需处理大量问卷数据，格式不统一且存在大时3天量缺失值和异常值VBA解决方案开发一套自动报表生成系统，实现一键生成所有部门报VBA解决方案开发数据清洗与分析工具，自动检测并修复数据问题，统表，包括数据汇总、图表生成、格式美化和自动发送邮件等功能一格式，生成统计报告和可视化图表成果报表生成时间从3天缩短至10分钟，大幅提升工作效率，同时消除了人工操作的错误常见问题与调试技巧错误类型与处理方法Sub错误处理示例On ErrorResume Next忽略错误继续执行或OnError GoTo错误处理跳转到错误处理部分可能出错的代码Workbooks.Open不存在的文件.xlsx检查是否有错误If Err.Number0Then MsgBox发生错误:Err.Description,vbExclamationErr.Clear清除错误EndIfExit Sub正常退出错误处理:MsgBox错误#Err.Number:Err.Description ResumeNext继续执行下一行代码或Exit Sub退出过程EndSub调试工具与技巧断点F9在代码行上设置停止点，程序运行至此暂停单步执行F8逐行执行代码，观察运行过程监视窗口实时查看变量值的变化即时窗口Ctrl+G执行简单代码和查看表达式结果Debug.Print输出调试信息到即时窗口消息框使用MsgBox显示关键点的变量值性能优化与代码规范性能优化关键点关闭屏幕更新Application.ScreenUpdating=False禁用自动计算Application.Calculation=xlCalculationManual•使用数组批量操作单元格，避免频繁访问工作表•使用With结构简化对象引用•合理使用变量类型，如Long代替Integer处理大数据•避免Select/Activate，直接引用对象操作•限制使用循环，尤其是嵌套循环代码规范建议•使用有意义的变量名和过程名，遵循命名规范•添加充分的注释，说明代码功能和复杂逻辑•模块化设计，将功能相关的代码组织到一起•使用Option Explicit强制变量声明•结构化错误处理，避免程序异常崩溃•代码缩进和格式保持一致，提高可读性•及时清理不再使用的变量和对象，避免内存泄漏课程学习资源介绍视频教程课件与代码下载答疑与支持完整的课程视频已上传至网络学习平台，包所有教学资料免费下载多种渠道获取学习帮助含•PPT课件（中文完整版）•在线问答社区•30+小时高清教学视频•示例代码文件（含注释）•微信学习群交流•分章节精细划分，方便查找•练习题与参考答案•定期直播答疑课•实时编码演示与详细讲解•下载地址vba.wangpeifeng.com/files•一对一辅导（付费）•访问链接vba.wangpeifeng.com/videos学员成功故事学员成功案例统计30%85%平均工作时间节省实际应用率学员通过VBA自动化日常工作，平均节省30%的工作时间85%的学员在工作中成功应用所学VBA技能40%获得职位晋升未来学习路径建议1VBA高级应用深入学习VBA的高级主题•高级类和集合2Office集成开发•自定义Ribbon界面•API函数调用扩展到其他Office应用•XML数据处理•Word自动化文档处理推荐资源《Excel VBA高级编程》在线课程•PowerPoint演示文稿生成•Outlook邮件自动化3数据分析与商业智能•Access数据库深入应用结合VBA与现代数据分析工具推荐资源微软官方Office开发文档•Power Query数据处理•Power Pivot数据建模4•DAX函数高级应用Python与Excel集成•Power BI可视化报表探索现代编程语言与Excel的结合推荐资源《数据分析师进阶指南》•Python基础入门•使用xlwings连接Excel•数据科学库Pandas,NumPy•机器学习应用课程总结与回顾VBA基础1语法、循环、判断、对象模型单元格与数据操作2范围操作、格式化、数组应用用户界面与交互3控件、窗体、事件处理数据集成与外部连接4ADO、SQL、多文件处理高级编程与项目开发5类模块、错误处理、性能优化、实战应用通过本课程，您已经掌握了Excel VBA的核心知识和技能，能够将繁琐的手工操作转变为高效的自动化流程VBA的价值不仅在于提升个人工作效率，更在于为企业创造实际价值，减少人力成本，提高数据处理准确性致谢与互动环节联系方式Email:contact@wangpeifeng.com网站:www.wangpeifeng.com微信公众号:Excel大师课堂知识星球:VBA自动化办公王佩丰微软最有价值专家MVPExcel VBA认证讲师十年Office开发经验。