第9章　Excel宏和VBA

在Excel日常应用中，一定会有许多有规律很强的、重复性的操作，如果这些操作每天重复几百次或上千次，你一定会感到厌烦。

Excel提供了一个非常有用的功能，那就是能把我们的操作序列录制下来，指定到一个快捷键或者按钮上，在需要这些操作的时候只要按一下快捷键或按一下单击钮就可以了。

Excel除了能够录制我们的操作序列以外，还可以通过一种叫做VBA的语言控制Excel，让Excel能够按照我们的指令做它能做的任何事情。

VBA是Visual Basic for Application的缩写，它是用来与Excel沟通的语言，让Excel能够听懂我们吩咐给它的任务，并能够重复执行。哪怕你只是略懂VBA，也能让它在工作中给你带来可观的收益。如果熟练地掌握了这种语言，则真的可以享受到Excel所带来的乐趣。

9.1　宏和VBA

一般情况下，我们把用Excel录制的动作称做“宏”，手动编写的控制Excel的代码称做VBA。

下面通过一个实例看一下Excel宏如何能够节省我们的时间。

假设我们的Excel报告经常使用一种适合公司风格的专用字型：隶书，16号字，蓝色，粗体。如果手工操作设置这些格式，至少需要单击四五次鼠标，下面通过录制一个宏来完成这些操作并把这些操作指定到一个快捷键上。

一般情况下，不需要录制“选择”单元格的动作，否则每当执行宏的时候，鼠标光标都会回到最初选择的那个单元格开始执行，这往往是我们所不需要的。

首先选择一个需要设置格式的单元格区域，单击“开发工具→代码→录制宏”命令，开始录制我们的Excel操作。

如果在Excel界面中没有找到“开发工具”标签，选择“Excel文件→选项“命令，在打开的Excel选项”对话框中选择左侧的自定义功能区”选项，对话框右侧的“自定义功能区”下拉框中选择“主选项卡”，在“主选项卡”下方的列表中勾选“开发工具”复选框即可

建议在开始录制操作之前，提前演练一下整个动作过程，以免Excel录制过多的无用的动作。

此时弹出“录制新宏”对话框，在该对话框中的“宏名”中，可以给即将录制的宏取一个新的名称。在这里使用Excel提供的默认名称，如图9-1所示。

图9-1

在“快捷键”选项中，这里把即将录制的宏指定给快捷键组合“Ctrl＋M”，需要注意的是，由于很多与Ctrl键组合的字母已经被Excel使用为内置快捷键（比如Ctrl＋F已经指定为“查找”的快捷键），因此在指定快捷键时要尽量避免和Excel内置快捷键发生冲突。

在“保存在”选项中需要告诉Excel，即将录制的宏保存在哪里。这里有三个选项：

（1）个人宏工作簿

如果把录制的宏保存在“个人宏工作簿”中，那么该宏在所有本机上打开的Excel文件中都能使用，“个人宏工作簿”在正常情况下是隐藏的，并随着Excel的启动而开启。

（2）新工作簿

如果把录制的宏保存在“新工作簿”中，那么录制的宏会保存在一个新建的工作簿中，当前工作簿并不保存录制宏所自动生成的VBA代码。

（3）当前工作簿

如果选择把即将录制的宏保存在“当前工作簿”中，那么录制宏所自动生成的VBA代码将保存在当前工作簿文件中，可以随着当前文件分发给其他用户。

注意：如果选择了“当前工作簿”这个选项，保存当前文件时，必须保存为“.xlsm”格式（表示该文件含有宏代码）。

我们在这里选择把宏保存在“个人宏工作簿”中，然后设置所选单元格格式为：隶书，16号字，蓝色，粗体。全部动作完成后，单击“开发工具→代码→停止录制”命令结束录制过程。此时，我们刚才录制的宏已经被保存到了所选择的“个人宏工作簿”中了，并且指定了用快捷键“Ctrl＋M”来调用它，如图9-2所示。

图9-2

在Excel其他任意一个单元格中输入任意字符，选中这些字符，然后按下快捷键Ctrl＋M，这时会发现，选中的单元格区域变成了我们期望的格式，如图9-3所示。

图9-3

是不是很新奇？事实上，录制宏的过程实际上是Excel把我们动作序列变成了一种叫做VBA的语言存储到了Excel中，在需要重复执行该动作序列的时候再次调用它。

要想查看录制的宏转变成的VBA语言是什么样子，可以按照如下方式操作。

单击“开发工具→代码→Visual Basic”命令。此时进入VBA编程环境。

