28、Excel VBA：形状操作与插件创建

Excel VBA：形状操作与插件创建

1. 利用 VBA 操作形状

在电子表格中，我们可以插入各种形状，像图表和注释这类对象本质上也属于形状。以往我们可能会借助绘图工具栏上的自动形状菜单来插入形状，甚至尝试过 3D 选项。但其实，VBA 能提供更多在 Excel 菜单中无法获取的形状选择，并且通过 VBA 操作形状能让我们有更丰富的选择。与 Excel 的用户界面相比，VBA 操作更为直接，能让我们更清晰地了解操作过程。通过使用独特的形状、颜色和 3D 效果，还能让电子表格更具吸引力。

下面是一个在模块中插入形状的示例代码：

Sub test_shad()
    Dim w As Worksheet
    Set w = worksheets("sheet1")
    Dim myshape As Shape
    Set myshape = w.Shapes.AddShape(msoShapeBalloon, 90, 90, 90, 40)
    myshape.TextFrame.Characters.Text = "Test Message"
    myshape.TextFrame.Characters.Font.Bold = True
    With myshape.ThreeD
       .Visible = True
       .Depth = 40
       .ExtrusionColor.RGB = RGB(255, 100, 255)
       .PresetLightingDirection = msoLightingTop
    End With
    myshape.BottomRightCell = ""
End Sub

代码解释如下：
- 首先，定义变量 w 为工作表类型，并将其设置为工作表集合中的 sheet1 。
- 接着，定义变量 myshape 为形状类型，使用 Set 语句将其设置为在 w 代表的工作表中添加的新形状，这里使用的形状是气球（气泡框），参数设定了气球的左、上、宽和高属性。
- 通过 TextFrame 对象将文本设置为 “Test Message”，并将字体设为加粗。
- 使用 With 语句设置形状的 3D 属性：
- Visible 属性设为 True ，使对象的 3D 部分可见。
- Depth 属性将 3D 效果的深度设为 40。
- ExtrusionColor.RGB 将挤出颜色设为紫色的 RGB 值。
- PresetLightingDirection 将光照方向设为顶部，也可使用不同常量设置其他角度的光照方向，可通过对象浏览器（在 VBE 中按 F2）搜索 msoPresetLightingDirection 查看这些常量。
- 最后，将形状的右下角单元格设为空值，这能确保形状实际显示在工作表上，若省略此步骤并运行代码，需滚动工作表才能看到形状。

运行该模块中的代码，就能看到相应结果。

形状还能用于激活宏。在自己插入的模块中添加以下代码：

Sub text_message()
    MsgBox "My shape message"
End Sub

这段代码会显示一个简单的消息框。然后在形状宏中添加以下行：

myshape.OnAction = "text_message"

这会将 OnAction 属性设置为调用刚刚创建的 text_message 子例程。当点击形状时，代码就会运行。删除之前的形状（点击形状的挤出部分并按 DELETE），重新运行宏，将光标划过新形状时会出现手形图标，点击形状就会弹出消息框。

2. 将 VBA 代码转换为插件

如果只是为自己或高级用户编写宏，在代码窗口或电子表格的命令按钮中运行宏是可行的。但如果想让更多人使用这些宏，比如在工作场所使用，甚至开发商业应用，就需要将代码转换为插件。其他用户可能不想处理零散的 VBA 代码，他们可能不了解代码模块的工作原理，并且我们也不希望他们查看和修改代码，以免引入错误。同时，用户可能希望将其与自己的电子表格结合使用。

创建插件相对容易，以下是具体步骤：
- 创建用户界面 ：需要一个合适的用户界面来调用所有程序，最好的方法是在 Excel 菜单结构中添加自己的菜单。这里将创建一个名为 “Magic” 的插件。
- 假设已输入之前示例的所有代码并插入了相应的用户窗体，现在需要为菜单结构添加以下代码：

