17、Excel VBA 事件与范围操作全解析

Excel VBA 事件与范围操作全解析

1. Excel 事件概述

Excel 中有多种事件，这些事件会在特定操作触发时发生。以下是一些常见事件及其触发动作的列表：
| 事件 | 触发事件的动作 |
| ---- | ---- |
| SheetDeactivate | 任何工作表被停用 |
| SheetFollowHyperlink | 点击超链接 |
| SheetPivotTableUpdate | 任何数据透视表更新 |
| SheetSelectionChange | 除图表工作表外，任何工作表上的选择区域发生变化 |
| WindowActivate | 任何工作簿窗口被激活 |
| WindowDeactivate | 任何工作簿窗口被停用 |
| WindowResize | 任何工作簿窗口被调整大小 |
| WorkbookActivate | 任何工作簿被激活 |
| WorkbookAddinInstall | 工作簿作为加载项安装 |
| WorkbookAddinUninstall | 任何加载项工作簿被卸载 |
| WorkbookBeforeClose | 任何打开的工作簿关闭 |
| WorkbookBeforePrint | 任何打开的工作簿打印 |
| WorkbookBeforeSave | 任何打开的工作簿保存 |
| WorkbookDeactivate | 任何打开的工作簿被停用 |
| WorkbookNewSheet | 任何打开的工作簿中创建新工作表 |
| WorkbookOpen | 工作簿打开 |

2. 启用应用程序级事件

要使用应用程序级事件，需要按以下步骤操作：
1. 插入一个新的类模块。
2. 在“属性”窗口的“名称”下为这个类模块设置一个名称。默认情况下，VBA 会为每个新类模块提供一个默认名称，如 Class1、Class2 等。你可以给类模块取一个更有意义的名称，例如 clsApp。
3. 在类模块中，使用 WithEvents 关键字声明一个公共的 Application 对象。示例代码如下：

Public WithEvents XL As Application

4. 创建一个变量，用于引用类模块中声明的 Application 对象。它应该是在常规 VBA 模块（而不是类模块）中声明的模块级对象变量。示例代码如下：

Dim X As New clsApp

5. 将声明的对象与 Application 对象连接起来。这一步通常在 Workbook_Open 过程中完成。示例代码如下：

Set X.XL = Application

6. 在类模块中为 XL 对象编写事件处理程序。

3. 确定工作簿何时打开

下面的示例通过将信息存储在逗号分隔变量（CSV）文本文件中来跟踪每个打开的工作簿。可以将此文件导入 Excel。
首先，插入一个新的类模块并将其命名为 clsApp，类模块中的代码如下：

Public WithEvents AppEvents As Application

Private Sub AppEvents_WorkbookOpen (ByVal Wb As Excel.Workbook)
    Call UpdateLogFile(Wb)
End Sub

此代码将 AppEvents 声明为带有事件的 Application 对象。每当打开工作簿时， AppEvents_WorkbookOpen 过程将被调用。这个事件处理程序调用 UpdateLogFile 并传递 Wb 变量，该变量表示打开的工作簿。
然后，添加一个 VBA 模块并插入以下代码：

Dim AppObject As New clsApp
Sub Init()
'   Called by Workbook_Open
    Set AppObject.AppEvents = Application
End Sub

Sub UpdateLogFile(Wb)
    Dim txt As String
    Dim Fname As String
    txt = Wb.FullName
    txt = txt & "," & Date & "," & Time
    txt = txt & "," & Application.UserName
    Fname = Application.DefaultFilePath & "\logfile.csv"
    Open Fname For Append As #1
    Print  #1, txt
    Close #1
    MsgBox txt
End Sub

Workbook_Open 过程调用 Init 过程，因此当工作簿打开时， Init 过程会创建对象变量。最后一条语句使用消息框显示写入 CSV 文件的信息。如果你不想看到该消息，可以删除此语句。

4. 监控应用程序级事件

