编译错误：尝试运行RegEx代码时找不到方法或数据成员

vbopmzt1 于 2023-03-31 发布在其他

关注(0)|答案(2)|浏览(129)

我发现了下面的StackOverflow问题。
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
我在VBA接口中的引用中添加了“Microsoft VBScript Regular Expressions 5.5”，并在单元格A1中键入12abc，如示例所示。然后我执行Insert -〉Module并将以下代码复制到代码框中：

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

当我在单元格B1中输入=simpleCellRegex(A1)时，我得到错误
编译错误：未找到方法或数据成员
在调试器中，突出显示代码的第一行。
有人在回答的评论中提到，如果代码在ThisWorkbook中，但我的代码在Modules中，可能会发生错误。

regex

来源：https://stackoverflow.com/questions/58328694/compile-error-method-or-data-member-not-found-when-trying-to-run-regex-code

2条答案

按热度按时间

wz3gfoph1#

考虑通过调用CreateObject函数来使用Late Binding。它可以避免错误地引用不需要的库或交叉引用它们可能带来的错误。请参见下面：

Public Function simpleCellRegex(ByRef Myrange As Range) As String

    Dim regEx As Object
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "^[0-9]{1,3}"

    'late binding
    Set regEx = CreateObject("VBScript.RegExp")

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx

            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If

    End If
End Function

赞(0）回复(0）举报 2023-03-31

vatpfxk52#

确保引用已添加到您尝试执行函数的模块和工作簿中
必须选择该模块才能将引用添加到该特定模块：