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.
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.)
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”.
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!
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:
REGEX
. (Uses ICU REs.)ReplaceRegex
in the ScriptForge.String
service. (Seems to use Python REs.)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
z
to make debugging easier.FunctionAccess
object, but I do not suppose that saves much (I have not measured it).z_
indicates a local variable and y_
an argument; where possible I initialise a variable in the line where it is declared.(I have slightly simplified my code, but I have briefly tested this version too)
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.