Compile error: method or data member not found when trying to run a code from a top StackOverflow answer

Question

This is my first experience with VBA. I wanted to use regular expression in Excel and found the following StackOverflow question.

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

I added "Microsoft VBScript Regular Expressions 5.5" to my references in the VBA interface, and typed in 12abc in cell A1 as in the exmple. I then did Insert -> Module and copied the following code to the code box:

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

However, when I type in =simpleCellRegex(A1) in cell B1 I get the error

Compile error: method or data member not found

And in the debugger mode the first line of the code is highlighted.

Someone in the comments to the answer mentioned that an error might occur if the code is in ThisWorkbook, but mine in is Modules.

You don't have Regular Expressions 1.0 also turned on do you? — Warcupine, Oct 10 '19 at 18:10
@Warcupine Huh, apparently it was turned on. And now everything works. Thank you! — Antsushi, Oct 10 '19 at 18:14

rickmanalexander · Answer 1 · 2019-10-10T18:29:38.560

1

Consider using Late Binding by calling the CreateObject Function. It avoids possible bugs associated with mistakenly referencing unneeded libraries or cross referencing them. See below:

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

edited Oct 10 '19 at 18:29

answered Oct 10 '19 at 18:15

rickmanalexander

591
1
4
16

Thank you, this seems to be useful for any future endeavors in VBA. Oddly (or maybe not, I don't know), when I turn on Regular Expressions 1.0 after having them turned off (so both 5.5 and 1.0 are working) the code executes just fine. But if I first select 1.0 and then turn on 5.5 I get the error. – Antsushi Oct 10 '19 at 18:23
1

@Antsushi You can set the priority order of references, the first clicked has a higher priority so any conflicting references now default to 1.0. You can change this with the arrows to the right of the list. – Warcupine Oct 10 '19 at 18:31
1

@Antsushi ^^^^ what @Warcupine said. `CreateObject` in my opinion is still the better way to go in the long run. – rickmanalexander Oct 10 '19 at 18:34

mdelapena · Answer 2 · 2019-10-10T18:23:00.227

Make sure the reference was added to the module and workbook where you are trying to execute the function

the module must be selected in order to add the reference to that specific module:

here it shows that my Module1 has the reference "Microsoft VBScript Regular Expressions 5.5"

and if said reference is unchecked, you'll get this error: Compile error: method or data member not found

this is the result I got:

Compile error: method or data member not found when trying to run a code from a top StackOverflow answer

2 Answers2