为了了解事件生成过程，查看在工作过程中生成的事件列表会很有帮助。有一个名为 ApplicationEventTracker.xlsm 的工作簿，它使用类模块监控所有应用程序级事件，并在事件发生时显示各种应用程序级事件的描述。这个工作簿对于学习事件的类型和顺序很有帮助。

5. 访问与对象无关的事件

前面讨论的事件都与对象（如 Application 、 Workbook 、 Sheet 等）相关。这里讨论另外两个特殊事件： OnTime 和 OnKey 。这些事件通过 Application 对象的方法访问，且需要在常规 VBA 模块中编程。

5.1 OnTime 事件

OnTime 事件在一天中的指定时间发生。以下示例展示了如何编程让 Excel 在下午 3 点发出蜂鸣声并显示消息：

Sub SetAlarm()
    Application.OnTime TimeValue("15:00:00"), "DisplayAlarm"
End Sub

Sub DisplayAlarm()
    Beep
    MsgBox "Wake up. It's time for your afternoon break!"
End Sub

在这个示例中， SetAlarm 过程使用 Application 对象的 OnTime 方法设置 OnTime 事件。该方法接受两个参数：时间（示例中为下午 3 点）和时间到达时要执行的过程（示例中为 DisplayAlarm ）。执行 SetAlarm 后， DisplayAlarm 过程将在下午 3 点被调用。

你还可以相对于当前时间安排事件，例如 20 分钟后：

Application.OnTime Now + TimeValue("00:20:00"), "DisplayAlarm"

也可以在特定日期安排过程，例如在 2013 年 4 月 1 日凌晨 12:01 运行 DisplayAlarm 过程：

Application.OnTime DateSerial(2013, 4, 1) + _
    TimeValue("00:00:01"), "DisplayAlarm"

注意， OnTime 方法还有两个额外的参数，如需使用该方法，可参考在线帮助获取完整详细信息。

以下两个过程展示了如何编程实现重复事件。在这个例子中，单元格 A1 每 5 秒更新一次当前时间：

Dim NextTick As Date

Sub UpdateClock()
'   Updates cell A1 with the current time
    ThisWorkbook.Sheets(1).Range("A1") = Time
'   Set up the next event five seconds from now
    NextTick = Now + TimeValue("00:00:05")
    Application.OnTime NextTick, "UpdateClock"
End Sub

Sub StopClock()
'   Cancels the OnTime event (stops the clock)
    On Error Resume Next
    Application.OnTime NextTick, "UpdateClock", , False
End Sub

需要注意的是， OnTime 事件在工作簿关闭后仍然存在。如果在不运行 StopClock 过程的情况下关闭工作簿，假设 Excel 仍在运行，工作簿将在 5 秒后重新打开。为防止这种情况，可在 Workbook_BeforeClose 事件过程中包含以下语句：

Call StopClock

5.2 OnKey 事件

在工作时，Excel 会不断监控你输入的内容。因此，你可以设置一个按键或按键组合，按下时执行特定过程。这些按键在你输入公式或使用对话框时不会被识别。

需要注意的是，创建响应 OnKey 事件的过程并不局限于单个工作簿，重新映射的按键在所有打开的工作簿中都有效。同时，如果你设置了 OnKey 事件，要确保提供取消该事件的方法，常见的做法是使用 Workbook_BeforeClose 事件过程。

以下示例使用 OnKey 方法设置 OnKey 事件，重新分配 PgDn 和 PgUp 键：

Sub Setup_OnKey()
    Application.OnKey "{PgDn}", "PgDn_Sub"
    Application.OnKey "{PgUp}", "PgUp_Sub"
End Sub

Sub PgDn_Sub()
    On Error Resume Next
    ActiveCell.Offset(1, 0).Activate
End Sub

Sub PgUp_Sub()
    On Error Resume Next
    ActiveCell.Offset(-1, 0).Activate
End Sub

