Ask Your Question
0

Cryptic FunctionAccess callFunction

asked 2018-05-18 11:55:23 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag flag offensive close merge delete

Comments

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?

Lupp gravatar imageLupp ( 2018-05-18 12:53:20 +0200 )edit

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.

liberli gravatar imageliberli ( 2018-05-18 13:44:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-05-18 12:31:36 +0200

Lupp gravatar image

updated 2018-05-18 13:08:57 +0200

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

edit flag offensive delete link more

Comments

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?

liberli gravatar imageliberli ( 2018-05-18 12:41:06 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-05-18 13:02:46 +0200 )edit

u and v contain a3 or a4, the results of the IF function. In this example, u = 11 and v = 12.

Jim K gravatar imageJim K ( 2018-05-18 14:29:39 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-05-18 15:38:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-18 11:55:23 +0200

Seen: 39 times

Last updated: May 18