Excel上的VBA错误:找不到方法或数据成员 [英] VBA Error on Excel: Method or Data Member not found

问题描述

我的代码:

 公共子splitUpRegexPattern()昏暗的regEx作为新的RegExpDim strPattern作为字符串昏暗的strInput作为字符串昏暗的strReplace作为字符串昏暗的Myrange作为范围设置Myrange = ActiveSheet.Range("B2:B4279")对于Myrange中的每个cstrPattern =([[AZ] {2} \/[AZ] {2} \/[AZ] [0-9] {2} \/[az] {3} [0-9] {9} \/)([0-9] {4})"如果strPattern<>"然后strInput = c.ValuestrReplace ="$ 1"使用正则表达式.Global =真.MultiLine =真.IgnoreCase =假.Pattern = strPattern结束于如果regEx.test(strInput)然后c.Offset(0，1)= regEx.Replace(strInput，"$ 2")别的c.Offset(0，1)="万一万一下一个 

结束子

最初运行良好，会给我一个错误，但仍然可以完成它正在执行的任务.但是当我在新的电子表格上使用此宏时，它给了我错误:

编译错误:找不到方法或数据成员.

此站点上的所有解决方案都是针对不同情况量身定制的，因此很遗憾，我无法将其应用于我的情况.

我不知道为什么会这样，因为我没有更改任何代码.我敢肯定，如果我对VBA脚本有了更深入的了解，就能理解这一点，但是我没有，所以我来看看这里是否有人可以帮助我！

谢谢！

艾丹

解决方案

您需要添加对名为" Microsoft VBScript正则表达式5.5 "的库的引用，才能使其正常工作.

如果此代码在工作簿中有效，仅表示您已添加该库引用，则在将代码复制到新工作簿中时，需要再次添加相同的引用.

在现有代码中，您将自动实例化名为redEx的变量，该变量假定已经添加了库引用以使其正常工作.

为避免这种情况，您可以使用后期绑定技术，该技术不需要您添加引用，并且代码可以在任何工作簿上使用.

为此，将regEx变量声明为如下所示的对象...

  Dim regEx作为对象设置regEx = CreateObject("VBScript.RegExp") 

My code:

Public Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("B2:B4279")

For Each c In Myrange
    strPattern = "([A-Z]{2}\/[A-Z]{2}\/[A-Z][0-9]{2}\/[a-z]{3}[0-9]{9}\/)([0-9]{4})"

    If strPattern <> "" Then
        strInput = c.Value
        strReplace = "$1"

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

        If regEx.test(strInput) Then
            c.Offset(0, 1) = regEx.Replace(strInput, "$2")
        Else
            c.Offset(0, 1) = ""
        End If
    End If
Next

End Sub

It was initially working well, it would give me an error, but still complete the task it was doing. But when I use this macro on a new spreadsheet, it gives me the error:

Compile Error: Method or Data Member not found.

All the solutions on this site are tailored to different situations, so I couldn't apply them to my circumstance unfortunately.

I have no idea why this is happening, as I haven't changed any of the code. I am sure if I had a greater understanding with VBA script that I would be able to understand this, but I do not, so I came to find out if someone here could help me!

Thanks!

Aydan

解决方案

You need to add a reference to a library called "Microsoft VBScript Regular Expressions 5.5" to make it work.

If this code works in a workbook that simply means you have added that library reference and when you copy the code to a new workbook there you will need to add the same reference again.

In the existing code you are auto instantiating the variable called redEx which assumes that the library reference has been already added to make it work properly.

To avoid this, you may use the late binding technique which will not require you to add the reference and the code will work on any workbook.

To do so declare the regEx variable as an Object like below...

Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")

这篇关于Excel上的VBA错误:找不到方法或数据成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！