User-defined function that uses an in-built Calc function

Question

Consider the following user-defined function:

Function LIMPA(x as String) as String
    Dim oFunctionAccess As Object
    oFunctionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
    LIMPA = oFunctionAccess.callFunction("REGEX", Array(x, "^\d+\:\d+\sPM", "", 0))
End Function

Unfortunately, this function does not return the correct result for the following string:

5:32 PMMuitas letras

The result should be

Muitas letras

but I am getting exactly the same string, that is:

5:32 PMMuitas letras

Moreover, I get an error if I try to run the function as an array formula.

Any ideas? Thanks!

Answer 1

The last argument of 0 indicates not to match any occurrences. This works:

MsgBox oFunctionAccess.callFunction(_
    "REGEX", Array("5:32 PMMuitas letras", "^\d+\:\d+\sPM", "", ""))

To match exactly one occurence, specify 1 as mentioned by @JohnSUN.

If you enter =REGEX and look at the tooltip text, it says:

Occurrence: Text specifying option flags, "g" for global replacement. Or number of occurrence [sic] to match or replace.

You also mentioned trying an array formula. In that case, x is a two-dimensional array. Here is a version that handles both normal calls and array formula calls.

Function DoReplace(x as String) as String
    Dim oFunctionAccess As Object
    oFunctionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
    DoReplace = oFunctionAccess.callFunction(_
        "REGEX", Array(x, "^\d+\:\d+\sPM", "", ""))
End Function

Function LIMPA(x)
    If IsArray(x) Then
        Dim results(1 to UBound(x), 1 to 1) As String
        For y = 1 to UBound(x)
            results(y, 1) = DoReplace(x(y, 1))
        Next
        LIMPA = results()
    Else
        LIMPA = DoReplace(x)
    End If
End Function

I suppose I ought to have handled the case where the array formula spans multiple columns, but you could add that if needed, for example Dim results(1 to UBound(x, 1), 1 to UBound(x, 2)) As String.