LibreOfiice Basic function to perform regular expression replacement

Requirement

I wish to manipulate string values in a LibreOffice Basic macro using regular expression replacement? I should like to use something like y = regex (x, "^¿(.*)\?$", "$1") to operate on string variables (rather than cells of a document). The macro is to be used from LibreOffice Calc.

This is not for the above (trivial) problem, but a possibility that I frequently miss.

Apparent status

Only search exposed to scripts?

Of course the LibreOffice engine performs regular expression replacement (both in interactive Find/Replace and in the REGEX function). However, it seems that only regular expression search is exposed to Basic – and that is not what I am looking for. (It is provided by the somewhat cumbersome TextSearch service, which (if I recall correctly) can also search in string values in Basic variables.)

Spreadsheet functions: do not work

I thought one was meant to be able to call Calc functions in LO Basic, but calls like

z   = regex (x, "^¿(.*)\?$", "$1")
z   = regex (x, "^¿(.*)\?$", "$1", "g")

just said “Basic runtime error. Sub-procedure or function procedure not defined”.

Hack

As a horrible hack, one could manipulate some cells containing appropriate formulae in a scratch part of my sheet, but that has so many evident disadvantages that I would only use it in extremis!

Various sources

The question Using regex in a libreoffice calc macro to extract text from parentheses in a cell is about using REs to find text in cells.

Searching the API documentation for “regular” only threw up the TextSearch Service mentioned above.

Several forums have posts like this that only address search; I have found nothing about replacement.

Thanks to JohnSUN for his comment giving the solution using Calc.REGEX!

I know of 2 approaches to RE substitutions in LibreOffice Basic:

• Call the Calc function REGEX. (Uses ICU REs.)
• Use the function ReplaceRegex in the ScriptForge.String service. (Seems to use Python REs.)

Using the Calc function REGEX

One can call spreadsheet functions from macros via the callFunction method of the service com.sun.star.sheet.FunctionAccess. The following function RE_replace uses this to perform an arbitrary RE replacement (where report is some error-handling):

Function RE_replace (y_Within as String, y_Regex as String, y_By as String, Optional y_Flags as String) as String

    Dim z_A as  Variant
    If IsMissing (y_Flags) then
        z_A = Array (y_Within, y_Regex, y_By)
    Else
        z_A = Array (y_Within, y_Regex, y_By, y_Flags)
    EndIf

    Dim z_Function_Access   as  Object  :   z_Function_Access   = createUnoService ("com.sun.star.sheet.FunctionAccess")

    Dim z_Error             as  Boolean :   z_Error             = True
    On Local Error Goto ErrHandler
        Dim z   as  String  :   z   = z_Function_Access.callFunction("REGEX", z_A)
        z_Error = False
    ErrHandler:
    On Local Error Goto 0

    If z_Error Then
        Dim z_Flags_S   as  String  :   z_Flags_S   = ""
        If not IsMissing (y_Flags) Then
            z_Flags_S   = ", '" + y_Flags + "'"
        EndIf
        report ("Error from Calc.REGEX ('" + y_Within + "', '" + y_Regex + "', '" + y_By + "'" + z_Flags_S + ")")
    EndIf
    RE_replace  = z
End Function

Notes

• I assign the result of functions to a local variable z to make debugging easier.
• My original version can optionally repeatedly substitute up to a given limit.
• One can cache the FunctionAccess object, but I do not suppose that saves much (I have not measured it).
• One could simplify this by stripping out the error handling — but that may make debugging mistakes in REs harder.
• My coding conventions work well for me! z_ indicates a local variable and y_ an argument; where possible I initialise a variable in the line where it is declared.
• The RE syntax supported is that of ICU regular expressions.
  • (For a brief summary see the List of Regular Expressions in the LibreOffice (7.2) documentation)

(I have slightly simplified my code, but I have briefly tested this version too)

Using ReplaceRegex in ScriptForge.String

This is documented (for LO 7.2) at https://help.libreoffice.org/7.2/en-US/text/sbasic/shared/03/sf_string.html?&DbPAR=WRITER&System=UNIX , but I have not tried it. It appears to use Python RE syntax, as it refers us to the documentation of the Python module re.