Microsoft Excel 中的 VBA RegEx

Microsoft Excel VBA (Visual Basic for Applications) 中的正则表达式 (RegEx) 是比传统 Excel 函数更高级地搜索和操作文本的工具。想象一下，它是处理海量数据集的超能力。正则表达式是一种模式或一组规则，可帮助我们查找字符串中存在的特定类型的文本，例如日期、电子邮件地址，甚至是简单的单词。RegEx 通过定义一个与文本特定特征匹配的模式来工作。它可以查找特定字母、数字、符号，甚至是它们的组合。

• 例如， 假设我们在定义的 Microsoft Excel 工作表中有一个电子邮件地址列表，我们想检查每个电子邮件地址的格式是否正确。因此，为此，我们无需手动检查每个电子邮件地址，我们可以使用 RegEx 函数来自动查找和验证所有这些电子邮件模式。

在 VBA 中，RegEx 被称为一个对象，它使我们能够处理所有这些模式。尽管它一开始可能看起来很复杂，但它与其他 Microsoft Excel 函数更相似，但功能更强大。如果我们熟悉 Excel 函数，例如 LEFT、RIGHT 或 MID，RegEx 非常相似，只是它允许更灵活和详细的文本匹配。例如，我们还可以使用 RegEx 查找以某个单词开头、包含数字或匹配特定规则的文本。这就像拥有一个搜索功能，它不仅能理解精确匹配，还能查找模式。为了在 Microsoft Excel VBA 中有效地使用它，我们必须创建一个 RegEx 对象，然后我们还需要设置要搜索的模式，然后告诉 VBA 在找到匹配项时该怎么做。

尽管 VBA RegEx 看起来很复杂，但一旦我们了解了它的基础知识，它只是在选定的电子表格中与文本交互的另一种方式，使处理复杂任务变得更容易，例如以更自动化、更高效的方式验证、提取或替换数据。

RegEx 具有以下参数和属性

1. Pattern：在要进行操作的相应 RegEx 中定义的相应模式。
2. IgnoreCase：它将忽略所选字母的大小写（字符串中相同的大写/非大写）。
3. Global：它负责允许个人查找所有可能的匹配项，如果设置为 TRUE。如果设置为 FALSE，则在这种情况下它将只有效地找到第一个匹配项。
4. MultiLine：它允许匹配跨多行的字符串中的模式。

此外，相应的 RegEx 促进以下三种操作

1. Test：此特定操作测试所述模式是否存在于字符串中。
2. Replace：它用于将所有原始模式替换为另一个模式。
3. Execute：它有助于返回分别搜索到的所有匹配模式。

主要收获

1. VBA 中的 RegEx：VBA 中的正则表达式使我们能够根据特定模式搜索和操作文本，使其成为验证输入、提取数据和文本替换等任务的理想选择。
2. 启用 RegEx：为了在 VBA 中使用 RegEx，我们将启用 Microsoft VBScript Regular Expressions 库。这可以通过进入 VBA 编辑器、访问引用部分并添加库来有效提供 RegExp 对象。
3. RegExp 对象方法:
   • .Match()：它专门用于查找并返回字符串中模式的第一个匹配项。它通常以有效方式提供详细信息，例如匹配的字符串及其位置。
   • .Execute()：它搜索整个字符串，并且尽管如此，它还返回模式的所有匹配项，从而允许我们一次处理多个结果。
4. 测试正则表达式：在广泛使用正则表达式之前，彻底测试它们非常重要。相应的正则表达式可能很复杂，一个小错误可能导致意想不到或不正确的结果。
5. 性能注意事项：但是，相应的 RegEx 操作可能占用大量资源，尤其是在处理大量数据集时。在大型字符串或大量项目上运行所有这些操作可能会减慢程序速度。优化正则表达式的效率对于避免性能问题非常重要。
6. RegEx 用例：RegEx 在各种场景中都很有用，例如验证用户输入、从文本中提取特定信息以及替换字符串的某些部分。它对于以高效方式自动化各种重复的文本相关任务非常有用。
7. 模式优化：为确保最佳性能，我们必须定期审查和优化正则表达式。高效的模式不仅可以提高速度，还可以降低错误风险，使我们的代码更可靠。
8. 注重细节：正则表达式模式中的小错误可能导致不正确的结果或遗漏匹配。因此，我们需要关注模式的每个部分，然后我们需要在有效实现之前进行彻底测试。

VBA RegEx 模式