因为我们把宏保存在了个人宏工作簿，因此在VBA编程环境界面的左侧单击VBAProject(PERSONAL.XLSB)下方的“模块”文件夹，在展开的列表中单击“模块1”，此时在右边的VBA代码编辑界面显示的就是我们录制的宏所对应的VBA代码，如图9-4所示。

图9-4

我们录制的宏所对应的VBA代码如下：

我们已经知道，Excel录制宏的过程其实就是把我们的操作以VBA语言的形式记录下来，如果我们学习了VBA，完全可以不用录制的方法来设计宏，而直接把我们想要做的事情用VBA语言传达给Excel，让它自动帮我们完成工作。

还有，用Excel录制宏所生成的VBA语句往往过于啰嗦，如果我们自己编写VBA代码的话，完全可以用非常精简的语言完成同样的任务。不信？可以用下面的代码代替刚刚录制的宏中的代码，照样能够完成同样的任务。

学习并掌握了VBA语言，我们就开启了另一扇门，进入了一个新天地，由于VBA的内容不是本书的重点，因此这里不会过多地讨论。但也不要失望，通过本书前面所介绍的知识的学习，我们已经了解：Excel本身的内置功能已经足够强大，足以完成我们日常数据分析和管理建模的大多数任务；而且，在本书第二部分　有关Excel和Access结合内容，会让我们更加了解，即使不用VBA，我们照样能够完成复杂的数据分析和处理任务，从而成倍地提高我们的数据分析的效率。

9.2　Excel报告自动化

恐怕很多人都有一个的梦想，那就是让Excel自动为我们工作！每天早晨上班，打开计算机，双击Excel文件，然后喝两口咖啡，顺便浏览一下全球最新新闻，5分钟以后，本来两三个小时的Excel数据分析报告就自动完成了！我们要做的只需“另存”一下即可，岂不快哉？

这是做梦吗？不会Excel编程的话有可能做到吗？我可以负责任地告诉大家，完全有可能！

现在假设一个场景：你是某家公司的管理人员，每天需要汇总一下前一天产品出货情况，前一天的出货数据由公司的业务系统在每天凌晨1点钟以文本文件的形式保存到共享磁盘空间的指定位置。你每天早晨的第一件事就是对这些数据进行分析汇总，第一时间报告给老板，以便老板进行当天的生产安排。

为了避免无关细节的干扰，下面对问题进行了最大程度的简化，我们只要明白了其中的逻辑就可以了。在实际工作中，我们可以根据具体情况无限扩展。

如图9-5所示的数据就是在每天凌晨1点的时候保存到共享磁盘空间指定位置上的文本文件，新的数据会以相同的文件名覆盖旧的数据。

图9-5

如图9-6所示是老板需要的数据分析汇总结果，需要注意的是，老板需要的分析报告绝对不会这么简单，这只是示例而已，真实情况是，我们可能需要对原始数据做各种分析汇总并辅以各种图表使数据可视化。而这些都可以在保持数据的连接性完整的情况下扩展达成的。

图9-6

下面的任务是，建立Excel与数据源的连接，然后对数据进行分析处理，最后利用Excel录制的“宏”和自行编写的VBA代码（只有一句）让整个工作自动化。

下面让我们分步查看具体实现的过程。

（1）导入文本数据，此时需要注意，为了避免每次刷新数据时出现选择文本文件对话框，中断自动化进程，需要做如下设置。

将鼠标放置在数据导入区的任意单元格上，单击“数据→连接→属性”命令，弹出“外部数据区域属性”对话框，在该对话框中，取消勾选“刷新时提示文件名”复选框。数据导入到Excel后效果如图9-7所示。

图9-7

（2）在导入的文本数据的基础上制作数据透视表、图表或其他统计图表。为说明问题简便，我们只制作了一个数据透视表，如图9-8所示。

图9-8

（3）录制宏。我们通过录制Excel宏把“刷新数据源”和“刷新数据透视表”这两个动作自动化完成。单击“开发工具→代码→录制宏”命令。在弹出的“录制新宏”对话框的“宏名”的位置给即将录制的宏取一个有意义的名字，在这里我们取名“myMacro”。

在“录制新宏”对话框的“保存在”选项处，我们选择把即将录制的宏保存在“当前工作簿”，这是因为我们只希望在当前工作簿中执行该宏，如图9-9所示。

图9-9

（4）从现在开始，我们对Excel的所有操作都会被Excel录制宏功能记录在案，因此为了使录制的宏简洁高效，我们必须认真对待每一步操作，尽量避免任何多余的操作。

在这一步骤里，我们只需录制两个动作，即“刷新数据源”和“刷新数据透视表”。首先选择Sheet1工作表，把鼠标置于导入数据区域的任意单元格，然后单击“数据→链接→全部刷新”命令刷新数据源。