执行 Setup_OnKey 过程后，按下 PgDn 会执行 PgDn_Sub 过程，按下 PgUp 会执行 PgUp_Sub 过程，效果是按下 PgDn 使光标向下移动一行，按下 PgUp 使光标向上移动一行。使用 PgUp 和 PgDn 的按键组合不受影响，例如 Ctrl+PgDn 仍会激活工作簿中的下一个工作表。

通过执行以下过程，可以取消 OnKey 事件并将这些键恢复到正常功能：

Sub Cancel_OnKey()
    Application.OnKey "{PgDn}"
    Application.OnKey "{PgUp}"
End Sub

需要注意的是，使用空字符串作为 OnKey 方法的第二个参数不会取消 OnKey 事件，而是让 Excel 忽略该按键并什么都不做。例如，以下指令让 Excel 忽略 Alt+F4 ：

Application.OnKey "%{F4}", ""

以下是 OnKey 事件中可以使用的键码表：
| 键 | 代码 |
| ---- | ---- |
| Backspace | {BACKSPACE} 或 {BS} |
| Break | {BREAK} |
| Caps Lock | {CAPSLOCK} |
| Delete 或 Del | {DELETE} 或 {DEL} |
| Down Arrow | {DOWN} |
| End | {END} |
| Enter | ∼ (波浪号) |
| Enter (数字小键盘上的) | {ENTER} |
| Escape | {ESCAPE} 或 {ESC} |
| Home | {HOME} |
| Ins | {INSERT} |
| Left Arrow | {LEFT} |
| NumLock | {NUMLOCK} |
| PgDn | {PGDN} |
| PgUp | {PGUP} |
| Right Arrow | {RIGHT} |
| Scroll Lock | {SCROLLLOCK} |
| Tab | {TAB} |
| Up Arrow | {UP} |
| F1 到 F15 | {F1} 到 {F15} |

你还可以指定与 Shift、Ctrl 和 Alt 组合的键。要指定与其他键组合的键，使用以下符号：
- Shift：加号 (+)
- Ctrl：脱字符 (^)
- Alt：百分号 (%)

例如，要将一个过程分配给 Ctrl+Shift+A 键，使用以下代码：

Application.OnKey "^+A", "SubName"

要将一个过程分配给 Alt+F11 （通常用于切换到 VB 编辑器窗口），使用以下代码：

Application.OnKey "%{F11}", "SubName"

6. 禁用快捷菜单

之前提到过 Worksheet_BeforeRightClick 过程可以禁用右键快捷菜单，以下过程放在 ThisWorkbook 代码模块中：

