25、Excel VBA 实用编程技巧

Excel VBA 实用编程技巧

在处理电子表格时，我们常常会遇到一些需要自动化操作或个性化设置的场景。下面将介绍几个实用的 Excel VBA 编程技巧，帮助你更高效地处理数据。

1. 交替行和列着色

当电子表格中有大量数据行时，阅读跨列数据可能会变得困难，尤其是打印出来或者数据行很长的时候。一种解决方案是对交替行进行着色。

操作步骤：

1. 插入 API 调用和变量类型声明 ：在用户窗体模块的声明部分（模块的最顶部）插入以下代码：

Private Type COLORSTRUC
    lStructSize As Long
    hwnd As Long
    hInstance As Long
    rgbResult As Long
    lpCustColors As String
    Flags As Long
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
Private Const CC_SOLIDCOLOR = &H80
Private Declare Function ChooseColor _
    Lib "comdlg32.dll" Alias "ChooseColorA" _
    (pChoosecolor As COLORSTRUC) As Long

2. 插入着色代码 ：

Sub shade()
    Dim x As Long, CSel As COLORSTRUC, CustColor(16) As Long
    CSel.lStructSize = Len(CSel)
    CSel.Flags = CC_SOLIDCOLOR
    CSel.lpCustColors = String$(16 * 4, 0)
    x = ChooseColor(CSel)
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            addr = Worksheet.Name & "!" & Selection.Address
            For counter = 1 To Application.Selection.Rows.Count
                If counter Mod 2 = 1 Then
                    Range(addr).Rows(counter).Interior.Color = CSel.rgbResult
                End If
            Next counter
        Next worksheet
    Next window
End Sub

3. 操作流程 ：
   • 在电子表格中选择一个范围。
   • 运行上述代码，会弹出颜色选择对话框，选择你想要的颜色。
   • 点击确定后，所选范围的交替行将被着色。

交替列着色代码：

Sub shade1()
    Dim x As Long, CSel As COLORSTRUC, CustColor(16) As Long
    CSel.lStructSize = Len(CSel)
    CSel.Flags = CC_SOLIDCOLOR
    CSel.lpCustColors = String$(16 * 4, 0)
    x = ChooseColor(CSel)
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            addr = Worksheet.Name & "!" & Selection.Address
            For counter = 1 To Application.Selection.Columns.Count
                If counter Mod 2 = 1 Then
                    Range(addr).Columns(counter).Interior.Color = CSel.rgbResult
                End If
            Next counter
        Next worksheet
    Next window
End Sub

操作步骤与交替行着色类似，只是着色对象变为交替列。

2. 为包含公式的单元格着色

在复杂的电子表格中，有时很难区分哪些单元格包含实际数字，哪些单元格包含公式。可以通过为包含公式的单元格着色来解决这个问题。

操作步骤：

1. 插入 API 调用和变量类型声明 ：与交替行和列着色的声明代码相同。
2. 插入着色代码 ：

Sub col_cell()
    Dim x As Long, CSel As COLORSTRUC, CustColor(16) As Long
    CSel.lStructSize = Len(CSel)
    CSel.Flags = CC_SOLIDCOLOR
    CSel.lpCustColors = String$(16 * 4, 0)
    x = ChooseColor(CSel)
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            For Each cell In Application.Selection
                addr = Worksheet.Name & "!" & cell.Address
                If Range(addr).HasFormula Then
                    Range(addr).Interior.Color = CSel.rgbResult
                End If
            Next cell
        Next worksheet
    Next window
End Sub

3. 操作流程 ：
   • 选择包含公式和数字的单元格范围。
   • 运行上述代码，弹出颜色选择对话框，选择颜色。
   • 点击确定后，包含公式的单元格将被着色。

3. 根据主单元格引用求和

在求和行或列的单元格时，有时只希望包含特定属性的单元格的值。可以自定义一个函数来实现这个功能。

函数语法：

SUMCELLSBYREF (range, reference, attribute)

• range ：与标准 SUM 函数定义方式相同的单元格范围，通过输入公式并拖动光标选择。
• reference ：单个单元格引用，设置为你想要求和的属性，如斜体、粗体等。
• attribute ：包含以下属性之一的字符串：
  | Attribute | Description |
  | — | — |
  | Color | 对与参考单元格具有相同字体颜色的所有单元格求和。 |
  | Italic | 对与参考单元格具有相同斜体字体设置的所有单元格求和。 |
  | Bold | 对与参考单元格具有相同粗体字体设置的所有单元格求和。 |
  | Size | 对与参考单元格具有相同字体大小的所有单元格求和。 |
  | Underline | 对与参考单元格具有相同下划线设置的所有单元格求和。 |
  | Subscript | 对与参考单元格具有相同下标设置的所有单元格求和。 |
  | Superscript | 对与参考单元格具有相同上标设置的所有单元格求和。 |

代码实现：

Public Function SUMCELLSBYREF(cells_to_sum As Object, r As Object, p As String)
    Application.Volatile
    total = 0
    For Each cell In cells_to_sum
        If p = "bold" And cell.Font.Bold = r.Font.Bold Then
            total = total + cell.Value
        End If
        If p = "color" And cell.Font.Color = r.Font.Color Then
            total = total + cell.Value
        End If
        If p = "italic" And cell.Font.Italic = r.Font.Italic Then
            total = total + cell.Value
        End If
        If p = "name" And cell.Font.Name = r.Font.Name Then
            total = total + cell.Value
        End If
        If p = "size" And cell.Font.Size = r.Font.Size Then
            total = total + cell.Value
        End If
        If p = "underline" And cell.Font.Underline = r.Font.Underline Then
            total = total + cell.Value
        End If
        If p = "subscript" And cell.Font.Subscript = r.Font.Subscript Then
            total = total + cell.Value
        End If
        If p = "superscript" And cell.Font.Superscript = r.Font.Superscript Then
            total = total + cell.Value
        End If
    Next cell
    SUMCELLSBYREF = total
