21、Excel VBA：功能扩展与数据处理

电竞养老选手

于 2025-11-20 09:21:55 发布

阅读量7

点赞数

CC 4.0 BY-SA版权

分类专栏： Excel VBA实战精讲文章标签： Excel VBA Ribbon 控件自定义UI

本文链接：https://blog.youkuaiyun.com/tensor9flow/article/details/155339226

Excel VBA实战精讲专栏收录该内容

29 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

Excel VBA：功能扩展与数据处理

1. Excel VBA 中 Ribbon 控制的使用

在 Excel VBA 编程里，ExecuteMso 方法十分实用，而 GetImageMso 方法虽使用复杂，但能在电子表格上显示控件图像。以下是显示复制控件图像的代码示例：

Sub DisplayMsoImage()
    Dim MyMsoImage As OLEObject
    Set MyMsoImage = _
        ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Left:=100,
        Top:=100)
    MyMsoImage.Object.Picture = _
        Application.CommandBars.GetImageMso("Copy", 32, 32)
End Sub

若要清除该图像，可使用以下代码：

ActiveSheet.OLEObjects(1).Delete

1.1 自定义 Ribbon 控件

借助自定义 UI 编辑器，可在用户界面的主页标签上创建自定义 Ribbon 控件。具体操作步骤如下：
1. 在之前构建的示例电子表格中，于 </group> 之后、 </tabs> 之前添加以下代码：

</tab>
<tab idMso="TabHome">
    <group id="Group1" label="My Control">
        <checkBox id="Checkbox1"
            label="Checkbox sample"
            onAction="MyCheckbox"
        />
    </group>
</tab>

完整的 XML 代码如下：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="Contoso" insertAfterMso="TabHome">
                <group idMso="GroupClipboard" />
                <group idMso="GroupFont" />
                <group id="customGroup" getLabel="MyLabel">
                    <button id="customButton1" label="ConBold" size="large"
                        onAction="conBoldSub" imageMso="HappyFace" />
                    <button id="customButton2" label="ConItalic" size="large"
                        onAction="conItalicSub" imageMso="Italic" />
                    <button id="customButton3" label="ConUnderline"
                        size="large" onAction="conUnderlineSub"
                        imageMso="Underline" />
                </group>
                <group idMso="GroupEnterDataAlignment" />
                <group idMso="GroupEnterDataNumber" />
                <group idMso="GroupQuickFormatting" />
            </tab>
            <tab idMso="TabHome">
                <group id="Group1" label="My Control">
                    <checkBox id="Checkbox1" label="Checkbox sample"
                        onAction="MyCheckbox" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

需注意，XML 文件中只能有一个自定义 UI 根节点，否则会出错。

定义回调函数：
由于当前 MyCheckbox 回调函数未定义，打开主页标签会报错。可使用“Generate Callbacks”图标生成回调代码，将其复制粘贴到 VBA 编辑器的模块中并保存关闭文件。以下是 MyCheckbox 回调函数的代码：

Sub MyCheckbox(control As IRibbonControl, pressed As Boolean)
    MsgBox pressed
End Sub

当用户激活复选框时，会弹出显示“True”的消息框；取消激活则显示“False”。

1.2 添加编辑框

若要在 Ribbon 控件中捕获用户输入，可添加编辑框。在复选框 XML 代码下方添加以下 XML 代码：

<editBox id="Editbox1"
    label="Editbox sample"
    onChange="MyChange"/>