Sub menu()
    Dim newsubitem As Object
    CommandBars("Worksheet menu bar") _
      .Controls.Add(Type:=msoControlPopup).Caption = _
        "Magic"
    Set newsubitem = CommandBars("worksheet menu bar") _
      .Controls("magic")
    With newsubitem
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Absolute Relative Formula"
        .Controls("Absolute Relative Formula").OnAction = _
            "conv_formula"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Calculate Range"
        .Controls("Calculate Range").OnAction = _
            "range_calculate"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Change Values"
        .Controls("Change Values").OnAction = "change_val"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Color Alternate Columns"
        .Controls("Color Alternate Columns").OnAction = _
            "shade1"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Color Alternate Rows"
        .Controls("Color Alternate Rows").OnAction = _
            "shade"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Color cells with Formula"
        .Controls("Color Cells with Formula").OnAction = _
            "col_cell"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Contents to Label"
        .Controls("Contents to Label").OnAction = _
            "contents_to_label"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Copy Hidden Sheets"
        .Controls("Copy hidden Sheets").OnAction = _
            "hidden_sheets"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Enter Formulae as Notes"
        .Controls("Enter Formulae as Notes").OnAction = _
            "note"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Label to Number"
        .Controls("Label to Number").OnAction = _
            "label_to_number"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Matrix Total"
        .Controls("Matrix Total").OnAction = _
            "matrix_total"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Reverse Label"
        .Controls("Reverse Label").OnAction = _
            "reverse_label"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Search"
        .Controls("Search").OnAction = "findsheet"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Sort Sheets"
        .Controls("Sort Sheets").OnAction = "sortsheet"
        .Controls.Add(Type:=msoControlButton).Caption = _
            "Transpose"
        .Controls("Transpose").OnAction = "transpose"
    End With
End Sub

代码解释如下：
- 定义变量 newsubitem 为对象类型。
- 在工具菜单栏添加一个新菜单项，注意它是以弹出式（ type=msoControlPopup ）形式添加的，这意味着它可以有子菜单，子菜单将包含所有例程。
- 将 newsubitem 变量设置为该菜单项，使用 With 语句为子菜单添加各个菜单项，每个菜单项作为控制按钮（ type=msoControlButton ）添加， OnAction 属性设置为子例程的名称。
- 将上述代码输入到模块并运行，在工作表顶部的 “AddIns” 菜单中会出现 “Magic” 选项，选择它会看到一个子菜单。

• 若不想在 Excel 副本中保留该菜单，需要编写代码移除它，可使用以下代码：

Sub remove_menu()
    CommandBars("Worksheet menu bar"). _
      .Controls("magic").Delete
End Sub

• 处理安装和卸载事件 ：从用户角度看，插件安装时菜单结构应出现，卸载时菜单应移除。在 VBE 的项目树中，打开 “Microsoft Excel Objects” 并双击 “ThisWorkbook”，在模块窗口右上角的下拉列表中向上滚动，找到 “AddinInstall” 和 “AddinUninstall”。
• 点击 “AddinInstall” 并输入以下代码：

Private Sub Workbook_AddinInstall()
    Call menu
End Sub

此代码会在插件安装时调用 menu 子例程，添加新的菜单结构。由于插件只能安装一次，能避免出现多个相同菜单项的情况。
- 点击 “AddinUninstall” 并输入以下代码：

Private Sub Workbook_AddinUninstall()
    Call remove_menu
End Sub

此代码会在用户卸载插件时调用 remove_menu 子例程，删除 “Magic” 菜单。

• 命名插件并设置保护 ：选择 VBE 菜单中的 “Tools | VBAProject Properties”，在弹出的 “VBAProject Project Properties” 表单中输入项目名称，这将设置插件的名称和文件名，同时可选择添加描述。若想防止用户查看和修改代码，可点击 “VBA Project Properties” 窗口的 “Protection” 选项卡，勾选 “Lock Project for Viewing” 框，输入并再次输入密码，点击 “OK”，务必牢记密码，否则若忘记且没有无密码备份，所有工作将白费。

• 保存为插件文件 ：选择工作表屏幕，点击屏幕左上角的 Excel 开始按钮，从菜单中选择 “Save As”，在出现的菜单中选择 “Other Formats”，打开 “Save As” 窗口。在 “Save As Type” 下拉列表中选择 “Microsoft Excel Add-In”，除非需要为旧版本 Excel 创建插件，否则不要选择 “Excel97 - 2003 Add-In”。使用默认路径 <username>\AppData\Roaming\Microsoft\Addins ，以便 Excel 自动识别文件位置，无需手动浏览。选择 “Addin” 过滤器并点击 “OK”，文件将以 .xlam 扩展名保存。

• 测试插件 ：退出 Excel 并重新打开，点击工作表屏幕左上角的 Excel 开始按钮，再点击窗口底部的 “Excel Options” 按钮，点击窗口左侧菜单中的 “Add-Ins”，会在 “Interactive Application Add-Ins” 下列出新建的插件。若要安装，点击屏幕底部的 “Manage” 下拉列表，选择 “Excel Add-Ins”（通常为默认选项），点击 “Go”。若插件未自动列出，可使用 “Browse” 按钮定位文件。勾选所选插件旁边的框，点击 “OK”，插件即安装完成。此时 Excel 菜单栏会出现 “Add-Ins” 项，点击它会看到包含 “Magic” 菜单的 “Menu Commands” 功能区，点击 “Magic” 菜单会弹出包含之前输入的所有菜单项的菜单，选择一些单元格并尝试这些选项，确保它们都能正常工作。

