regexreplacelibreoffice-basic

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.


Solution

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

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

    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 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.