How can I write a scalar User Defined Function for Calc that takes range? [closed]

asked 2013-07-08 06:00:10 +0200

I want to create a User Defined Function for use in Calc. It is intended to take a numeric value as argument. But I need it to work like built-in functions like "SQRT" do: if you pass a range to it, it selects one value from that range that corresponds to either same column or same row as the calling cell (the cell in which the function is calculated). You may, for example, set A1 = 1, A2 = 4, A3 = 9, and if you put "=SQRT($A$1:$A$3)" into B1, B2 and B3, you will see B1 = 1, B2 = 2, and B3 = 3. But if I declare a function in LibreOffice Basic like this:

function Func(x) Func = x^2 end function

and use it in a cell (say, C1) like "=Func($A$1:$A$3)", it will bring error, because x has type variant (array of arrays of doubles). And there seems to be no way to tell Calc to pass the arguments like it does to built-in functions: declaration like

function Func(x as double) ..

doesn't help, either.

Of course, if I wanted to reference cells by their addresses, I better simply use it like "=Func(A1)", and dragged the formula so that it will point to relative cell in each place. But the real purpose for this is to be able to use named ranges in the function. In complex formulas, it is very inconvenient to reference cells by addresses, but rather by names set in Insert->Names.

From this discussion and similar, I already know that it's impossible to get the calling cell address from within the code. But isn't it possible to do this another way?

If there is no way at the moment, I will post enhancement request to Bugzilla to implement the desired treatment of functions that are declared explicitly to take scalar arguments (as in my second syntax). Just want to know for sure that it isn't already possible.

Thanks in advance!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-04 19:45:37.296296


As Wolfgang Jäger mentioned in tdf#66717, it is possible to do that using "applying a neutral scalar operand and operator (""& or *1 e.g.) to our arrays". That's not as universal, because you cannot always decide in advance what is the result of the function: numeric or textual

Mike Kaganski gravatar imageMike Kaganski ( 2016-12-11 00:30:01 +0200 )edit