Regex Match & Replace on Range in Excel using Visual Basic Function

Question

I'm looking to create a user-defined function in Excel (2010, Visual Basic 7) called CODEMATCH that will

1. Take a range as an input
2. Preserve the structure of the range when outputting
3. On each value in the range:
   a. If the value matches [matchPattern]:
      i. regex match the value against [matchPattern] and store it as [var1]
      ii. regex replace [var1] against [stripPattern] and store it as [var2]
      iii. return [var2]
  b. If the value does not match [matchPattern]:
      i. return an empty value

 Where
   matchPattern = "^[^A-Z0-9:]*[A-Z0-9][^A-Z0-9:]*[A-Z0-9]?"
   stripPattern = "[^A-Z0-9]*"

 AndWhere
   RegEx match is not global and respects case
   RexEx replace is global and respects case

 Such that
   "Nobody Cares about Bob" returns "NC"
   "1 Duck for Jody" returns "1D"
   "Apples: I Don't Like Them" returns "A"
   "foobar" returns ""

---

Part of my agony is that I'm new to Visual Basic. Part of my agony, I think, comes from multiple versions of RegEx existing in Visual Basic and not knowing which version takes what properties.

I tried building the function up in level of complexity, and this is as far as I could get before I hit an impenetrable brick wall:

Function CODEMATCH(ByVal valueIN As String) As String

Set matchRegEx = New RegExp
    matchRegEx.Pattern = "(sdi \d+)"  '<--what's giving me difficulty
    matchRegEx.Global = False
    matchRegEx.IgnoreCase = False

Set matches = matchRegEx.Execute(valueIN)

If matches.Count <> 0 Then
    CODEMATCH = matches.Item(0).SubMatches.Item(0)
Else
    CODEMATCH = ""
End If

End Function

The code as it is works, but it won't let me use the matchPattern that I defined earlier. Beyond that, I still need to adopt it to perform a regex replace and adopt it to handle ranges rather than single cells.

Answer 1

How this? :)

Function CODEMATCH(ByVal valueIN As String) As String

Dim strTemp As String
Set matchRegEx = New RegExp
With matchRegEx
    .Pattern = "[^A-Z0-9:]*[A-Z0-9][^A-Z0-9:]*[A-Z0-9]?"
    .Global = False
    .IgnoreCase = False
If .Test(valueIN) Then
    Set matches = .Execute(valueIN)
    .Pattern = "[^A-Z0-9]*"
    .Global = True
    strTemp = matches(0)
    CODEMATCH = .Replace(strTemp, vbNullString)
Else
 CODEMATCH = vbNullString
End If
End With

End Function

test

Sub Test()
Debug.Print CODEMATCH("Nobody Cares about Bob")
Debug.Print CODEMATCH("1 Duck for Jody")
Debug.Print CODEMATCH("Apples: I Don't Like Them")
Debug.Print CODEMATCH("foobar")
End Sub