2.3　宏与VBA

宏是一系列可以重复执行的操作。在处理工作表的过程中，如果要重复执行一系列相同的操作，可以将这些操作过程录制成宏。以后如果要执行这些操作，只需运行宏，从而大大简化了操作。

Excel提供了录制宏的功能，为了优化宏的功能，还可以利用Visual Basic对录制的宏进行编辑。为了使宏更容易运行，可以将宏与快捷键、工具栏的按钮或菜单项建立关联，一旦按下快捷键、单击按钮或执行菜单项就可以运行宏。此外，为了更有效地利用宏，还可以在模块间、不同工作簿之间进行宏复制。

2.3.1　录制宏

录制宏的步骤如下。

(1)单击“视图”选项卡，再单击“宏”功能组中“宏”的下拉按钮，从显示的菜单中选择“录制宏”，弹出如图2-10所示的“录制宏”对话框。

(2)在对话框的“宏名”处输入一个新的宏名，然后指定快捷键，选择宏保存的位置，最后单击“确定”按钮便开始录制宏。

(3)在工作表中执行一遍需要录制的操作。

(4)单击“视图”选项卡，再单击“宏”的下拉按钮，从显示的菜单中选择“停止录制”。

于是步骤(3)在工作表上所做的操作被指定到一个宏中，完成宏的录制。

需要说明的是，宏名必须是首字符为字母的字母数字串，名称中不允许出现空格。如果在“快捷键”编辑框中指定了快捷键(在Ctrl+后的编辑框中输入一个字母)，以后可以用Ctrl+字母(小写字母)或Ctrl+Shift+字母(大写字母)的方式来运行宏。

【例2-19】录制一个宏用于设置表头的样式，要求给单元格区域设置粗边框、浅蓝色底纹，设置文字颜色为暗红色，宏名为“Frame”，指定运行宏的快捷键为Ctrl+a。

具体操作过程如下。

(1)在工作表中选择单元格区域A1:E1。

说明：此操作是开始录制宏之前的操作，并不会录到宏里。如果将此操作也录到宏里，运行此宏时将只对区域A1:E1设置表头样式。

(2)单击“视图”选项卡，再单击“宏”的下拉按钮，从显示的菜单中选择“录制宏”，弹出如图2-10所示的“录制宏”对话框。

(3)在对话框的“宏名”处输入“Frame”，在快捷键处输入字母“a”，选择宏保存的位置为当前工作簿，如图2-11所示。

(4)单击“确定”按钮开始录制宏。

(5)单击“开始”选项卡，再单击“字体”功能组右下角的扩展按钮，出现如图2-12所示的“设置单元格格式”对话框。单击“边框”选项卡，选择边框的线条样式和颜色，选择“预置”下的外边框选项。

(6)继续上一步，在该对话框的“字体”选项卡中设置字体的颜色为红色，并加粗。在“填充”选项卡中选择填充的颜色为浅蓝色。最后单击“确定”按钮，返回工作表。

(7)单击“视图”选项卡，再单击“宏”按钮的下拉按钮，从显示的菜单中选择“停止录制”按钮，完成宏的录制。

(8)单击“文件”选项卡，选择“另存为”命令，选择存储位置，出现“另存为”对话框，如图2-13所示。在“保存类型”列表中选择“Excel启用宏的工作簿”，修改文件名为“销售情况2”，单击“确定”按钮，将以新文件名、以.xlsm为扩展名保存工作簿。

说明：如果仍以.xlsx为扩展名保存工作簿，则刚录制的宏将不能保存。

2.3.2　运行宏

宏录制好后，每次运行保存好的宏就可以重复前面录制的操作。我们仍然以【例2-19】为例说明运行宏的方法。

(1)打开上面同一个工作簿中的另一个新工作表，如Sheet2，选中需要设置表头的单元格区域，如C2:F2。

(2)单击“视图”选项卡，再单击“宏”按钮的下拉按钮，从显示的菜单中选择“查看宏”按钮，于是弹出如图2-14所示的对话框。

(3)在对话框中的“位置”下拉式列表框中选择“所有打开的工作簿”，并在“宏名”列表框中选择要运行的宏“Frame”，再单击“执行”按钮，即按照录制宏的操作将所选的单元格区域加上粗边框、设置底纹和字体颜色，也就设置了表头。

另外，因为已经定义了宏运行的快捷键，因此运行时也可以在选中要设置表头的单元格区域后，直接按“Ctrl+a”也可以获得同样的效果。

2.3.3　编辑宏

宏其实就是一段Visual Basic程序，如果你已经掌握了Visual Basic程序，就可以查看或编辑宏，从而获得更精确的自定义的功能。编辑宏的操作步骤如下。

