excel 如何创建自己的自动化工具

Excel技巧 2021-11-25 12:05:03

经常要使用Excel来制作图表的读者可能常常会碰到：图表系列标签引用单元格，需要手工使用“＝”分别和单元格建立关联，这样的工作如若频繁出现，会使人几近崩溃。

笔者在本书第二部分的案例中大量提及这样的方法，所以有必要创建一个工具来使这个过程变得简单和高效，本节内容就是针对该应用的一个VBA宏代码实践。

创建自己的自动化工具

需求分析

要创建一个基于Excel图表的VBA自动化功能扩展，核心代码段的部分也许并不复杂。录制宏：在图表中选中任意系列的任意数据点标签，使用“＝”和对应单元格建立关联，可获得如下代码段：

ActiveSheet.ChartObjects("图表1").Activate

ActiveChart.SeriesCollection(1).Points(1).DataLabel.Text="=Sheet1!R5C7"

简化一下上述代码，甚至只需一行即可。但这样的代码段并不能完成一个图表的自动化功能扩展，一个完整的小工具应该具备以下特点：

1．良好的Excel版本兼容性

要考虑到该工具可能被读者使用到Excel的各个已发行版本中，目前，至少要保证代码在Excel 2003/2007/2010上运行正常。

2．容易被加载和卸载

应该使用标准的Excel加载项程序集，可以方便地被加载和卸载，且在Excel的工作簿运行界面中不可见，只运行在Excel的后台。应该有相应的菜单按钮来驱动程序执行，这些按钮应该在程序加载时出现，程序卸载时从界面上消失。

3．可以在Excel图表任意元素上使用

考虑到使用鼠标无法选取有些图表上的系列，故工具应该是：只要鼠标选中任意Excel图表，且不论是图表中的何种图表元素，该工具都可被调用，同时列出相应的图表系列列表，以供使用者进行相应系列的选择。且如果鼠标选中相应系列和对应的系列标签，应该可以直接智能指定对应的系列标签，减少使用者选取时间。

4．充分考虑可能的期望问题

读者可能期望标签的引用有单元格链接和直接赋值两种类型可供选择；需要标签有诸如字体及其颜色和大小、数字格式和引用单元格保持一致的选项；对于单元格为函数引用的应该具有处理零值和空值选项；对于单元格为函数引用的应该具有处理错误值（包括#NA）选项。

5．充分考虑操作的不确定性

读者可能在操作时会出现以下误操作：选取单元格区域多于一行或一列；选取的单元格个数少于当前的系列数据点个数；或者没有指定要修改的系列。

建立用户选项面板

基于本章13.2.1节的分析，考虑到编程逻辑，首先需要在VBE界面（按下键盘Alt＋F11组合键即可打开）中新建一个窗体，然后依次添加两个Frame框架控件、1个ComboBox下拉列表框控件、4个CheckBox勾选控件、1个CommandButton按钮控件，整体效果如图13.2-1所示。

创建自己的自动化工具

图13.2-1　工具属性界面

Frame框架控件仅是为了美化，在程序执行过程，不参与代码运行；ComboBox下拉列表框控件用来存储所有图表系列，并且显示将被设置修改标签的系列名称；4个CheckBox勾选控件用来分别对应13.2.1节分析的第4点提及的4个期望；CommandButton按钮用来激活系列标签的设置与更改。

这是一个较为简单的用户窗体设计，该设计可以完全无视控件间的逻辑关系，其实将其看作是一个选项面板更加贴切。考虑到大多数人的习惯，选项中的“单元格动态引用”被默认选中，其他具体的相关设置，请读者参看示例文档。

该窗体在初始化显示后，需要将当前激活图表的系列名称加载到ComboBox下拉列表框控件中。这需要使用VBA代码来循环遍历图表的每个系列，并添加到ComboBox下拉列表框的选项内容，这样当单击右侧下拉按钮时，即可将系列名称点选到显示框中。以下是写入窗体代码区的初始化VBA代码：

程序代码　片段：13.2-1a　UserForm_Initialize窗体初始化－判断当前鼠标的选取对象

创建自己的自动化工具

上述代码的主要功能是确认当前鼠标选取的焦点是否在图表系列或系列标签上。若是，则获取对应的系列群组序号，以及其在群组中的序号。目的是将当前选取的图表系列名称直接指定到ComboBox下拉列表框的显示框中，从而减少手工下拉点选。

程序代码　片段：13.2-1b　UserForm_Initialize窗体初始化

创建自己的自动化工具

注：如果读者曾尝试使用录制宏获取代码，可能会产生“为何此处使用Excel图表系列的群组，而不使用Excel图表系列序号”的疑问。这是因为在实际操作中笔者发现，当图表中包含一个以上相同图表类型的系列，并被分别放置在主次坐标系时，VBA中使用Excel图表系列序号会使操控不起作用。

