Excel regex query returning empty data

Question

I'm using the following VBA code from a related question in my Excel spreadsheet, and when I use it in a cell, it always fails (returns nothing). Even if I call it on a string literal in the function call (i.e. =RegexExtract("ABC1_DEF","ABC[0-9]")), it still fails. I've enabled the "Microsoft Visual Basic Regular Expressions 5.0" feature in the MSVBA application, so I'm not sure why these results are always empty. How can I resolve this?

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional separator As String = ", ") As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String

RE.pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & (separator & allMatches.Item(i).submatches.Item(j))
    Next
Next

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(separator))
End If

RegexExtract = result

End Function

Edit

I tried yet another function from a separate question, and it just returns #VALUE!:

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String) As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)
RegexExtract = allMatches.Item(0).submatches.Item(0)

End Function

Answer 1

Note you are trying to access .Submatches that stores capturing group values, but you have not defined any capturing groups in the pattern.

If you use (ABC[0-9]) you will get your match with the current function. Else, access the allMatches.Item(i) for full match values and discard the code to get the captured groups.