return rows in Query from variant array return from VBA UDF

[hswerdfe]

I have a simple UDF that takes a string and returns a variant, which is an array of strings

Example Input "Brick Wall"
Return value would be a variant array with first element "Brick" and and second element "Wall"

Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this
[strField]
"kick the ball"
"return the pass"

my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

Thanks for any help

 

[jdraw]

Please show all code for your UDF.

 

[hswerdfe]

current function, is very basic.

Public Function Words(ByVal orig As String) As Variant
    Dim regEx As New VBScript_RegExp_55.RegExp
    regEx.Pattern = "\b\w+\b"
    regEx.Global = True
    If regEx.test(orig) Then
        Set Words = regEx.Execute(orig)
    Else
        Set Words = Nothing
    End If
End Function

 

[jdraw]

I ran your function and received
450 Wrong number of arguments or invalid property assignment

Public Function Words(ByVal orig As String) As Variant
    
    Dim regEx As New VBScript_RegExp_55.RegExp
    regEx.Pattern = "\b\w+\b"
    regEx.Global = True
    If regEx.test(orig) Then
        Set Words = regEx.Execute(orig)
    Else
        Set Words = Nothing
    End If
End Function

My Test Routine (which could be set up incorrectly)

 Sub testit()
Dim x As Variant
Dim myinput As String
   On Error GoTo testit_Error

myinput = "Last time I had to do something like this I used VBA exclusively" _
           & " with ADO objects  but I thought a query based solution would be easier"
           
 x = Words(myinput)

'ParseIt (myinput)
Debug.Print "x " & x
   On Error GoTo 0
   Exit Sub

testit_Error:
Debug.Print Err.Number & " " & Err.Description
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testit of Module parseStringStuff"
End Sub

And a small routine I create to parse a string

Function ParseIt(sInput As String)
    sInput = Replace(sInput, "  ", " ") 'remove any double spaces
    Dim sArray() As String
    Dim i As Integer
    sArray = Split(sInput, " ")
    For i = LBound(sArray) To UBound(sArray)
        Debug.Print "sarray(" & i & ") " & sArray(i)
    Next i
End Function

I'm not sure where the output is suppose to go.