User Defined Function Options

English
#1

As a newcomer to this forum and comparatively new to LOCalc I am posting a question about its ability to utilize the built-in functions and to extend their use with custom or “user defined” functions. I am attaching a file that may illustrate some of the topics I reference.

I began by adding a date value, “6/21” and observed calc convert this to “06/21/2024”. I assumed this was a date serial number calculated by the system as “time since” some reference point. A few cells over I added an integer value to a reference to the aforementioned date and calculated the result as “08/05/2024”. I was calculating mid-summer; the date also happened to be a family-member’s birthday. I wondered what day of the week that might be.

Calc has functions for converting date-serial numbers into month-ordinal and week-ordinal numbers but not (so far as I could find) one for naming the day of the week. I created a simple formula using the SWITCH function to find the name of the weekday, referencing my previous calculation. It worked, once I used the proper syntax (double quotes, not single quotes around strings), so I tried to modify this formula using a “%1” parameter instead of the cell reference. This is a technique used in other SS but was not successful here. After more research I found I would have to write a function to use this approach anywhere in the sheet.

That function should be visible in the BASIC editor of the attached sheet: accepts an integer, processes it in a case statement, converting the number to a string and returning the string as the result. Before using the case select structure I tried my former SWITCH statement structure in my BASIC function. This approach was both successful, in that it produced a result when invoked from a sheet cell, and unsuccessful, as calc immediately thereafter issued an error message and erased the result.

This experience leaves me with some questions, which are the point of my posting here:
What is the relation (overlap?) between spreadsheet functions and BASIC functions? Are they identical? Can sheet functions be referenced in BASIC code at all?
Is the “undefined parameter” ("%1, %2 etc.) or “placeholder” ever used in LOcalc?
If not, is there a design or operational philosophy that restricts it use?
Could this be suggested as a feature for future release? What say you?

Thank you for your replies.
-CH-
LO_FunctionTest.ods (23.9 KB)

#2

That’s a bit strange. Basically there is no relation, but …
There is a Now() function of Basic and the NOW() function of Calc which work slightly different.

I will avoid “reference” because it’s unclear to me in the context. You can call a Calc function relying on a slightly complicated construct:
You need to create a service instance, and then its method .callFunction() with the appropriate parameters .
A parameter designation using the %-sign or generally the index of position is not known.

But You may not get a personalized tutorial for every topic here. If interested in the use of OpenOffice Basic (older common name) either for the creation of any UserDefinedFunction for calls from Calc formulas or in any programming for document automation in LibreOffice you best start with the famous texts by Andrew Pitonyak from his site. Look at te table “My Main Downloads”.

#3

Yes.

#6

I will need to delve farther into the API in order to use it more effectively. Thanks.

#7

So be it. I will examine the suggested reference.

#8

A few additional comments.

  1. The included Basic IDE with debugger knows
    Option VBAsupport 1
    allowing to work with a ParamArray as known from VBA. I would dissuade from using it without urgent need because some native constructs of LibO Basic may change their behaviour under this setting.

  2. LibO allows to use custom routines for which the source can be written in many different languages if the used language/DevEnv provides the needed bridge to the LibO API. Some experienced users prefer to generally work with Python scripts instead of Basic. This is well supported. Of course there may again be tools for a task which work in a similar but not identical way as compared to those provided by the LibO API directly. Confusion must be thoroughly avoided then.

  3. Concerning the question how to call Calc functions from a routine in native Basic, you may be interested in this basic-code snippet.

  4. Unfortunately there are a few Calc functions supporting a variant of usage which requires the omission of an optional parameter as a part of the information to be passed to them. Namely: INDEX() , REGEX() . These variants are not supported by the com.sun.star.sheet.FunctionAccess service. (Tell me if you found a way.)

#9

There is no way. ScFunctionAccess::callFunction follows the documentation to the letter; and the spec requires that each argument in the passed array are one of a number, a string, an array, or a cell range.

It is trivial to extend that (and that would be a permissible change in the API: it extends the accepted set of types, doesn’t break existing code). Just adding code like

        else if (!rArg.hasValue())
        {
            aTokenArr.AddToken(formula::FormulaMissingToken());
        }

to the implementation would allow Basic code like

  service = CreateUnoService("com.sun.star.sheet.FunctionAccess")
  ret = service.callFunction("REGEX", Array("abc12", "\d", Empty, 2)) ' would return "2"

but I don’t see a request in our bug tracker…

#11

I am, as this essentially answers one of my questions.

#12

Thank you, Mike, for your elucidation.

#4

Looking for the TEXT() function?

image
image1293×532 51.1 KB

https://help.libreoffice.org/latest/en-US/text/scalc/01/04060110.html?DbPAR=CALC#bm_id3147132

3 Likes
#5

Apparently so. Thanks.

#13

=TEXT(A3,“NNNN”) & TEXT(A3,“MMMM”) & " " & TEXT(A3,“D”) & " " & TEXT(A3,“YYYY”)
displays “Friday, June 21 2024”

QED, thanks.

#14

No need, just try =TEXT(A3;"DDDD, MMMM D YYYY")

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.