I want to create a User Defined Function (https://help.libreoffice.org/Calc/User-Defined_Functions) 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:
Func = x^2
and use it in a cell (say, C1) like "=Func($A$1:$A$3)", it will bring error, because x is array, not single value. 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 discussion at http://forum.openoffice.org/en/forum/viewtopic.php?f=44&t=27860 and similar, I already know that it's impossible to get the calling cell address from within the code.
So, the request is to provide a means to get only one value from a relative cell in the range passed to a Basic function.
As an option, I suggest to distinguish when the function argument is declared explicitly as a scalar.
Other option could be to allow to get the information about the cell in which the formula resides.
Have you taken a look to macros documentation?
(In reply to comment #1)
> Have you taken a look to macros documentation?
Yes, of course. But haven't found any info on this matter. Do you know if it is possible, and where can I find this information?
In Andrew Pitonyak documentation (virtually everything is there), in section 3.5 and 5 there is information about Arrays in basic or 15.4.7. Computing functions on a range.
(In reply to comment #3)
You just haven't tried to understand what had been asked. I tried to express the question as detailed as possible, and included an example of desired behaviour in form of use case of a built-in function. Have you tried it to see what is it about?
There's no problem if you need all the range. But the problem arises if you need just a single cell in the range, when the only thing you know is that the cell is in the same row (or in the same column) with the cell in which the formula resides.
Suppose that there are ranges (columns) in the worksheet named "Length", "Diameter" and "Thickness" describing properties of some pipes. You need to create a column with the weights of the pipes. You write formula like "=8,7*Length*PI()*Thickness*(Diameter-Thickness)" and put it to all the cells in that column. Note that the arguments are _ranges_, not individual cells, but each resulting cell only uses _one_cell_ from each range, that is in the same row with that resulting cell.
That is what I want to be able to do in User-defined functions, and something that seems to be impossible there now.
Moggi - thoughts on this one? Valid request?
Created attachment 115862 [details]
(In reply to Mike Kaganski from comment #4 - mainly)
As far as I can understand the intention is to get a user function evaluated "by intersection" as described in the section
3.3 Non-Scalar Evaluation (aka 'Array expressions') found in
of the ODF specifications, so far only applicable to expressions with arrays placed in positions which are specifid to accept only scalars.
This cannot be implemented by an ordinary custom function because it doesn't know the cell position from where it wa called nor the position of the raneg from where the parameters were calculated. However, the specification is binding not the function but the evaluator. The evaluator, however, cannot do it without knowing that a specific position should only accept scalars. If we know the type (and for the parameters of functions implemented by ourselves we should know) we can help the evaluator applying a neutral scalar operand and operator (""& or *1 e.g.) to our arrays. In fact the evaluator does apply intersection under this condition. The intersection applies to every single parameter position then.
The attached Calc document is demonstrating this for the example described by @Mike_Kaganski in the above mentioned comment.
(In reply to Wolfgang Jäger from comment #6)
Thank you VERY MUCH! That's highly informative, you got the problem exactly correct, and provided a brilliant solution!
The information that you've provided should be included into the wiki.
However, there are something that is very cumbersome doing this way. For instance, if I want to make a function that could operate both on scalar texts and numbers. I would need to provide an "IF" for that argument in formula, to handle that, or something. And anyway, it's something not very intuitive.
So then the proposal is to provide a special syntax for a function argument (a "decoration"), like, say, "#$E$11:$E$20" or "#Length" or something, that explicitly says thet "intersection" mode is expected for that argument.
Thank you again!
Ok, so valid enhancement.
Best regards. JBF
Migrating Whiteboard tags to Keywords: (needAdvise -> needAdvice)
'needsConfirmationAdvise' is only used for unconfirmed bugs. Removing it from this bug.
A workaround is using CELL("CONTENTS",<range>).
An alternative solution to new syntax would be a one-argument CELL(address) that would be equivalent to CELL("CONTENTS",address). It is short enough and it tells.
The best workaround is to use FUNC(0+range) or FUNC(1*range) as it is already done in the sample document.
The problem is that Basic afaics does not expose expected parameter types to the outer world, so there's nothing Calc could do.
Note that Add-In functions using the UNO interface handle this correctly.
(In reply to Eike Rathke from comment #12)
> The best workaround is to use FUNC(0+range) or FUNC(1*range) as it is
> already done in the sample document.
As I mentioned above, using 0+range doesn't work for mixed number/string case.
Created attachment 144449 [details]
Demonstration of elaborate workarounds covering the mixed case
(In reply to Mike Kaganski from comment #13)
> As I mentioned above, using 0+range doesn't work for mixed number/string
I doubt if designing sheets for "mixed number/string cases" is good practice at all.
Anyway you can enhance the mentioned workaround by
=FUNC(IFERROR(0+range;"" & range))
which should work in most cases though sometimes automatic conversions may interfere. A more elaborate, but overtorqued(?) example you find in the new attachment. It also demonstrates the workaround
which also comes with some disadvantages, of course.
I would expect this old enhancement-bug to be classified "wontfix" some day.
As I see it the intersection-mode is of use only in connection with named ranges. To omit one or a few ranges -or to only reference them in specific formulae- for getting rid of the problem should be an acceptable workaround not relying on long formulae like in the new example.
Anyway named ranges aggravate scaling, and enhancement of sheets sometimes.
(Excel enthusiasts on YouTube may bias users to apply named ranges,.. Are there interoperability issues?)