Function returns cells value not cells location - VBA

Question

I'm trying to setup a function for Regex which returns a cell.address. It's 99% working except it seems to return the value of the cell test vs the cell location and I can't figure it out.

Debugging:

It says Object variable or with block variable not set if I Dim celladdr As Range

but if I comment that out, then the error changes to Object doesn't support this property or method and I can see that celladdr = test.

I then tried Set celladdr = Range(celladdr.Address) and get Object Required.

Can anybody point out the error?

Here is some stripped down code: Note, I hardcoded the RegEx pattern as that function works as expected, the problem seems to be in the RegExSearch function, but I can add more code back in if needed.

Public Sub TESTING()
  Dim celladdr As Range
  celladdr = RegExFunc("TEST")
  ActiveSheet.celladdr.Select
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function RegExFunc(var) As Variant
  RegExSearchPattern = RegExPattern(var)
  RegExFunc = RegExSearch(RegExSearchPattern)
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function RegExPattern(my_string) As Variant
  RegExPattern = "([a-z]{4})"
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function RegExSearch(strPattern) As Range
    Dim regexp As Object
    Dim rcell As Range, rng As Range
    Dim strInput As String

    Set regexp = CreateObject("vbscript.regexp")
    Set rng = Range("A1:Z255")

    For Each rcell In rng.Cells

        If rcell <> "" Then

            If strPattern <> "" Then
            strInput = rcell.Value

            With regexp
                .Global = False
                .MultiLine = False
                .ignoreCase = True
                .Pattern = strPattern
            End With

            If regexp.Test(strInput) Then
                MsgBox rcell & " Matched in Cell" & rcell.Address
                Set RegExSearch = Range(rcell.Address)
                MsgBox RegExSearch
            End If
            End If
        End If
    Next
End Function

Answer 1

There were various instances in which you needed to Set the object and one in which a Dim was needed. Since you declared almost everything a Variant, it makes it a bit harder to pinpoint the breakdown. It's best, in my experience to always specifically declare the variables in VBA.

I ran the following adjusted code (added a few sets as noted above) and it worked without any decompile or run time errors.

Public Sub TESTING()
  Dim celladdr As Range
  Set celladdr = RegExFunc("TEST")
  celladdr.Select
End Sub

Public Function RegExFunc(var As String) As Range
Dim RegExSearchPattern As String
  RegExSearchPattern = RegExPattern(var)
  Set RegExFunc = RegExSearch(RegExSearchPattern)
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function RegExPattern(my_string As String) As String
  RegExPattern = "([a-z]{4})"
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function RegExSearch(strPattern As String) As Range
    Dim regexp As Object
    Dim rcell As Range, rng As Range
    Dim strInput As String

    Set regexp = CreateObject("vbscript.regexp")
    Set rng = Range("A1:Z255")

    For Each rcell In rng.Cells

        If rcell <> "" Then

            If strPattern <> "" Then
            strInput = rcell.Value

            With regexp
                .Global = False
                .MultiLine = False
                .ignoreCase = True
                .Pattern = strPattern
            End With

            If regexp.Test(strInput) Then
                MsgBox rcell & " Matched in Cell" & rcell.Address
                Set RegExSearch = Range(rcell.Address)
                MsgBox RegExSearch
            End If
            End If
        End If
    Next
End Function

Answer 2

A cell address is just a string; you don't Set it, simply assign it with =.

RegExSearch = rcell.Address

... will return the absolute cell address.

You might want to consider exiting the For Each rcell In rng.Cells loop if the pattern is found. There doesn't seem to be any point in continuing unless you want the cell addresses of the union of all matching cells.

        If regexp.Test(strInput) Then
            MsgBox rcell & " Matched in Cell" & rcell.Address
            RegExSearch = rcell.Address
            MsgBox RegExSearch
            Exit For
        End If

You're setting identical RegEx arguments inside the loop. Move the argument assignment above the For Each rcell In rng.Cells loop.

    With regexp
        .Global = False
        .MultiLine = False
        .ignoreCase = True
        .Pattern = strPattern
    End With

    For Each rcell In rng.Cells