Regexp生成#NAME？ UDF中的错误

I've found many things online that show me how to create the function and how to implement it, but nothing is helping me figure out why it won't accept the function's name.

我在网上找到很多东西,告诉我如何创建函数以及如何实现它,但没有什么能帮助我找出它为什么不接受函数的名称。

I opened the Visual Basic section under developer. I've entered my code and I assume that is it? Ctrl + S only makes me save the sheet, not the code.

我在开发人员下打开了Visual Basic部分。我输入了我的代码,我认为是这样的吗? Ctrl + S只让我保存工作表,而不是代码。

The purpose of my code is to take a string and remove the first 7 characters, one of which will be a ; and the following 6 will be random numbers. I have some more fixing to do, such as removing 4 random characters from the end, but I wanted to test it out first.

我的代码的目的是取一个字符串并删除前7个字符,其中一个将是a;以下6个是随机数。我还有一些工作要做,比如从最后删除4个随机字符,但我想先测试一下。

Here is my code:

这是我的代码:

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]{6}"

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

I'm not sure if there is a step that I am missing that will allow excel to accept my code.

我不确定是否有一个我错过的步骤,允许excel接受我的代码。

Thanks for any help!

谢谢你的帮助!

2 个解决方案

#1

Check out this thread. You most likely missed to add a reference to Microsoft VBScript Regular Expressions 5.5. You find it under "How to use":

看看这个帖子。您很可能错过了添加对Microsoft VBScript Regular Expressions 5.5的引用。你可以在“如何使用”下找到它:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

如何在Microsoft Excel中使用正则表达式(正则表达式)在单元格和循环中

#2

I have recut the code below to clean up the variables and use late binding

我已经重新编写下面的代码来清理变量并使用后期绑定

Also your current code doesnt test for the user taking more than one cell into the range.

此外,您当前的代码不会测试用户将多个单元格放入该范围内。

Function simpleCellRegex(Myrange As Range) As String
Dim regEx As Object
Dim strPattern As String
Dim strReplace As String

Set regEx = CreateObject("vbscript,regexp")

strPattern = "^[;][0-9]{6}"
If Len(strPattern) = 0 Then Exit Sub

simpleCellRegex = "Not matched"
strReplace = vbNullString

With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
    If .test(Myrange) Then simpleCellRegex = .Replace(Myrange.Value2, strReplace)
End With

End Function

#1