How can I write a scalar User Defined Function for Calc that takes range? [closed]
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!