执行标签设置及修改作业

当选项面板设置好之后，接下来着手处理按下窗体［操作］按钮后的响应代码。这个部分是整个工具的核心，主要作用是执行标签设置及修改作业，因为这个部分要处理13.2.1节分析的各种不同状况，所以在整个小工具中也是代码结构最复杂的部分。以下是VBA代码的分步处理过程：

1．检查系列是否指定

该步骤检查ComboBox下拉列表显示框中是否已经指定了要修改的图表系列。若无，则弹出对话框提醒使用者进行相应的选取，否则直接执行后续代码。以下为详细代码：

程序代码　片段：13.2-2a　CommandButton1_Click按钮响应－检查系列是否指定

创建自己的自动化工具

上述代码的执行效果如图13.2-2所示。

创建自己的自动化工具

图13.2-2　程序代码　片段：13.2-2a的执行效果

2．指定引用区域并检查引用是否为单列或单行

该步骤确定标签引用的单元格区域，并且检查使用者在选取单元格区域时是否有误操作，导致引用的区域大于1列或1行。若有误操作，则提示并重新进行选取。以下为详细代码：

程序代码　片段：13.2-2b　CommandButton1_Click按钮响应－检查系列是否指定

创建自己的自动化工具

上述代码的执行效果如图13.2-3所示。

创建自己的自动化工具

图13.2-3　程序代码　片段：13.2-2b的执行效果

3．查找系列在群组中的位置

该步骤循环遍历图表系列群组，查找ComboBox下拉列表显示框中的系列，并确定其群组序号和在群组中的位置。以下为详细代码：

程序代码　片段：13.2-2c　CommandButton1_Click按钮响应－查找系列在群组中的位置

创建自己的自动化工具

4．判断单元格个数

该步骤判断引用单元格个数是否和要修改标签的数据点个数相符，若不相符则给出3个不同的解决方法来处理。以下为详细代码：

程序代码　片段：13.2-2d　CommandButton1_Click按钮响应－判断引用单元格个数

创建自己的自动化工具

上述代码的执行效果如图13.2-4所示。

创建自己的自动化工具

图13.2-4　程序代码　片段：13.2-2d的执行效果

5．标签设置及修改

在完成上述各类可能问题的检查，及相应的处理后，以下代码根据使用者在选项窗口中勾选的内容，执行标签设置及修改，这是整个工具中最核心的部分。

程序代码　片段：13.2-2e　CommandButton1_Click按钮响应－判断引用单元格个数

创建自己的自动化工具

关联到程序

完成了核心代码的编写，接下来要考虑的是设计调用代码。调用代码其实就是将图13.2-1所示窗体激活，但前提是先检查鼠标的焦点是否在图表上。这个过程为全局过程，放置在VBA模块中，以下是相应代码：

程序代码：13.3　DataLableChange启动标签更改面板

创建自己的自动化工具

建立菜单系统

代码编写的最后部分是建立菜单系统，这些菜单被用来触发这个标签更改工具的执行，菜单应该包含加载和卸载两个部分。

程序代码：13.4　AddCommandBars菜单设置

创建自己的自动化工具

有些误操作或其他异常状况会使Excel崩溃，或者直接关闭，而菜单并未卸载干净。所以加载菜单前必须要确保Excel中并不含有这些工具栏及相应按钮，否则菜单就会被重复创建，并可能引发程序执行错误。

上述代码被关联到工作簿的打开和关闭事件中，当相应的事件被触发时，Excel根据需要来加载菜单或是卸载菜单，以下是相关代码：

程序代码：13.5　Workbook_Open和Workbook_BeforeClose加载与卸载事件

创建自己的自动化工具

上述菜单基于Excel 2003创建，虽然Excel 2007/2010使用基于XML格式的Ribbon菜单，但菜单依旧可以很好地和这些版本兼容。执行效果如图13.2-5所示。

创建自己的自动化工具

图13.2-5　标签更改工具的菜单效果

注：如果读者使用Excel 2007/2010，［加载项］选项卡一般会自动出现，不需要用户额外进行添加。如果在Excel 2007/2010中看不到，请在“文件>选项>自定义功能区>主选项卡”中勾选“加载项”。

设置文档

作为一个VBA自动化功能扩展，应该将文档存储为后缀名为xla或xlam的加载项文档（加载项：指为Microsoft Office提供自定义命令或自定义功能的补充程序），此类文档可以被Excel加载项管理器管理。需要选中VBE窗口中的文档ThisWorkbook项，在其属性中将IsAddin设为True，使文档中工作表在Excel前台窗口不可见，如图13.2-6所示。

创建自己的自动化工具