I remember the macro environment has several advanced scripting aid tools that I used in the past but these days I often happen to write macros on the fly with no proper development environment set up (which thing I guess, may be common to many general-purpose programmers like me).
This to introduce the question about “FunctionAccess callFunction” which sounds quite obscure to me: what are the built-in functions it can handle? Which is the correct order spreadsheet function arguments must be passed to it?
An example: a routine written through Basic if-then-else statement seems very slow and it is possible that callFunction can be of some help to speed it up. Following some trials, it also appears that callFunction accepts IF as a parameter. However, I have no idea about how to express a condition such as: IF ARG1 < ARG2 THEN ARG3 ELSE ARG4. I even tried out several combinations based on the assumption that comparison operators might be arguments coded like < : -1, = : 0, > : 1, etc…The only meaningful resource I found online is Dr. Andrew Davison’s Java LibreOffice Programming - Chapter 27. Funcs & Analysis Draft #2 (20th March 2017). There I found a Functions Help described and particularly Java methods Calc.getFunctionNames() and Calc.printFunctionInfo(). Yet, I don’t see how to obtain the same help output in Basic and JavaScript for example, inside the LibreOffice scripting environment (JavaScript Console/Evaluate panel?). By the way, can anyone shed light on how to employ FunctionAccess callFunction with IF?
Wiki posts, though offered as an option, are not recommendable.
Most experienced contributors to the forum seem to be unanimous about this.
Specifically concerning questions: Who should know better what you wanted to ask?
Before publishing it, I couldn’t actually get the full picture from the description next to the wiki option checkbox and I was lazy enough not to investigate it. Once published, the caption of the wiki frame wrapped by text in the post is much more clear and I tried to roll back to a non-wiki post but I couldn’t find how.
(Nothing cryptic.)
Try:
Sub test
faService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
a1 = 7 : a2 = 9 : a3 = 11 : a4 = 12
u = faService.CallFunction("IF", Array(CDbl(a1 < a2), a3, a4))
v = faService.CallFunction("IF", Array(CDbl(a2 < a1), a3, a4))
End Sub
and ask for u and v.
The tiny problem is that logical results are ordinary numbers (Double) in Calc while they are Boolean in BASIC.
The terrific concept of automatic conversion obviously doesn’t reach that far to provide the IF() function which is Calc the parameter type it expects in this case. You have to do the conversion explicitly.
Thanks. I’ll give it a try soon with the help of the integrated debugger/watcher. Meanwhile, can you explain what double u and v contain? True/False or a3/a4?
Double is the well known IEEE 754 floating point type.
u and v are unDim’d (variant) variables just taking the results in the demo. Like any numeric Calc function IF() returns type Double. If you declare a different type for u or v Basic will try a conversion. CInt() is the Basic function for an erxplicit conversion to ‘Integer’. There is also the (truncating but NOT type changing) function Int() in Basic and in Calc as well…
u and v contain a3 or a4, the results of the IF function. In this example, u = 11 and v = 12.
u and v get their values by assignments using the results from the CallFunction method provided by the FunctionAccess service… The values 11 (a3) an 12 (a4) respectively demonstrate that “IF” passed as the first parameter to the method worked as expected.