(1)在如图2-14所示的对话框中，选择要编辑的宏，再单击“编辑”按钮，于是启动Visual Basic编辑器，并显示该宏的程序，如图2-15所示。

(2)在Visual Basic的窗口中依据Visual Basic语法规则，对程序语句进行修改。

(3)编辑完成后，单击保存按钮，并关闭Visual Basic窗口。

2.3.4　认识VBA

前面所介绍的宏可以完成一些简单的重复操作，从而极大地方便用户对财务数据工作表的处理。但是使用录制的宏有一些局限性，例如，要创建用户自己定义的特殊用途的函数，或者创建特殊的对象或对对象进行特殊的操作，仅仅靠使用宏就无法完成了。通过Excel提供的Visual Basic for Applications(VBA)，即VBA可以对宏进行优化，或者编制用户定义的过程和函数，以满足用户特定的数据处理需求。

1．VBA(Visual Basic for Applications)

Basic是Beginners All-Purpose Symbolic Instruction Code的缩写，是一种广泛使用的计算机语言。

Visual Basic是Microsoft针对Windows应用，在Basic语言的基础上发展起来的可视的应用程序开发工具。所谓可视(Visual)是指一种利用图形用户界面(GUI)开发程序的方法。在可视的编程系统中已经定义了大量的对象，编程人员只需将这些对象放到一个界面上，指定对象的属性，并建立用户操作(事件)、对象、方法之间的关系，就可以完成应用程序的编制。应用程序的开发过程对开发者来说是可见的。Visual Basic是独立的开发工具，是Visual Studio的组件之一，可以单独运行。

VBA同样使用了Visual Basic的通用的编程功能，具有Visual Basic可视编程的强大功能。同时，VBA又针对应用提供有针对性的对象、属性和方法等。VBA不能单独运行，需随应用软件提供给用户。例如，随Microsoft Office提供的VBA，其中包含Excel的VBA、Word的VBA、Access的VBA等，它们既有通用的功能，又各自具有特定的功能，如Excel的VBA会提供工作簿、工作表、区域等对象。

2．使用Visual Basic编辑器

要在Excel中使用VBA，需要借助Visual Basic编辑器(VBE)。VBE是VBA的开发环境，VBE的功能包括“工程资源管理器”“属性窗口”“对象浏览器”“代码窗口”和“监视窗口”等。这些窗口是开发VBA的主要环境。

在Excel窗口中，单击“开发工具”选项卡，再单击“代码”功能组中的“Visual Basic”按钮，即启动了Visual Basic编辑器。通过前面介绍的编辑宏的方法也可以启动Visual Basic编辑器。

在Visual Basic编辑器窗口中，单击“视图”选项卡，就会显示各窗口命令的菜单，如图2-16所示。单击其中的菜单项，便可以打开相应的VBE开发工具。

例如，单击“视图”菜单中的“工程资源管理器”命令，再单击“视图”菜单中的“属性窗口”，即弹出如图2-17所示的界面。从图2-17中可以看出，Excel的工程资源包含一些系统内部的工程和用户定义的工程。一个用户定义的工程对应一个打开的工作表，如图中的“销售情况2.xlsm”对应一个用户定义的工程。“属性”窗口列出了Sheet1的各种属性。

在“工程资源管理器”窗口中，打开一个用户定义的工程，可以看到一个工程由Microsoft Excel对象、窗体、模块和类模块组成。其中，Excel对象包含本工作簿和工作表；窗体由用户创建，在一个应用中可以作为用户与应用程序交互的界面；模块也由用户创建，一个模块包含若干过程，双击其中的一个模块，如模块2，可以在“代码”窗口中看到用户录制的宏所对应的过程，参考图2-15所示的内容。

3．理解对象、属性、事件和方法

VBA是面向对象的开发工具，在介绍后面的内容之前，理解对象、属性、事件和方法的概念对读者使用VBA是很有帮助的。

●　对象代表应用程序中的元素，如工作表、单元格、图表、按钮、窗体等。只有在确定了对象之后，才能对它进行操作。通常将具有相同类型的对象集合起来，形成对象集合。例如，在Excel中的Worksheets对象包含了工作簿中所有的Worksheets对象。

对象需要用名字来标识。例如，在下面的过程中使用sheet1来识别第一个工作表。

如果一个对象在对象集合中，还可以用它在集合中的索引号来确定。例如，在下面的过程中第一个工作表使用Worksheets(1)来识别。

●　属性是一个对象的属性。它定义了对象的特征，如大小、颜色、屏幕位置，或某一方面的行为，如对象是否被激活或可见等。修改对象的属性值可以改变对象的特征。有些属性可以被用户设置属性值，有些属性不能被用户设置。若要设置属性值，就要在对象的引用后面加上一个复合句。它是由属性名加上等号“=”，及新的属性值组成的。例如，下面的过程通过设置窗体中的Caption属性来更改窗体的标题。