注意，编辑框使用的是 onChange 事件而非 onAction 事件。完整的 XML 代码如下：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    onLoad="Startup">
    <ribbon>
        <tabs>
            <tab id="customTab" label="Contoso" insertAfterMso="TabHome">
                <group idMso="GroupClipboard" />
                <group idMso="GroupFont" />
                <group id="customGroup" getLabel="MyLabel">
                    <button id="customButton1" label="ConBold" size="large"
                        onAction="conBoldSub" imageMso="HappyFace" />
                    <button id="customButton2" label="ConItalic" size="large"
                        onAction="conItalicSub" imageMso="Italic" />
                    <button id="customButton3" label="ConUnderline"
                        size="large" onAction="conUnderlineSub"
                        imageMso="Underline" />
                </group>
                <group idMso="GroupEnterDataAlignment" />
                <group idMso="GroupEnterDataNumber" />
                <group idMso="GroupQuickFormatting" />
            </tab>
            <tab idMso="TabHome">
                <group id="customGroup1" label="My Control">
                    <checkBox id="Checkbox1" label="Checkbox sample"
                        onAction="MyCheckbox" />
                    <editBox id="Editbox1" label="Editbox sample"
                        onChange="MyChange" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

保存文件并加载到 Excel 中，点击主页菜单项，自定义 Ribbon 控件“ My Control”会出现文本输入框。输入内容并按回车键会报错，因为 MyChange 回调函数未定义。可使用自定义 UI 编辑器的回调图标生成代码并复制粘贴到 VBA 模块中。以下是 MyChange 回调函数的代码：

Sub MyChange(control As IRibbonControl, text As String)
    MsgBox text
End Sub

1.3 添加组合框

若要在 Ribbon 控件中添加组合框（下拉控件），可在编辑框 XML 代码下方添加以下 XML 代码：

<comboBox
    id="MyCombo" label="My Combo"
    onChange ="OnChange"
    getItemCount ="GetItemCount"
    getItemLabel ="GetItemLabel"
/>

完整的 XML 代码如下：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="Contoso" insertAfterMso="TabHome">
                <group idMso="GroupClipboard" />
                <group idMso="GroupFont" />
                <group id="customGroup" getLabel="MyLabel">
                    <button id="customButton1" label="ConBold" size="large"
                        onAction="conBoldSub" imageMso="HappyFace" />
                    <button id="customButton2" label="ConItalic" size="large"
                        onAction="conItalicSub" imageMso="Italic" />
                    <button id="customButton3" label="ConUnderline" size="large"
                        onAction="conUnderlineSub" imageMso="Underline" />
                </group>
                <group idMso="GroupEnterDataAlignment" />
                <group idMso="GroupEnterDataNumber" />
                <group idMso="GroupQuickFormatting" />
            </tab>
            <tab idMso="TabHome">
                <group id="customGroup1" label="My Control">
                    <checkBox id="Checkbox1" label="Checkbox sample"
                        onAction="MyCheckbox" />
                    <editBox id="Editbox1" label="Editbox sample"
                        onChange="MyChange" />
                    <comboBox id="MyCombo" label="My Combo"
                        onChange ="OnChange" getItemCount ="GetItemCount"
                        getItemLabel ="GetItemLabel" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

保存文件并加载到 Excel 中，点击主页菜单项，自定义 Ribbon 控件会出现下拉框但未填充内容。使用自定义 UI 编辑器的“Create Callbacks”图标复制三个回调函数（ OnChange 、 GetItemCount 和 GetItemLabel ）的代码并粘贴到 VBA 模块中。同时，需在模块的声明区域定义一个数组来保存下拉框的值：

Dim ComboArray(3)

以下是回调函数的代码：

Sub GetItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 4
End Sub

Sub GetItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    ComboArray(0) = "Option1"
    ComboArray(1) = "Option2"
    ComboArray(2) = "Option3"
    ComboArray(3) = "Option4"
    returnedVal = ComboArray(index)
End Sub

Sub OnChange(control As IRibbonControl, text As String)
    MsgBox text
End Sub

GetItemCount 函数返回组合框列表中的项目数量， GetItemLabel 函数填充数组并根据索引返回选项标签， OnChange 函数在用户选择项目时显示所选文本。需注意， GetItemCount 和 GetItemLabel 仅在文件加载到 Excel 时调用，组合框列表无法在电子表格操作期间动态更改。

2. Excel 数据透视表的创建与操作

