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

[mikekaganski]

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!

[mikekaganski]

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

[Lupp]

A short while later:
For cases where a “more universal” solution is needed, I also gave a workaround in tdf#66717, but it requires to accept rather complicated formulas in the spreadsheet. To avoid this, the Basic function would need to handle the cases of having got passed a single value or an array. Since Basic has an IsArray() function this can be done like in:

Function evalArrayExample(p)
REM The function is for calls from Calc exclusively!
If NOT isArray(p) Then
  Dim h(1 To 1, 1 To 1)
  h(1, 1) = p
Else
  h = p
End If
doWhateverNeeded(h, res)
REM This can handle numbers and strings as needed.
evalArrayExample = res
End Function