用于在VBA中测试一个或多个空格以在Excel单元格中找到匹配项的表达式 [英] Expression to test for one or more spaces in VBA to find matches in Excel cells

问题描述

我正在尝试在Excel文档的宏中使用正则表达式.

I am trying to use regular expressions in a macro for an Excel document.

这是我到目前为止所拥有的:

Here is what I have so far:

dim regex
regex.pattern = "\s+"
for p = 3 to totalparamcount
    if (regex.test(Worksheets("table").Cells(p,1)) then
        msgbox ("blah blah")
    end if
next

我要匹配的模式是一个或多个空格，制表符，换行符，我看到的是用"\s"表示.

The pattern I am trying to match is one or more spaces, tabs, newlines, which I saw is denoted by "\s".

行'if (regex.test(Worksheets("table").Cells(p,1)) then'出现此错误:

Run-time error '424':

Object required

我正在Excel中使用VBA 7.0.

I am using VBA 7.0 in Excel.

推荐答案

您需要创建regexp对象，即

You need to create the regexp object, ie

Dim regex
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = "\s+"

这篇关于用于在VBA中测试一个或多个空格以在Excel单元格中找到匹配项的表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！