数据透视表是 Excel 处理和分析大量数据的强大功能，能以“立方体”格式展示数据摘要，让用户使用不同参数和条件汇总数据。

2.1 从 Excel 前端创建简单数据透视表

步骤如下：
1. 在工作表中创建列格式的数据，确保使用列标题，用于数据透视表的分组。
2. 点击菜单栏的“插入”，再点击功能区“表格”组中的“数据透视表”图标。
3. 在弹出的对话框中选择数据范围（如 A1:D11），保持其他设置不变，点击“确定”。
4. 在数据透视表窗口中，点击“数据透视表字段列表”中的“客户”并勾选左侧复选框，再勾选“产品”复选框。
5. 将“数量”和“值”字段从“数据透视表字段列表”拖到“值的总和”窗口，完成数据透视表的创建。

数据透视表能以易读格式按客户和产品汇总数据，对于大量数据有明显优势，且具有扩展和折叠标题、使用过滤器等高级功能。

2.2 使用 VBA 创建数据透视表

以下是使用 VBA 创建相同数据透视表的代码示例：

Sub CreatePivot()
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Sheet1!R1C1:R11C4", _
        Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable5", _
        DefaultVersion:=xlPivotTableVersion12
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Customer")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Product")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField _
        ActiveSheet.PivotTables("PivotTable5").PivotFields("Quantity"), _
        "Sum of Quantity", xlSum
    ActiveSheet.PivotTables("PivotTable5").AddDataField _
        ActiveSheet.PivotTables("PivotTable5").PivotFields("Value"), _
        "Sum of Value", xlSum
End Sub

使用此代码需注意：
- 工作表名称和数据透视表名称不能已存在，如代码中创建的工作表为“Sheet4”，数据透视表名称为“PivotTable5”。
- 地址引用使用 R1C1 格式，不能使用 A1:D11 等地址作为参数。

代码首先创建新工作表，然后定义数据透视表缓存，接着选择数据透视表所在工作表并设置偏移量，添加行字段（客户和产品），最后创建值列（数量和值的总和）。

2.3 使用 VBA 操作数据透视表

数据透视表对象模型丰富，可使用 VBA 编程更改数据透视表。以下是一些示例：
- 将“数量”列转换为项目计数列 ：

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of Quantity")
    .Caption = "Count of Quantity"
    .Function = xlCount
End With

将其改回总和列 ：

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of Quantity")
    .Caption = "Sum of Quantity"
    .Function = xlSum
End With

需注意，更改标题属性时， PivotFields 集合中的索引名称也会改变。
- 添加额外的“数量计数”字段 ：

ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
    "PivotTable3").PivotFields("Quantity"), "Count of Quantity", xlCount

更改列顺序 ：
- 将产品设为主要列，客户设为次要列：

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Product")
    .Position = 1
End With

- 恢复原始顺序：

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Customer")
    .Position = 1
End With

将客户字段转换为列标签 ：

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Customer")
    .Orientation = xlColumnField
End With

通过 VBA 可提供多种数据透视表视图，可将不同视图的 VBA 代码连接到电子表格的按钮上，方便用户操作。

Excel VBA：功能扩展与数据处理

3. 综合应用示例与总结

3.1 综合示例：Ribbon 与数据透视表结合

为了更好地展示 Excel VBA 中 Ribbon 控制和数据透视表的综合应用，我们可以创建一个示例，通过 Ribbon 上的按钮来控制数据透视表的操作。

首先，在自定义 UI 编辑器中，在现有的 XML 代码中添加一个新的按钮，用于触发数据透视表的操作。在 <group id="customGroup1" label="My Control"> 中添加以下代码：

<button id="PivotActionButton" label="Change Pivot Table" size="large"
    onAction="ChangePivotTable" imageMso="Refresh" />

