VBA培训材料.pdf

《VBA培训材料.pdf》由会员分享，可在线阅读，更多相关《VBA培训材料.pdf（18页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、上海宝信软件股份有限公司VBA 学习培训材料上海宝信软件股份有限公司2007 年 10 月 11 日上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 1 页 共 17 页序言ExcelVBA 基础知识体系基本上由编程语法和对象模型两大部分组成。如下图所示。第一部分：编程语法与其它的程序设计语言一样，VBA 也有基本的数据类型和程序的基本语句结构，例如IF,Then 语句结构、For,Next 语句结构等，并且VBA 有其特有的数据类型，如Object 类型，以及特殊的语句构造，如With,End With 语句、F

2、or Each,Next 语句。此外，子过程和函数过程也有它们自身的特点，如传递参数和返回值的规则、调用方式等。一般来说，语法是基础。在学习 VBA 程序设计的语法时，除理解一般程序设计语言所通用的语法特点外，要重点关注VBA 特有的语法及其规则。一、变量、数据类型和常量1、变量VBA 的主要目的就是处理数据。某些数据存在于对象中，比如工作表的单元格区域内，其他的数据存储在变量中。“变量”只是命名的位于计算机内存中的存储位置。变量可以接纳很多种数据类型，从简单的布尔变量值（True 或者 False）到复杂的双精度值。给变量赋值的时候使用等号运上海宝信软件股份有限公司-培训材料上海宝信软件股份

3、有限公司Shanghai Baosight Software Co.,Ltd第 2 页 共 17 页算符。正如其他编程语言一样，变量的命名方式是由字母、数字和下划线组成，必须以字母开头。同时，VBA 中也有很多的保留字，如for，next 等等，这些单词不能用在变量或者过程的名称中。如果想使用其中的一个作为名称将得到一个错误消息。如果不为VBA 例程中使用的某个变量声明数据类型，那么VBA 将将使用默认的数据类型 Variant。存储为Variant 数据类型的数据行为根据所处理的内容不同，它将改变数据的类型。2、数据类型因为 VBA 可以自动处理运用数据时设计到的所有细节，所以会使得编程人员

4、更省事，不是所有的编程语言都能够这样令人省事。例如，某些语言对于键入的内容要求极其严格，这意味着编程人员必须显示定义每个变量所使用的数据类型。数据类型是指如何将数据存储在内存中，比如整数。实数和字符串等。尽管VBA 可以自动维护数据的键入操作，但是这将导致执行的速度更慢、对内存的使用不够有效（因此，建议大家严格定义数据类型）。一般来讲，最好使用占用字节最少却能够处理所有赋值给它的数据的数据类型。当 VBA使用了数据的时候，执行的速度和VBA 为其配置的字节数有关。换句话说，数据使用的字节数越少，VBA 可以访问和处理数据的速度就越快。可以使用VBA 的 TypeName 函数来确定变量的数据类

5、型。在过程中使用每个变量之前，对变量进行声明是一个非常好的习惯，对变量的声明将告诉 VBA 变量的名称和数据类型。声明变量能带来两个好处:程序运行得更快并能够更有效地使用内存：默认的数据类型Variant 将导致 VBA 重复执行耗时的检查和保留比较多的内存。如果VBA 知道数据类型，并不必进行检查，而且可以保留刚好足够的内存来存储数据。避免出现与拼写错误变量名称有关的问题：如果使用 Option Explict 强制声明所有的变量，那么当一个未知的变量出现时编译器会发出一个错误消息。3、常量在过程执行的时候，变量的值也许（通常都是）会发生变化，有时候需要引用从不发生改变的值或者字符串即“常量

6、”使用 const 语句来声明常量，在代码中使用常量来代替编码的值或者字符串是个非常好的编程习惯。同时可以使用预定义的常量。Excel 和 VBA 提供了很多预定义的常量，不用声明就可以使用，甚至不需要知道这些值就可以使用它们。通常，宏录制器使用常量而不是实际的值。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 3 页 共 17 页二、程序语句结构1、With-End With 构造With-End With 指令构造允许在单个对象上执行多项操作。具体例子如下：With Application.CommandBa