Private Sub Worksheet_BeforeRightClick _
  (ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox "The shortcut menu is not available."
End Sub

用户仍可以通过按下 Shift+F10 显示快捷菜单。为了拦截 Shift+F10 按键组合，在标准 VBA 模块中添加以下过程：

Sub SetupNoShiftF10()
    Application.OnKey "+{F10}", "NoShiftF10"
End Sub

Sub TurnOffNoShiftF10()
    Application.OnKey "+{F10}"
End Sub

Sub NoShiftF10()
    MsgBox "Nice try, but that doesn't work either."
End Sub

执行 SetupNoShiftF10 过程后，按下 Shift+F10 会显示消息框。需要注意的是， Worksheet_BeforeRightClick 过程仅在其所在的工作簿中有效，而 Shift+F10 按键事件适用于所有打开的工作簿。

7. 使用 VBA 处理范围

VBA 可以用于操作工作表范围，以下是一些常见的范围操作示例：

7.1 复制范围

Excel 的宏录制器虽然生成的代码不一定高效，但可以帮助发现相关对象、方法和属性的名称。例如，录制一个简单的复制粘贴操作会生成以下 VBA 代码：

Sub Macro1()
    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

在 VBA 中，不需要选择对象就可以对其进行操作。可以用以下更简单的例程替换上述过程：

Sub CopyRange()
    Range("A1").Copy Range("B1")
End Sub

要将范围复制到不同的工作表或工作簿，只需限定目标范围的引用。例如，将 File1.xlsx 中 Sheet1 的范围复制到 File2.xlsx 中 Sheet2 ：

Sub CopyRange2()
    Workbooks("File1.xlsx").Sheets("Sheet1").Range("A1").Copy _
      Workbooks("File2.xlsx").Sheets("Sheet2").Range("A1")
End Sub

也可以使用对象变量表示范围：

Sub CopyRange3()
    Dim Rng1 As Range, Rng2 As Range
    Set Rng1 = Workbooks("File1.xlsx").Sheets("Sheet1").Range("A1")
    Set Rng2 = Workbooks("File2.xlsx").Sheets("Sheet2").Range("A1")
    Rng1.Copy Rng2
End Sub

复制不限于一次一个单元格，例如复制一个大的范围：

Sub CopyRange4()
    Range("A1:C800").Copy Range("D1")
End Sub

7.2 移动范围

移动范围的 VBA 指令与复制范围类似，只是使用 Cut 方法而不是 Copy 方法。例如，将 18 个单元格（A1:C6）移动到以 H1 开始的新位置：

Sub MoveRange1()
   Range("A1:C6").Cut Range("H1")
End Sub

7.3 复制当前区域

以下宏展示了如何将范围从 Sheet1 复制到 Sheet2 （从单元格 A1 开始），使用了 CurrentRegion 属性：

Sub CopyCurrentRegion2()
    Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
End Sub

使用 CurrentRegion 属性相当于选择“开始”➜“编辑”➜“查找和选择”➜“定位条件”命令并选择“当前区域”选项（或使用 Ctrl+Shift+* 快捷键选择当前区域）。

如果要复制的范围是一个表格（通过选择“插入”➜“表格”➜“表格”指定），可以使用以下代码（假设表格名为 Table1 ）：

Sub CopyTable()
    Range("Table1[#All]").Copy Sheets("Sheet2").Range("A1")
End Sub

7.4 复制可变大小的范围

在很多情况下，需要复制单元格范围，但不知道范围的确切行和列维度。例如，有一个跟踪每周销售的工作簿，每周添加新数据时行数会发生变化。由于不知道任何给定时间的确切范围地址，编写复制范围的宏需要额外的编码。

Excel VBA 事件与范围操作全解析

7.5 选择范围

在 VBA 中选择一个范围很常见。以下是几种选择范围的示例代码：

' 选择单个单元格
Sub SelectSingleCell()
    Range("A1").Select
End Sub

' 选择一个连续的范围
Sub SelectRange()
    Range("A1:C10").Select
End Sub

' 选择不连续的范围
Sub SelectNonContinuousRange()
    Union(Range("A1:C3"), Range("E5:G7")).Select
End Sub

7.6 识别范围中的信息类型

可以通过 VBA 来识别范围中信息的类型，例如判断单元格是文本、数字还是日期等。以下是一个简单的示例，用于判断 A1 单元格的信息类型：

Sub IdentifyCellType()
    Dim cell As Range
    Set cell = Range("A1")
    If IsNumeric(cell.Value) Then
        MsgBox "单元格包含数字"
    ElseIf IsDate(cell.Value) Then
        MsgBox "单元格包含日期"
    ElseIf IsEmpty(cell.Value) Then
        MsgBox "单元格为空"
    Else
        MsgBox "单元格包含文本"
    End If
End Sub

7.7 提示输入单元格值

有时需要提示用户输入单元格的值。以下代码会弹出一个输入框，让用户输入值并将其赋值给 A1 单元格：

Sub PromptForCellValue()
    Dim inputValue As Variant
    inputValue = InputBox("请输入一个值")
    If inputValue <> "" Then
        Range("A1").Value = inputValue
    End If
End Sub

7.8 确定列中的第一个空单元格

在处理数据时，常常需要找到列中的第一个空单元格。以下代码可以实现这个功能：

Sub FindFirstEmptyCell()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    MsgBox "第一列的第一个空单元格是第 " & lastRow & " 行"
End Sub

7.9 暂停宏以允许用户选择范围

有时候需要让用户手动选择一个范围。以下代码会暂停宏，等待用户选择范围，然后将选择的范围地址显示出来：

Sub PauseForRangeSelection()
    Dim selectedRange As Range
    On Error Resume Next
    Set selectedRange = Application.InputBox("请选择一个范围", Type:=8)
    On Error GoTo 0
    If Not selectedRange Is Nothing Then
        MsgBox "你选择的范围是 " & selectedRange.Address
    End If
End Sub

7.10 计算范围中的单元格数量

可以使用 VBA 计算一个范围中的单元格数量。以下代码计算 A1:C10 范围中的单元格数量：

Sub CountCellsInRange()
    Dim cellCount As Long
    cellCount = Range("A1:C10").Cells.Count
    MsgBox "范围 A1:C10 中的单元格数量是 " & cellCount
End Sub

7.11 遍历范围中的单元格

遍历范围中的每个单元格并进行相应操作是常见的需求。以下代码遍历 A1:C10 范围中的每个单元格，并将其值乘以 2：

Sub LoopThroughCells()
    Dim cell As Range
    For Each cell In Range("A1:C10")
        If IsNumeric(cell.Value) Then
            cell.Value = cell.Value * 2
        End If
    Next cell
End Sub

8. 创建自定义函数

除了使用 VBA 进行范围操作和处理事件外，还可以创建自定义函数，这些函数可以在 VBA 过程中使用，也可以在工作表公式中使用。

8.1 在 VBA 过程中使用的自定义函数

以下是一个简单的自定义函数，用于计算两个数的和：

Function AddTwoNumbers(num1 As Double, num2 As Double) As Double
    AddTwoNumbers = num1 + num2
End Function

Sub TestAddTwoNumbers()
    Dim result As Double
    result = AddTwoNumbers(5, 3)
    MsgBox "5 和 3 的和是 " & result
End Sub

8.2 在工作表公式中使用的自定义函数

可以创建在工作表公式中使用的自定义函数。以下是一个计算字符串长度的自定义函数：

Function StringLength(str As String) As Long
    StringLength = Len(str)
End Function

在工作表中，可以像使用内置函数一样使用 StringLength 函数，例如在单元格中输入 =StringLength("Hello") 会返回 5。

9. Windows API 调用

在某些情况下，可能需要调用 Windows API 函数来实现一些特殊功能。例如，使用 Windows API 函数来弹出一个系统消息框：

Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long

Sub ShowSystemMessageBox()
    Dim result As Long
    result = MessageBox(0, "这是一个系统消息框", "提示", 0)
End Sub

总结

通过以上内容，我们详细介绍了 Excel VBA 中的事件处理、范围操作、自定义函数创建以及 Windows API 调用等方面的知识。掌握这些内容可以让你更加高效地使用 VBA 来处理 Excel 中的各种任务，无论是自动化数据处理、自定义功能实现还是与系统进行交互等。希望这些示例和技巧能够帮助你在 Excel VBA 编程的道路上更进一步。

下面是一个简单的 mermaid 流程图，展示了复制范围的基本流程：

graph TD;
    A[开始] --> B[指定源范围];
    B --> C[指定目标范围];
    C --> D[复制范围];
    D --> E[结束];

同时，为了方便大家回顾，这里再次列出一些重要的操作和对应的代码：
| 操作 | 代码示例 |
| ---- | ---- |
| 复制范围 | Range("A1").Copy Range("B1") |
| 移动范围 | Range("A1:C6").Cut Range("H1") |
| OnTime 事件设置 | Application.OnTime TimeValue("15:00:00"), "DisplayAlarm" |
| OnKey 事件设置 | Application.OnKey "{PgDn}", "PgDn_Sub" |