完整的 XML 代码如下：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="Contoso" insertAfterMso="TabHome">
                <group idMso="GroupClipboard" />
                <group idMso="GroupFont" />
                <group id="customGroup" getLabel="MyLabel">
                    <button id="customButton1" label="ConBold" size="large"
                        onAction="conBoldSub" imageMso="HappyFace" />
                    <button id="customButton2" label="ConItalic" size="large"
                        onAction="conItalicSub" imageMso="Italic" />
                    <button id="customButton3" label="ConUnderline" size="large"
                        onAction="conUnderlineSub" imageMso="Underline" />
                </group>
                <group idMso="GroupEnterDataAlignment" />
                <group idMso="GroupEnterDataNumber" />
                <group idMso="GroupQuickFormatting" />
            </tab>
            <tab idMso="TabHome">
                <group id="customGroup1" label="My Control">
                    <checkBox id="Checkbox1" label="Checkbox sample"
                        onAction="MyCheckbox" />
                    <editBox id="Editbox1" label="Editbox sample"
                        onChange="MyChange" />
                    <comboBox id="MyCombo" label="My Combo"
                        onChange ="OnChange" getItemCount ="GetItemCount"
                        getItemLabel ="GetItemLabel" />
                    <button id="PivotActionButton" label="Change Pivot Table" size="large"
                        onAction="ChangePivotTable" imageMso="Refresh" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

然后，在 VBA 编辑器中定义 ChangePivotTable 回调函数，用于改变数据透视表的显示。以下是示例代码：

Sub ChangePivotTable(control As IRibbonControl)
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Customer")
        .Orientation = xlColumnField
    End With
End Sub

这个函数将数据透视表中的“客户”字段转换为列标签。

保存文件并加载到 Excel 中，点击 Ribbon 上的“Change Pivot Table”按钮，数据透视表的显示就会相应改变。

3.2 总结

通过以上内容，我们详细介绍了 Excel VBA 中 Ribbon 控制和数据透视表的使用方法。

在 Ribbon 控制方面：
| 控件类型 | 关键操作 | 回调函数 |
| ---- | ---- | ---- |
| 复选框 | 在 XML 中添加 <checkBox> 标签，定义 onAction 属性 | MyCheckbox |
| 编辑框 | 在 XML 中添加 <editBox> 标签，使用 onChange 事件 | MyChange |
| 组合框 | 在 XML 中添加 <comboBox> 标签，定义多个事件属性 | OnChange 、 GetItemCount 、 GetItemLabel |
| 按钮 | 在 XML 中添加 <button> 标签，定义 onAction 属性 | 如 ChangePivotTable |

在数据透视表方面：
| 操作方式 | 关键步骤 | 注意事项 |
| ---- | ---- | ---- |
| 前端创建 | 创建列格式数据，使用菜单插入数据透视表，设置字段 | 使用列标题，选择正确的数据范围 |
| VBA 创建 | 创建新工作表，定义数据透视表缓存，添加行字段和值列 | 工作表和数据透视表名称不能重复，使用 R1C1 地址格式 |
| VBA 操作 | 更改字段属性、添加字段、调整列顺序等 | 更改标题属性时注意 PivotFields 集合索引名称的变化 |

通过这些功能，我们可以为 Excel 用户提供更加个性化和便捷的操作界面，实现复杂的数据处理和展示需求。同时，利用 VBA 编程，我们可以将这些操作自动化，提高工作效率。

以下是一个简单的 mermaid 流程图，展示了整个操作流程：

graph LR
    A[开始] --> B[创建 Ribbon 控件]
    B --> C[定义回调函数]
    C --> D[创建数据透视表]
    D --> E[使用 VBA 操作数据透视表]
    E --> F[通过 Ribbon 按钮触发操作]
    F --> G[结束]

总之，Excel VBA 为我们提供了强大的工具，通过合理运用 Ribbon 控制和数据透视表的功能，我们可以充分发挥 Excel 在数据处理和分析方面的优势，满足不同场景下的需求。无论是处理日常工作中的数据报表，还是进行复杂的数据分析和决策支持，Excel VBA 都能帮助我们更加高效地完成任务。