●　事件是一个对象可以辨认的动作，如单击鼠标或按下某键等。可以通过写代码针对这些动作做出响应。事件的发生可以由用户的动作或程序代码的结果引起，也可以由系统引发。例如，下面的过程实现了“单击用户定义的命令按钮，将一个隐藏的工作表显示出来”的功能。

●　方法指的是对象能执行的动作。例如，Add是属于ComboBox对象的一个方法，它会增加一个新项到下拉式列表框中。下面的过程通过使用Add方法增加一个新项到ComboBox中。

2.3.5　了解VBA命令结构

要利用VBA，必须首先明确VBA的命令结构。

1．定义过程

VBA程序包含一系列的过程(程序)，如用户创建的宏就对应着相应的过程。过程可以分为Sub过程、Function过程和Property过程。在财务处理中，常常用到Sub过程和Function过程。Property过程允许用户创建自定义的属性，较少使用。

●　Sub过程是由Sub和End Sub语句包含起来的一系列Visual Basic语句，用于实现特定的功能。可以为Sub过程定义参数变量，并在调用Sub过程时为这些参数变量指定相应的值，但是Sub过程不能返回一个值。Sub过程也可以没有参数，但即使没有参数，定义Sub过程时也必须在过程名后带一个空的圆括号。例如，下面的Sub过程是在屏幕上显示一个如图2-18所示的消息框。

●　Function过程是由Function和End Function语句包含起来的一系列Visual Basic语句。Function过程和Sub过程很类似，但函数会在过程的一个或多个语句中指定一个值给函数名称，从而返回一个值。

在下面的示例中，Interest函数会根据输入的贷款数量和年利率两个参数计算年利息。当Main过程调用Interest函数时，将贷款数量和年利率两个参数变量传递给此函数。而计算的结果会返回到调用的过程，并且显示在一个消息框中。

运行该过程，则首先出现如图2-19所示的对话框，提示输入第一个参数：贷款年利率，在此我们假设贷款年利率为10%；输入后，单击“确定”按钮，则出现如图2-20所示的对话框，提示输入第二个参数：贷款数量，在此我们假设贷款数量为10 000；输入后，单击“确定”按钮，则出现如图2-21所示的消息框，即每年的年利息为1000。

2．声明语句

可以使用声明语句去命名和定义过程、变量、数组及常数。当声明一个过程、变量或常数时，也同时定义了它的范围，而此范围取决于声明位置及用什么关键字来声明它。

下面的示例包含两个声明：

3．赋值语句

赋值语句可以给一个变量指定一个值或表达式，也可以给变量指定一个对象。

4．With语句

在一段程序中，如果多个变量具有相同的限定，就可以使用With语句以节省输入的字符，并避免错误。例如，在上一个例子中给单元格的字体赋值的语句可以改写为：

5．使用Do…Loop语句

Do…Loop语句是一种循环结构语句，根据所带的条件语句的不同可以有下面几种结构。

(1)先判断条件，条件为True时就重复，其结构如下。

下面例子的过程即利用了该结构：

(2)先执行一次，再判断条件，条件为True时就重复，其结构如下。

下面例子的过程即利用了该结构：

(3)先判断条件，如条件为False就重复，直到条件变成True时才停止，其结构如下。

下面例子的过程即利用了该结构：

(4)先执行一次，再判断条件，如条件为False就重复，直到条件变成True时才停止，其结构如下。

6．使用For Each…Next语句

For Each…Next语句是另一种循环结构语句，作用于集合中的每个对象或是数组中的每个元素。循环执行一次，Visual Basic就会自动设置一个变量。例如，下面的过程将对指定范围的单元格做循环，并将任何绝对值小于0.01的号码设为0。

7．使用For…Next语句

For…Next语句也是一种循环结构语句，它的循环次数是确定的。

在下面的示例中，计数变量j会在每次循环重复时加上2。当循环完成时，total的值为2、4、6、8和10的总和。

8．使用If…Then…Else语句

If…Then…Else语句是一种条件结构语句，根据条件产生分支，从而执行一些语句而跳过另一些语句。

根据实际需要可以从下面几种结构中选择需要的结构。

(1)如果条件为True则运行语句。

(2)如果条件为True，则运行某些语句；如果条件为False，则运行其他语句。

(3)如果第1个条件为False，则测试第2个条件，如果第2个条件也为False，则测试第3个条件，以此类推……。

9．使用Select Case语句

当一个表达式与几个不同的值比较时，可以使用Select Case语句。Select Case语句从多个条件中选择第一个符合条件的情况执行，其结构如下。

如果我们能熟练掌握宏的操作方法及VBA的基本语法，将有利于对录制的宏进行优化，从而实现对工作表的某些特别操作。