• 卸载插件 ：点击工作表屏幕左上角的 Excel 开始按钮，再点击窗口底部的 “Excel Options” 按钮，点击窗口左侧菜单中的 “Add-Ins”，在 “Interactive Application Add-Ins” 下列出的插件列表中找到要卸载的插件，取消勾选其旁边的框，点击 “OK”，“Magic” 菜单将从 “Add-Ins” 的 “Menu Commands” 控件中消失，若这是唯一的自定义菜单，“Add-Ins” 项也会从菜单栏消失。

以下是创建插件的流程图：

graph LR
    A[创建用户界面] --> B[处理安装和卸载事件]
    B --> C[命名插件并设置保护]
    C --> D[保存为插件文件]
    D --> E[测试插件]
    E --> F[卸载插件]

通过以上步骤，我们可以利用 VBA 操作形状，并将 VBA 代码转换为插件，方便更多用户使用。

Excel VBA：形状操作与插件创建

3. 形状操作与插件创建的技术细节分析

3.1 形状操作的技术要点

在使用 VBA 操作形状时，有几个关键的技术点需要注意。首先是形状的添加，通过 Shapes.AddShape 方法可以精确控制形状的类型、位置和大小。例如，在 test_shad 代码中， msoShapeBalloon 确定了形状为气球，后面的四个参数分别设置了形状的左、上、宽和高属性。

参数	含义
左	形状在工作表中的水平位置
上	形状在工作表中的垂直位置
宽	形状的宽度
高	形状的高度

形状的文本设置也是一个重要方面。使用 TextFrame.Characters.Text 可以为形状添加文本，而 TextFrame.Characters.Font.Bold 可以设置文本的字体样式。在 3D 效果的设置上， ThreeD 对象提供了丰富的属性，如 Visible 控制 3D 效果是否可见， Depth 设置 3D 效果的深度， ExtrusionColor.RGB 确定挤出颜色， PresetLightingDirection 设定光照方向。

另外，将形状与宏关联起来，通过设置 OnAction 属性，可以实现点击形状触发宏的功能，这为电子表格增加了交互性。

3.2 插件创建的技术要点

在创建插件时，菜单的添加和管理是核心内容。通过 CommandBars 对象可以在 Excel 菜单结构中添加自定义菜单。在 menu 代码中，首先添加一个弹出式菜单项，然后为其添加多个子菜单项，每个子菜单项都与一个特定的子例程关联。

菜单项	关联子例程
Absolute Relative Formula	conv_formula
Calculate Range	range_calculate
Change Values	change_val
…	…

为了确保插件的完整性，需要处理安装和卸载事件。通过 Workbook_AddinInstall 和 Workbook_AddinUninstall 事件，可以在插件安装和卸载时自动添加和移除菜单。

插件的命名和保护也很重要。在 VBAProject Properties 中设置项目名称和描述，同时可以通过设置密码保护代码，防止用户查看和修改。

最后，将项目保存为 .xlam 格式的插件文件，并进行测试和卸载操作，确保插件的正常使用和管理。

4. 实际应用案例与总结

4.1 实际应用案例

假设我们在一个财务报表的电子表格中，需要突出显示某些重要的数据。我们可以使用 VBA 操作形状，创建一个带有 3D 效果的气球形状，在其中添加提示信息，并将其与一个宏关联，当点击形状时弹出详细的财务分析报告。

同时，为了方便财务人员使用一系列的数据分析工具，我们可以将这些工具的代码转换为插件。通过在 Excel 菜单中添加自定义的 “Magic” 菜单，财务人员可以轻松地调用各种功能，如计算范围、颜色交替显示行和列等。

4.2 总结

通过 VBA 操作形状和创建插件，我们可以为 Excel 电子表格增加更多的功能和交互性。形状操作可以让电子表格更加生动和直观，而插件的创建则方便了代码的共享和使用，提高了工作效率。

在实际应用中，我们需要根据具体的需求选择合适的形状和插件功能，同时注意代码的安全性和可维护性。例如，在设置插件密码保护时，要妥善保管密码；在编写代码时，要遵循良好的编程规范，确保代码的可读性和可扩展性。

总之，掌握 Excel VBA 的形状操作和插件创建技术，将为我们在数据处理和分析方面带来更多的便利和可能性。

以下是一个简单的总结流程图：

graph LR
    A[VBA 操作形状] --> B[增加电子表格交互性]
    C[创建插件] --> D[方便代码共享和使用]
    B --> E[实际应用：财务报表等]
    D --> E
    E --> F[提高工作效率]