Regexp producing #NAME? error in UDF

Question

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.

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.

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.

Thanks for any help!

Did you try to call your function from another macro passing a range? This could help with debugging. If your function is not supported, it most likely contains an error. — Fuzzzzel, Feb 04 '16 at 20:29
Did you add a module to your workbook and define the function there? Functions defined in sheets do not work — Code Different, Feb 04 '16 at 20:33
@Fuzzzzel I actually took the entire function from a highly voted thread on this website - http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops. — Ryan, Feb 04 '16 at 20:34
@CodeDifferent Ahh, I know that I defined it in a sheet. What is the purpose of adding a module and defining them there? I just followed the steps in the link I just posted in another comment, but it never mentioned anything like that. — Ryan, Feb 04 '16 at 20:36
Any Function to use as a UDF needs to be in a module and not attached to a sheet or ThisWorkbook. — Scott Craner, Feb 04 '16 at 20:38
From **[the link you provided](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops)** - *Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below (e.g. naming the Module Regex and the function regex causes #NAME! errors).* — , Feb 04 '16 at 20:44
@ScottCraner I placed it inside of a module, but it won't appear in the Macro section, nor will it still work if I try to use it in-cell (=simpleCellRegex(A1)) — Ryan, Feb 04 '16 at 20:45
@Jeeped I assumed that was a second answer, not a continuation of the first. My bad! — Ryan, Feb 04 '16 at 20:46
It is **not** a macro; it is a **User Defined Function** and will not appear in the macro list. Just start typing it into the formula bar and it will appear in a list. — , Feb 04 '16 at 20:47
Your macro works if you place it into the Module, remove it from the Worksheet, add reference to MS VBscript Regex 5.5, put something like `;123456eee` into B1, and put `=simpleCellRegex(B1)` into A1. Just tested, A1 shows `eee`. — Wiktor Stribiżew, Feb 04 '16 at 20:52
@WiktorStribiżew When I just tried it, I got a user defined type not defined error. — Ryan, Feb 04 '16 at 20:54
@Ryan You got the compile error because you did not set a reference to Microsoft VBScript Regular Expressions 5.5 as instructed — Ron Rosenfeld, Feb 04 '16 at 21:05
@RonRosenfeld No, I did do that. It wasn't checked when I went back and looked, so I don't know what unchecked it. It's working now though. Thank you :) — Ryan, Feb 04 '16 at 21:13

score 1 · Answer 1 · edited May 23 '17 at 11:59

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":

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

edited May 23 '17 at 11:59

Community

1
1

answered Feb 04 '16 at 20:37

Fuzzzzel

1,733
3
23
37

This is not true. I tested it in Excel. Actually if the function is not free from errors (and without the reference `RegExp` will not be recognized) it will not be able to be called and by this will display #NAME? – Fuzzzzel Feb 04 '16 at 20:44
1

If placed in a regular module, with no reference set, then it returns a `user defined type not defined` compile error. Of course, if you incorrectly put it in a sheet module, then you'll get the #NAME! error. – Ron Rosenfeld Feb 04 '16 at 20:49
There is no issue in the OP code, just tested it and it works. – Wiktor Stribiżew Feb 04 '16 at 20:54
Correct and as you just see, the original poster tells us, that theres a `user defined type not defined error` ... this again means, you will not be able to call the function in the sheet. – Fuzzzzel Feb 04 '16 at 20:56
I have just got this same error when removed the reference to the Microsoft VBScript Regular Expressions 5.5 library. There is no point multiplying the same answers then. I'd rather vote for a dupe and leave a comment. – Wiktor Stribiżew Feb 04 '16 at 21:00
@Fuzzzzel The error **is** stated in the **title** of his post – Ron Rosenfeld Feb 04 '16 at 21:02
Right, you also need to define the variable a little different. That's what is stated in the article behind the link: Dim regEx As New VBScript_RegExp_55.RegExp – Fuzzzzel Feb 04 '16 at 21:02

score 1 · Answer 2 · answered Feb 05 '16 at 01:28

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

Regexp producing #NAME? error in UDF

2 Answers2