我们都知道，Microsoft Excel VBA 中特定的 RegEx 模式是通过使用特殊字符、表示不同字符的语法和操作的组合来定义的。现在我们将看到下面列表中嵌入其描述的最常用的 RegEx 字符。

以下是 VBA 中 RegEx 模式的一些示例

RegEx 对象的属性和方法

尽管如此，特定的 VBA RegEx 模式主要通过使用 RegExp 对象来定义。但是，RegExp 对象具有一些属性和方法，我们可以轻松地使用它们来控制其行为，并且还需要对我们正在搜索的文本执行操作。以下是 RegExp 对象的一些最常用的属性和方法

VBA RegEx 对象的属性

在 Microsoft Excel VBA 中公开使用的 VBA RegEx 对象的各种属性如下

• .Pattern：它有助于以有效方式获取或设置正则表达式模式。
• .IgnoreCase：获取或设置一个值，指示正则表达式是否区分大小写。
• .Global：它负责设置一个值，指示正则表达式是否为全局（匹配所有出现）。

RegEx 对象的方法

• .Test(input)：如果正则表达式模式与输入字符串的任何部分匹配，则返回 True；否则，它将返回 False。
• .Execute(strInput)：这将负责针对输入字符串执行正则表达式模式，然后返回一个包含所有匹配项的 MatchCollection 对象。
• .Replace(strInput, strReplacement)：它将输入字符串中正则表达式模式的所有出现替换为指定的替换字符串，然后以有效方式返回修改后的字符串。
• .Match(strInput)：它搜索输入字符串中正则表达式模式的第一次出现，然后返回一个包含匹配信息的 Match 对象。
• .Matches(strInput)：它基本上搜索输入字符串中正则表达式模式的所有出现，并返回一个包含每个匹配信息的 MatchCollection 对象。

示例

现在，在本教程中，我们将进一步探讨如何有效地使用正则表达式 (RegEx) 在 Microsoft Excel VBA 中从非结构化文本列中提取电子邮件地址。A 列将包含电话号码和电子邮件地址的混合，我们将编写 VBA 代码以仅提取电子邮件地址，然后将其有效地输出到单独的列 (B 列) 中。

在 Microsoft Excel VBA 中积极使用 RegEx 提取电子邮件的分步指南

第 1 步：打开 VBA 编辑器：我们将从打开 Microsoft Excel VBE 编辑器开始。为此，我们必须按下键盘上的快捷按钮，即 Alt + F11。这将打开 Visual Basic for Applications (VBA) 编辑器，我们可以在其中有效地编写和管理代码。

第 2 步：启用 RegEx 库：在 Microsoft Excel VBA 中使用 RegEx 之前，我们需要启用 Microsoft VBScript Regular Expressions 5.5 库。为此，我们需要执行以下操作

• 在 VBA 编辑器中，单击顶部菜单中的“工具”。
  
• 之后，我们将从下拉列表中选择。
  
• 滚动可用库列表并选中 Microsoft VBScript Regular Expressions 5.5。
  
• 单击“确定”选项进行确认。

此特定步骤确保我们可以访问必要的功能，以有效地使用 VBA 中的正则表达式。

第 3 步：插入新模块：在此步骤中，我们需要插入一个新模块，我们将在其中编写代码。为此

• 在相应的 VBA 编辑器中，我们需要单击顶部菜单中的“插入”。
• 之后，我们将从下拉列表中选择模块。新模块将显示在编辑器中，我们可以在其中输入代码。

第 4 步：声明所有必要的变量：在此特定步骤中，我们将有效地声明代码中使用的 变量。我们需要

• 需要一个 RegExp 对象来处理正则表达式。
• MatchCollection 通常用于存储搜索结果。
• Match 对象用于保存每个匹配项。
• 字符串模式用于分别定义电子邮件地址的正则表达式。
• 字符串输入以保存每个单元格的内容。
• Range 对象用于引用 A 列中的单元格。
• 此外，相应的单元格对象循环遍历范围内的每个单元格。

此步骤的代码

第 5 步：定义电子邮件 ID 的正则表达式模式：现在在此步骤中，我们将定义用于识别电子邮件地址的正则表达式模式。

匹配电子邮件地址的模式如下

• strPattern = "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}"

此模式分解如下

• [a-zA-Z0-9._%+-]+：匹配电子邮件的本地部分（@ 符号之前）。此部分允许字母、数字和某些特殊字符，如句点、下划线和加号。
• @：它负责有效匹配将本地部分与电子邮件地址的域部分分开的 @ 符号。
• [a-zA-Z0-9.-]+：匹配域名（@ 之后的部分）。它可以包含字母、数字、句点和连字符。
• \.：匹配域名中的字面句点 (.)。
• [a-zA-Z]{2,}：匹配顶级域（如 .com 或 .org），它必须至少有两个字母长。