7、rs(Application.CommandBars(Chart).Caption=更改图表选项.OnAction=mod_chart_sourcedata.Style=msoButtonCaption End With2、For each-Next 构造集合是一组相关的对象。例如，Worksheets 集合是工作薄中所有的worksheet 对象的集合。还可以使用很多其他的集合，使用 For Each-Next 构造时，不必知道集合中有多少元素。具体例子如下：Dim sht as worksheet Dim row as Integer For Each sht In Workbooks(a

8、g.xls).Worksheets For row=1 To sht.UsedRange.Rows.Count If IsNumeric(sht.Range(A&row)Then sht.Range(A&row)=End If Next row Next sht 3、控制执行一些 VBA 代码从最上面一行的代码执行到最下面一行代码。例如，录制的宏通常就以这种方式工作。然而经常必须控制例程的流程，跳过某些语句、多次执行某些语句以及测试条件以决定例程接下来要做什么。在上面提到的For Each-Next 构造，这是一种循环构造。另外控制VBA 过程执行的还有以下方式：GoTo语句If-Then语句

9、Select Case语句For-Next循环Do While 循环Do Until循环正如其他编程语言一样，以上方式构成了VBA 程序的条件结构和循环结构。可以参考相关书籍来查阅这些结构的使用方法，这里不做赘述。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 4 页 共 17 页三、Sub 过程和 Function过程过程是一系列位于VBA 模块中的VBA 语句，在VBE 中可以访问VBA 模块。模块可以保护任意数量的过程。有很多方式可以调用或者执行过程，从头到尾执行过程中的代码，但是也可能中途结束过程的执行。

10、函数就是返回一个值的VBA 过程。在VBA 代码或者公式中使用这些函数。VBA 允许创建 Sub 过程和 Function 过程。可以将Sub 过程视为可以有用户或者另一个过程执行的命令。而Function 过程通常是返回一个值（或者一个数组）。1、Sub过程的声明与执行用 Sub 关键字声明Sub 过程。通过关键字private 和 public 来声明 sub 过程的作用域。过程的作用域也决定了那些其他的过程可以调用它。正如由关键字的语义所表示的那样，public 代表过程是公共的，也就是说，工作薄中的任意模块中的过程都可以使用。默认情况下，过程是公共的。Private 代表过程是私有的，

11、对于私有的过程只允许同一个模块中的其他过程调用，其他模块中的过程不能调用它。可以有很多方式来执行Sub 过程，诸如使用“运行子过程/用户窗体”命令（在 VBE 中）或者按下F5 键、从宏对话框中执行过程、用Ctrl 键组合执行过程、在自定义菜单中执行过程等。2、Sub过程中参数的传递过程中参数提供了它的指令中要使用的数据。通过参数传递的数据可以为下列任何一种：变量常量数组对象关于参数，过程在以下方面非常类似于工作表函数：过程可能不需要任何参数过程可能需要固定数量的参数过程可能接受不定数量的参数过程可能必需某些参数，而另一些是可选的参数过程的所有的参数可能都是可选的。可以通过两种方式将参数传递给

12、过程，分别是通过引用或者值传递。通过引用传递参数（默认的方式）只是传递变量的内存地址。而通过值传递参数是传递原始变量的副本，因此在过程中对参数的修改不会影响到原始的变量。如果不希望被调用的过程修改任何作为参数传递的变量值，那么可以修改被调用的过程的参数列表，使得通过“值”而不是“引用”传递参数的值。为此，在参数的值前面加上ByVal 关键字。这种方法导致被调用的例程使用的是被传递变量的副本而不是变量本身。具体例子如下：上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 5 页 共 17 页例一：Sub Process

13、(YourV alue)YourV alue=YourValue*10 End Sub 例二：Sub Process(ByVal YourValue)YourV alue=YourValue*10 End Sub 假如 YourValue 的初始值为1，通过调用例一中Process过程后 YourValue 的值为 10，通过调用例二中Process过程后 YourValue 的值为 1，值保持不变。在大多数情况下，将使用默认的引用方法传递参数。然而，如果过程必须使用在参数中传递给它的数据，但是又绝对不能修改原始的数据，那么可以通过值来传递参数。3、Function 过程的声明和执行自定义的F

14、unction 过程有很多地方都与Sub 过程相似（见前面描述）。为了创建自定义函数，首先要插入一个VBA 模块（或者使用已有的模块）。然后输入关键字Function 和函数名称并用括号括起参数列表。还可以通过使用As 关键字声明返回的数据类型（可选的动作，但是建议这么做）。然后插入VBA 代码以便执行任务，还要确保在Function 过程中至少一次将适当的值赋给函数的名称，最后用End Function 语句来结束函数。和 Sub 过程一样，Function 函数也有自己的作用域，对于函数的作用域，要记住一下几点:如果不声明函数的作用域，那么默认作用域应该是Public。声明为 As Pri

15、vate 的函数不会出现在Excel 的“粘贴函数”对话框中。因此，当创建只用在某个VBA 过程的函数时，应将其声明为Private，这样用户就不能在公式中使用它。如果 VBA 代码需要调用在另一个工作薄中定义的某个函数时，可以设置对其他工作薄的引用。虽然可以有很多方式来执行Sub 过程，但是只能有两种方式来执行Function 过程：从另一个过程中调用它在工作表公式中使用它4、Function 过程中参数的传递关于 Function 过程的参数，记住一下几点:参数可以是变量（包括数组）、常量、字面量或者表达式。某些函数没有参数。某些函数有固定数量的必须参数。某些函数既有必需的参数，又有可选的

16、参数。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 6 页 共 17 页第二部分：Excel 的对象模型这是 ExcelVBA 编程的核心。Excel 本身是由对象结成的，使用VBA 对 Excel 进行操作，就是对Excel 的对象的操作。在这里，要学习Excel 对象的方法、属性和事件，首先要理解对象、方法和属性之间的关系。Excel 的对象模型提供了大量的对象，想一下子学习并理解这么多对象及它们的成员是不可能的，可能绝大多数人都不可能。但事情往往是这样的，一门知识虽然包含的东西很多，但经常使用的总是那其中

17、的一些知识点。因此，可以先对其中经常使用到的对象进行研究、深入的学习和理解。通过这种方式，逐步加深对Excel 对象模型的认识，从而渐渐地掌握和灵活运用Excel 的对象模型。按照层次结构来学习Excel 对象模型，这个模型的顶层就是Application 对象，在这里就是 Excel 本身。但是如果在Word 中用 VBA 编程，那么Application 对象就是Word。一、对象层次结构Application 对象（也就是Excel）包含其他的对象。它包含的对象如下:Workbooks(所有 Workbook 对象的集合)Windows（所有 Window 对象的集合）AddIns（所有

18、 AddIn 对象的集合）某些对象可以包含其他的对象。例如，Workbooks 集合由所有打开的Workbook 对象组成，Workbook 对象有可以包含其他的对象，比如：Worksheets（Worksheet 对象的集合）Charts（Chart 对象的集合）Names(Name 对象的集合)同时，每一种这些对象有可以包含其他的对象。Worksheets 集合由 Workbook 中所有Worksheet 对象组成。Worksheet 对象有可以包含很多其他的对象，包含以下的对象：ChartObjects（ChartObject对象的集合）RangePageSetupPivotTable

19、s(PivotTable对象的集合)通过上面的例子，可以看出整个Excel 对象模型是一个层次性的结构。二、Excel 对象的属性、方法和事件1、关于集合在 VBA 程序设计中，集合是一个重要的概念。集合是一组属于同一类的对象，集合本身也是一个对象。正如前面所述，Workbooks 是当前打开的所有Workbook对象的集合。Worksheets 是包含在某个特殊的Workbook 对象中所有的Worksheet 对象的集合。可以处理对象的整个集合或者集合中的某个单独的对象。为了引用集合中某个对象，就将这个对象的名称或者索引号放在集合名称后面的括号中，如下所示：上海宝信软件股份有限公司-培训材

20、料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 7 页 共 17 页Worksheets（“Sheet1”）如果 sheet1 是集合中的第一个工作表，可以通过其索引号来加以引用。Worksheets（1）如果形式引用中的第二个工作表，依次类推。2、对象的属性、方法每个对象都有属性，例如，Comment 对象有个属性叫“Author”，返回或设置批注的作者。String类型，可读写。可以编写VBA代码来显示 Author 属性，或者编写 VBA代码来设置该属性的值。具体例子如下：Sub ShowandSetAuthor()Msgbox Work

21、sheets(1).Comments(2).Author Worksheets(1).Comments(2).Author=”小小”End Sub 当使用 VBA 代码来引用对象的属性（或者方法、事件）时，通常必须通过用句点（就是通常所说的点运算符）来连接对象的属性（或者方法、事件）。除了属性以外，对象还有方法。所谓方法，就是在对象上执行的某个动作。例如，Comment 对象有个方法叫“Text”，返回或者设置批注中的文本（包含3 个参数）。在下面的语句显示了包含在第一个工作表上第一个批注中的文本：Msgbox Worksheets(1).Comments(1).Text 3、对象的事件Exc

22、el 可以监视很多不同的事件，这些事件可以分为一下几类：工作薄事件：这种事件针对某个特殊的工作薄发生的。这些事件包括Open（打开或者创建工作薄），BeforeSave（准备保存工作薄）和NewSheet（添加新的工作表）工作表事件：这种事件针对某个特殊的工作表发生的。这些事件包括Change（更改工作表上的某个单元格的内容），SelectionChange（用户移动单元格指示器）和 Calculate（重算工作表）图表事件:这种事件针对某个特殊的图表发生的。这些事件包括Select（选中图表中某个对象）等。应 用程序 事件：这种事件针对应用程序（Excel）发生的。这些 事件包括NewWor

23、kbook（创建新的工作薄），WorkbookBeforeClose（准备关闭任意的工作薄）和SheetChange（改变了任意了任意打开的工作薄中某个单元格的值）用户窗体事件：这种事件针对某个特殊的用户窗体或者用户窗体上包含的某个对象发生的。这些事件包括用户窗体的Initialize 事件（在显示用户窗体之前发生），用户窗体上的命令按钮的Click 事件（当单击按钮时发生）没有与对象关联的事件：它们包含OnTime 事件（设置定时器）和OnKey 事件（定制特定键的处理方式）正如看到的那样，某些动作会触发多个事件。例如，当在工作薄中插入一个新的工作表上海宝信软件股份有限公司-培训材料上海宝信

24、软件股份有限公司Shanghai Baosight Software Co.,Ltd第 8 页 共 17 页时，这个动作就会触发3 个应用程序级别的事件：WorkbookNewSheet：当添加新的工作表时发生这种事件。SheetDeactivate：当使得活动工作表处于非活动状态时发生这种事件。SheetActivate：当激活最新添加的工作表时发生这种事件。上面列出的事件都是应用程序级别的事件。当添加新的工作表时，还会发生在工作薄级别以及工作表级别的事件。这里，只要记住是按照某种特殊的顺序触发事件就行了，在编写事件处理程序过程的时候，了解事件发生的顺序才是关键。在 VBE 窗口中，每个工程

25、都列在“工程”窗口中。工程的组件都列在可折叠的列表中，每个组件都有自己的代码模块，绝不要把事件处理程序放在通用的（也就是非对象的）模块中。虽然时间处理程序过程必须放在正确的模块中，但是这种过程仍然可以调用存储在其他模块中的其他标准的过程。默认情况下，所有的事件都是启用的。如果要禁用所有的事件，可以执行下列VBA 指令：Application.EnableEvents=False 使用下列语句启用事件：Application.EnableEvents=True 第三部分、结论最后，通过上述两部分的组合，就形成了VBA 程序。也就是说，当理解和掌握了VBA程序设计的基本语法和Excel 的对象模型

26、之后，通过使用VBA 语法控制和操作Excel 对象，一系列的指令就组成了ExcelVBA 程序。当程序按照需求进行设计时，它就完成了对Excel进行自动化操作的功能，或者是增强了Excel 原有的功能，或者是实现了Excel 原来没有的功能。因此，根据上面所讲述的ExcelVBA基本知识组成结构的特点，可以按此主线进行学习，即先学习VBA 的基本语法，再理解 Excel 的对象模型，最后自然就会编写出ExcelVBA程序来。当然，如果已经对程序设计语言的语法比较熟悉，可以学习VBA 独有的语法，然后转入Excel 对象模型的学习。在对语法和对象模型都了解之后，也可择重点进行学习，以此获得实质

27、上的突破。在进行学习的过程中，会慢慢的熟悉Excel 自带的VBE 编程环境，也会了解程序调试的相关知识，知道如何处理错误等。在学习的过程中，建议充分的利用VBA 的帮助系统和对象浏览器，解决在学习过程中出现的疑难，同时也丰富了的知识。另外，还建议多看看VBA 程序，并自已着手分析，对所学的知识进行归纳和总结，这是非常有利于迅速提高水平的。当学习积累到一定程度后，可以进一步深入学习了。譬如，类模块的知识、Windows API、对外部数据库的操作等等。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 9 页 共 1

28、7 页附录：项目中使用的VBA 编程实例工具栏的制作工具栏的制作主要在函数cellbardef 中实现。主要的代码段如下：上述代码中首先定义名为AuditingTest 的工具栏，初始化为可见的，接下来向工具栏中通过函数 addnewbar 添加条目。主要函数addnewbar 的介绍：上述函数包含9 个参数，其中1 个必需参数，8 个可选参数，其中对参数进行介绍：CommandBar：表示要添加条目的工具栏；cbtype：表示添加条目的类型，默认为标准的按钮；cbcaption：显示在工具栏上该条目的标题，默认为空；cbonaction：条目要执行的VBA 过程的名字，默认为空；cbface

29、id：显示在控制文本旁边的图形图像的数字，默认为空；cbbgngrp：如果该属性为True，那么在条目前会显示一个分隔条；bf：？cbstyle：？barenable：指示该条目最初的是否可用，默认为可用。右键菜单的制作右键菜单的制作主要在函数cellbardef 中实现。主要的代码段如下，其中代码中首先对Cell 的右键菜单进行重新设置，接下来向右键菜单中通过函数addcellbar 添加条目。函数 addcellbar 的参数与addnewbar 函数很相似，在这里就不介绍了。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software C

30、o.,Ltd第 10 页 共 17 页其生成的右键菜单如下：从上图可以看到数值计算和导出功能有一个向右的箭头，意味这是二级弹出式菜单，主要是由函数 addcellbarpopup 实现的。函数 addcellbarpopup 的介绍：函数中的参数意思参照addnewbar，在这儿不多介绍。工具栏与右键菜单的显示与销毁工具栏和右键菜单的显示主要是在workbook 的 Open 事件中实现的，其主要代码如下：其中的过程ReportInit 负责对 Excel 进行整理，比如删除多余的行或列；接下来判断该活动工作表中是否有数据，如果有数据则进行工具栏和右键菜单的定制；过程 mSet 将工具栏和右键

31、菜单的显示出来，在这个过程中对右键菜单中不需要的选项使其不显示；上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 11 页 共 17 页工具栏和右键菜单的销毁主要是在workbook 的 BeforeClose 事件中实现的，其主要代码如下：企业明细功能的实现企业明细功能的实现主要是由函数Corp_DrillDown实现的，其主要代码如下:在这段代码中要注意全局变量selrng 的设置：初始化：在workbook 的 Open 事件中设置的；每当用户在工作薄中选择不同的Range时，都会触发SheetSelecti

32、onChange事件，该事件包含两个参数，其中第 2 个参数表示当前选择的Range，因此在事件中对Selrng 进行设置，其主要代码如下：上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 12 页 共 17 页回到函数Corp_DrillDown中，接下来对Selrng 进行整理分别将行和列保存变量rngallh 和 rngallv 中；随后判断选择区域是否合法；接下来的for each-next 构造将每列中的内容整合到变量colstr 作为参数进行传递；最后生成fileurl 通过超链接来打开一个新的窗口来显

33、示企业的明细。历史数据功能的实现历史数据功能的实现是通过函数mod_history 来实现的，其主要代码非常简单，具体如下：前两行代码主要是对选择区域是否合法进行判断，如果合法并且活动工作表的名字为sheet1是显示用户自定义窗体UserForm1（图示如下）。点击“确定”按钮，将触发Click 事件来显示前12 个月的历史数据，Click 事件的主要代码如下：上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 13 页 共 17 页该事件主要获得开始时间btime 和结束时间etime 然后生成fileurl 得到

34、相应的历史数据。点击“取消”按钮，将触发Click 事件来隐藏自定义用户窗体，Click 事件的主要代码如下：排序功能的实现排序功能的实现主要是由函数mod_sort 来实现的，其主要代码如下：即用户自定义窗体UserForm2 的显示（图示如下）。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 14 页 共 17 页在这个函数要看一下排序关键字的填充方法，它主要是由函数mod_sort_loadkey 实现的，在用户窗体的初始化时被调用，其主要代码如下：函数首先将关键字段以逗号分隔填充到字符串变量keyStr

35、中，然后通过函数Split 将关键字段填充到数组Keyrow 中，最终填充到自定义窗体的3 个下拉列表中。在这里注意每个下拉列表的 ListIndex 初始化为0.点击窗体上面的“确定”按钮，将触发Click 事件来对工作表中的字段进行排序，具体代码如下：上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 15 页 共 17 页通过定义长度为3 的数组 key 来保存 3 个相应下拉列表框中选择的排序关键字段，同时定义长度为 3 的数组 order 来存放排序的类型，最后通过函数sort 来实现最终的排序。点击“取消

36、”按钮，将触发Click 事件来隐藏自定义用户窗体.筛选功能的实现排序功能的实现主要是由函数mod_filter 来实现的，其主要代码如下：筛选功能的实现主要在于确定在何处开始筛选（程序段中红色的一行确定了在第6 行开始筛选）同时确定筛选的行数由蓝色一行确定总的行数，然后减去5 即得，在两种颜色之间的一行确定了筛选的列数。程序的后半段是调用系统自带的筛选功能进行筛选。上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 16 页 共 17 页数值计算的实现数值计算的实现主要是由函数mod_cal_相关计算的函数名实现的

37、，它们都是通过函数check_cal_item来进行的，其主要代码如函数最后一个条件判断将相应的数据填充到工具栏中的数值计算文本框中。导出函数的实现导 出 函 数 的 实 现 是 通 过 函 数mod_output_xls(doc、htm)实 现 的，他 们 都 是 通 过 函 数mod_output_template 实现的，函数首先确定工作表中的最后一行和列分别保存在lastrow 和lastcol 中，然后将整个区域保存在变量rngall 中，最终通过copy 和 PasteSpecial来将要导出的区域保存在临时的工作表中，最后按照不同的文件格式导出到相应的文件中。主要的代码如下：上海宝信软件股份有限公司-培训材料上海宝信软件股份有限公司Shanghai Baosight Software Co.,Ltd第 17 页 共 17 页复制、粘贴、清除内容、图表向导、栏锁定功能的实现上述功能都是利用Excel 自带的功能实现的，在这里不做赘述。