RegEx Extraction #NAME error

Question

I'm trying to create my own VBA Function based on the one in step two at this link. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

However, I get a #NAME error. Where am I going wrong here? I've tested my RegEx on https://regex101.com.

Function extractGroupName(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 = "^.*Name:(.*);Id"

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

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

        If regEx.Test(strInput) Then
            extractGroupName = regEx.Replace(strInput, "$1")
        Else
            extractGroupName = "ERROR: NOT FOUND"
        End If
    End If
End Function

Are you calling this function from a worksheet cell, and the return in the cell is #NAME? If that is the case, where did you put the code? It should normally be in a Regular module in the same workbook. Did you name the module with the same name as the function? Also, did you set a reference to Regular Expressions under tools/references? — Ron Rosenfeld, Jan 06 '16 at 15:33
Yes, I am calling this from a worksheet cell, and the return in the cell is #NAME. The code is in a module in the workbook. The module and function do have the same name. I have turned on the reference to regular expressions. Everything works fine when I copy-paste the code in step 2 of the link I provided verbatim, however when I do this, it causes the #NAME error, implying it's an issue with code, not naming schema, location, or references. — Haybuck, Jan 06 '16 at 15:37
Sorry, just edited my original reply. Accidentally hit enter early. — Haybuck, Jan 06 '16 at 15:39
If your module and function have the same name, Excel does not know which you are referring to. So they need to have different names, or you need to use a fully qualified name. — Ron Rosenfeld, Jan 06 '16 at 15:42
I will add that as an answer in the thread, with a more full explanation and suggestions — Ron Rosenfeld, Jan 06 '16 at 15:46

score 0 · Answer 1 · answered Jan 06 '16 at 15:34

0

Looks to me that you are missing the reference to the Reg Expression library. I think this is Microsoft VBScript Regular Expressions in the References dialog box (Tools:Referneces in the VBIDE)...

answered Jan 06 '16 at 15:34

PaulG

1,051
7
9

Except when I use the one from the example verbatim, it works. I've already turned on the reference. – Haybuck Jan 06 '16 at 15:35
If there were a missing reference, it would give a compile error in the vbe, not a #NAME error in the cell. – Ron Rosenfeld Jan 06 '16 at 15:41
Ah, so if you execute in the Immediate window (a.k.a. the Debug Window) it works, right? Make sure the function is in a Module and not in the worksheet class. This will also cause that. – PaulG Jan 06 '16 at 15:42

score 0 · Accepted Answer · answered Jan 06 '16 at 15:48

It seems you have named the module and the function the same. When that happens, Excel doesn't know which you mean, so returns the #NAME error. There are two fixes:

Change the name of one or the other. I will frequently prefix all regular module names with mod so as to avoid this problem: modextractGroupName
Call the function with a fully qualified name:

=extractGroupName.extractGroupName(A1)

RegEx Extraction #NAME error

2 Answers2