第 6 步：获取 A 列中的单元格范围：在此步骤中，我们只需定义 A 列中包含我们要搜索电子邮件地址的文本的单元格范围。此外，我们可以使用 Cells(Rows.Count, "A").End(xlUp) — 行技术来获取 A 列中包含数据的最后一行。

然后我们将使用 For Each 循环遍历范围内的每个单元格

第 7 步：在每个单元格上执行正则表达式：现在，对于范围内的每个单元格，我们获取其值（非结构化文本），然后执行 RegEx 模式以搜索电子邮件地址。Execute 方法返回在文本中找到的所有匹配项的集合。

第 8 步：循环遍历每个匹配项并打印电子邮件 ID：执行 RegEx 模式后，相应的匹配集合包含在当前单元格中找到的所有匹配项。通常，我们循环遍历每个匹配项，然后使用 Offset() 方法将电子邮件地址打印在相邻单元格（B 列）中。

Offset(0, 1) 方法将一列（位于 A 列中当前单元格右侧的 B 列）移动，并将相邻单元格的值设置为匹配的电子邮件地址。

第 9 步：保存模块：编写完代码后，我们需要通过单击顶部菜单中的“文件”并保存来保存模块。并且我们还需要确保将工作簿保存为 Macro-Enabled Workbook (.xlsm)，以便以后也可以执行宏。

第 10 步：运行宏：在此步骤中，要有效地运行宏，我们需要按下 Alt + F8 以打开“宏”对话框。然后，我们需要选择名为 ExtractEmails 的宏，然后单击运行。

第 11 步：检查 B 列中的结果：运行宏后，我们必须检查 B 列中的值。尽管如此，我们还需要查看从 A 列中可用的文本中提取的所有电子邮件地址。

完整代码

这是完整的 VBA 代码

此外，这个简单的 VBA 代码主要演示了我们如何轻松地使用正则表达式来从 Microsoft Excel 中选定的单元格范围中提取电子邮件地址。通过遵循所有这些步骤，我们可以轻松识别并从大量非结构化数据中提取电子邮件地址，从而节省数据处理任务中的时间和精力。

需要记住的要点

在使用 Microsoft Excel VBA 中的相应 RegEx 时，存在一些需要记住的重要点。

1. 使用 VBA RegEx Test 方法，如果模式与输入字符串匹配，我们将得到布尔值 True，否则得到 False。当我们要检查字符串是否与模式匹配时，此方法很有用。
2. 还必须记住，在使用 VBA Regex Replace 函数和 RegExp 对象的 .Replace() 方法时，个人必须通过在替换字符串中使用 $1、$2 等来引用模式中的捕获组。
   • 例如，假设我们有一个带有两个捕获组的模式，并且我们想将匹配的文本替换为第二个捕获组，然后是第一个捕获组。尽管如此，我们还可以使用替换字符串 "$2 $1" 来实现此目的。

常见问题

问题 1：提到了 Microsoft Excel VBA RegEx 中 `Test` 和 `Execute` 之间的主要区别是什么？

答案：Test：这会检查我们正则表达式中的模式是否与我们正在查找的文本的任何部分匹配。如果测试条件匹配，我们将得到输出 `True` 值，否则我们将得到 `False` 值作为结果。

Execute：这会在文本上运行正则表达式，并为我们提供它找到的所有匹配项的详细列表。它不仅仅告诉我们是否存在匹配；尽管如此，它还会为我们提供有关这些匹配项在文本中的位置的更多信息。

所以，Test 就像一个快速的“是”或“否”答案，而 Execute` 则为我们提供了所有匹配项的完整细分。

问题 2：如何在 VBA RegEx 中转义字符？

答案：在正则表达式中，某些字符，如句点 (`.`) 或星号 (`*`)，具有特殊含义。如果实际上我们想字面地使用这些字符（就像我们指的是实际的句点，而不是“任何字符”），我们也需要转义它们。我们可以通过在字符前面添加反斜杠 (`\`) 来实现这一点。

• 例如，如果我们要找出实际的句点 (`.`)，那么在这种特定情况下，我们需要在选定的模式集中使用相应的 `\.`。这样，正则表达式就知道我们指的是字面句点，而不是“通配符”字符。