Lightweight Global Module Simplifying FunctionAccess

[Lupp]

More often than you might think, when writing Basic code for LibreOffice (or AOO), I want to have access to the standard Calc functions. There are also cases where Basic knows a function with the same name or working similarly, but which does not do exactly the same - or indeed less.
To get by with a single line in such cases, I set up the very simple module below (Standard.aaaGlobal for me). Of course, this module may get enriched with some additional functionality.
The names for global use are rather long. I deliberately chose them this way to avoid accidental conflicts.

Code: Select all

REM  *****  BASIC  *****

Global globalCalcFunctionAccess As Object
REM This object should not be dirtectly accessed by user code.
REM It must be expected to not be initiaized.

Function gFunctionAccess()
REM This function MAY be directly used AS IF it is a FunctionAccess service.
If IsNull(globalCalcFunctionAccess) Then globalCalcFunctionAccess = _
                       CreateUNOService("com.sun.star.sheet.FunctionAccess")
gFunctionAccess = globalCalcFunctionAccess
End Function

Function gCallCalcFunction(pName As String, pParams)
REM This function provides support for Calc functions under Basic.
gCallCalcFunction = gFunctionAccess.callFunction(pName, pParams)
End Function

Just one example of how to use it with two nested calls:

Code: Select all

useAsDate = gCallCalcFunction("TEXT", Array( _
              gCallCalcFunction("DATE", Array(2000, 0, 1111)), _
                                              "YYYY-MM-DD"))

[JeJe]

I considered writing a wrapper to make using these functions easier but haven't used them often enough, as well as the lack of autocomplete in the OO IDE limiting the benefit.

Using your code an example of the Replace function would be:

Code: Select all

sub testReplaceFA
msgbox REPLACEFA("mouse", 2, 3, "ic")
End Sub

function ReplaceFA(originaltext as string, startposition as long, length as long, newtext as string)
 ReplaceFA=gCallCalcFunction("REPLACE",array(originaltext, startposition, length,newtext))
end function

Edit: added missing closing )