选择Sheet2工作表，把鼠标置于数据透视表区域的任意一个单元格，然后单击“数据透视表工具→选项→数据→刷新”命令，数据透视表刷新完毕，如图9-10所示。此时，Excel宏的两个动作录制结束。

图9-10

（5）结束录制宏。单击“开发工具→代码→停止录制”命令结束录制宏。

（6）查看刚刚录制的宏。单击“开发工具→代码→Visual Basic”命令，打开Visual Basic编辑界面。

双击“模块”文件夹，然后双击“模块1”，即可看到Excel刚刚录制的Excel宏的后台VBA代码，如图9-11所示。

图9-11

下面解释一下每一句代码的含义。

Sub myMacro()——我们给宏取的名称，录制的宏在此开始。

' myMacro宏——Excel录制宏时自动添加的注释。

Sheets("Sheet1").Select——选择名称为“Sheet1”的工作表。

ActiveWorkbook.RefreshAll——刷新当前工作簿的所有外部连接的数据源。

Sheets("Sheet2").Select——选择名称为“Sheet2”的工作表

Range("B4").Select——因为上一句代码选择了“Sheet2”，因此这一句选取的是“Sheet2”的B4单元格，该单元格位于数据透视表范围内。

ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh——刷新“数据透视表1”，我们制作数据透视表时，Excel会自动给每个数据透视表取一个名称。

End Sub——录制的宏在此结束。

（7）如何让Excel文件打开时自动执行宏呢？我们在VBA编辑界面左侧的Excel对象浏览器里双击“ThisWorkbook”，进入“ThisWorkbook”的代码编辑界面。在该界面右侧的代码编辑区的上方单击写有“通用”字样的下拉框，从中选择“Workbook”选项，如图9-12所示。

图9-12

此时Excel自动生成以“Private Sub Workbook_Open()”开始，以“End Sub”结束的Excel宏开始和结束标识。该Excel宏标识表示，当Excel文件打开时（即Workbook_Open时），将执行“Private Sub Workbook_Open()”和“End Sub”之间的所有动作。

我们现在的目的是让Excel文件打开时自动执行我们刚刚录制的宏“myMacro”，因此我们在两句代码之间输入“Call myMacro”，表示我们要调用执行我们刚刚录制的宏“myMacro”，如图9-13所示。

图9-13

（8）所有工作完成后，别忘了保存我们精心制作的文件。单击Excel VBA编辑界面上的保存图标，在弹出的“另存为”对话框中选择保存位置，在“保存类型”处必须选择“Excel启用宏的工作簿(*.xlsm)”类型，然后单击“保存”按钮。

（9）打开刚刚保存的启用了宏的工作簿，默认情况下会弹出一个安全警告框，如图9-14所示，这里单击“启用宏”按钮，可以看到Excel执行的效果。

图9-14

这个在默认情况下弹出的安全警告框可以在Excel选项里设置为不显示，设置方法如下：选择“文件→信任中心”命令，打开“信任中心”对话框，如图9-15所示。

图9-15

在“信任中心”对话框的左侧，选择“受信任的位置”，在对话框右下角单击“添加新位置”按钮，在打开的“Microsoft Office受信任的位置”对话框中选择受信任的文件夹，如果该文件夹下的子文件夹也受信任，则需要同时勾选“同时信任此位置的子文件夹”复选框。然后单击“确定”按钮关闭所有对话框，如图9-16所示。现在，在打开该文件夹下的含有宏或VBA的Excel文件时，都不会弹出安全警告框了。

图9-16

好了，现在是“见证奇迹”的时刻了！为了证明我们的宏确实起了作用，下面修改数据源的文本文件中的数据，把出货数量全部由1改成10，然后保存关闭文本文件。现在，再次双击打开我们刚刚设计的Excel文件，此时可以看到Excel界面快速地闪动了两下后，停留在了数据透视表界面，我们发现，新的汇总结果已经在那里了，如图9-17所示。我们下面需要做的只是把已经准备好的Excel分析报告另存为一个新的文件名而已。

图9-17

你可能会有疑问，如果想修改Excel文件中的代码或者暂时不想执行Excel宏该怎么办呢？方法很简单，按住Shift键双击打开文件即可忽略自动执行的宏。

微言微语

●　数据透视表的本质：数据透视表数值汇总区的每一个单元格中的数值实际上是以该单元格所对应的行标签和列标签作为筛选条件、对原始数据进行“筛选”汇总得到的结果，数据透视表可以简单地理解为对大量数据进行批量筛选汇总的工具。