End Function

操作步骤：

• 在电子表格中输入公式，例如： =SUMCELLSBYREF(A1..A4,C1,"bold") 。
• 点击公式栏上的粘贴函数图标，该公式将出现在用户定义公式部分，可以像使用其他公式一样使用它。

4. 全局更改值范围

当电子表格中有大量数据时，可能希望将一系列数字按设定值或百分比进行更改。

操作步骤：

1. 创建用户窗体 ：
   • 在 VBE 菜单中选择“插入”|“用户窗体”。
   • 在窗体上添加一个标签控件用于显示输入说明，一个文本框用于捕获用户输入，以及两个命令按钮（OK 和 Cancel）。
   • 点击窗体，在属性窗口中更改标题。
2. 为按钮添加代码 ：

Private Sub CommandButton1_Click()
    'This button is for OK
    UserForm2.Hide
    canc = 0
End Sub
Private Sub CommandButton2_Click()
    'This button is for Cancel
    UserForm2.Hide
    canc = 1
End Sub

3. 插入主代码 ：

Sub change_val()
    UserForm2.Show
    If canc = 1 Then Exit Sub
    op = UserForm2.TextBox1.Text
    Dim addr As String
    For Each window In Windows
        For Each Worksheet In window.SelectedSheets
            For Each cell In Application.Selection
                addr = Worksheet.Name & "!" & cell.Address
                On Error Resume Next
                If Range(addr).Value <> "" _
                    And IsNumeric(Range(addr).Value) _
                    And Range(addr).HasFormula = False Then
                    Range(addr).Value = "=" & Val(Range(addr).Value) & op
                    Range(addr).Copy
                    Range(addr).PasteSpecial xlPasteValues
                End If
            Next cell
        Next worksheet
    Next window
End Sub

4. 操作流程 ：
   • 在电子表格中输入一系列数据（包括一些公式），并选择该范围。
   • 运行上述代码，弹出用户窗体。
   • 在文本框中输入更改因子，如 *90% ，点击 OK。
   • 所选范围内的非公式单元格将按因子进行更改。

通过以上这些 Excel VBA 编程技巧，可以更高效地处理电子表格中的数据，实现个性化的数据处理和显示。

Excel VBA 实用编程技巧

各技巧的使用场景和优势总结

为了更清晰地了解这些技巧的适用场景和优势，我们可以通过以下表格进行对比：
| 技巧名称 | 使用场景 | 优势 |
| — | — | — |
| 交替行和列着色 | 数据行或列较多，阅读困难时 | 提高数据可读性，使表格更清晰易读 |
| 为包含公式的单元格着色 | 复杂表格中区分公式和实际数字 | 快速识别公式单元格，便于数据检查和维护 |
| 根据主单元格引用求和 | 只对特定属性的单元格求和 | 灵活定制求和范围，满足特殊计算需求 |
| 全局更改值范围 | 大量数据需按设定值或百分比更改 | 批量处理数据，节省时间和精力 |

流程总结与拓展应用

以下是上述技巧操作流程的 mermaid 流程图：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px

    A([开始]):::startend --> B{选择技巧}:::decision
    B -->|交替行和列着色| C(插入 API 声明和变量类型):::process
    B -->|为包含公式的单元格着色| C
    B -->|根据主单元格引用求和| D(输入函数公式):::process
    B -->|全局更改值范围| E(创建用户窗体):::process
    C --> F(插入着色代码):::process
    F --> G(选择范围并运行代码):::process
    G --> H(选择颜色或输入因子):::process
    H --> I([结束]):::startend
    D --> J(设置参数并使用公式):::process
    J --> I
    E --> K(为按钮添加代码):::process
    K --> L(插入主代码):::process
    L --> M(选择范围并运行代码):::process
    M --> H

这些技巧不仅可以单独使用，还可以进行拓展组合应用。例如，在一个包含大量数据和公式的表格中，可以先使用“为包含公式的单元格着色”技巧，快速识别公式单元格；然后使用“交替行和列着色”技巧，提高表格的可读性；如果需要对特定属性的单元格进行求和，可以使用“根据主单元格引用求和”函数；最后，若要对数据进行批量调整，可以使用“全局更改值范围”技巧。

注意事项

在使用这些技巧时，还需要注意以下几点：
1. 代码运行环境 ：确保 VBA 宏功能已启用，否则代码将无法运行。
2. 数据备份 ：在进行全局更改值范围操作前，建议备份数据，以防误操作导致数据丢失或错误。
3. 公式更新 ：如果表格中有公式，在进行数据更改后，可能需要手动更新公式结果，特别是当“Recalc”设置为手动时。
4. 参数设置 ：在使用“根据主单元格引用求和”函数时，确保参数设置正确，否则可能会得到错误的结果。

总结

通过掌握这些 Excel VBA 编程技巧，我们可以在处理电子表格时更加高效、灵活。无论是提高数据的可读性，还是实现复杂的计算和批量数据处理，这些技巧都能发挥重要作用。希望大家在实际应用中不断探索和尝试，将这些技巧运用到更多的场景中，提升工作效率和数据处理能力。

在日常工作和学习中，我们可能会遇到各种各样的数据处理需求，Excel VBA 为我们提供了强大的工具来满足这些需求。只要我们不断学习和实践，就能更好地利用 Excel 的功能，让数据处理变得更加轻松和高效。

以上就是关于 Excel VBA 实用编程技巧的详细介绍，希望对大家有所帮助。如果你在使用过程中遇到任何问题，欢迎随时交流和探讨。