Ask Your Question

Determine matrix context in calc basic UDF

asked 2018-04-09 19:41:52 +0200

Markdv77 gravatar image


I can write user defined functions/macro's in basic for use in calc spreadsheets. Most thanks to this contribution

What I would like to be able to do is determine inside my function if it was called as a matrix function or not, and should return a single value or an array.

And, also, if it is possible to know the dimension(s) of the matrix to be filled with the return value.

Ragards, Mark.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-04-09 20:54:26 +0200

Lupp gravatar image

updated 2018-04-09 21:02:52 +0200

I cannot tell if the function was entered for array evaluation. But depending on the specific case you will find a way to decide that depending on what was passed for the parameters. In most cases it may suffice to decide if (at least one of ...) an array was passed for a parameter. Till now I never had a case where I needed that, but you can, of course use a parameter the user can explicitly tell with if array evaluation is needed. User shouldn't lie about such things anyway. ...

Let me demonstrate the way I do things of the kind by a simple example:

Function firstCharacters(pIn)
If Not IsArray(pIn) Then 
    Dim h(1 To 1, 1 To 1)
    h(1, 1) = pIn
    pIn = h
End If 
l1 = Lbound(pIn(), 1) : u1 = Ubound(pIn(), 1)
l2 = Lbound(pIn(), 2) : u2 = Ubound(pIn(), 2)
Dim r(l1 To u1, l2 To u2) As String
For y = l1 To u1
    For x = l2 To u2
        r(y, x) = Left(pIn(y, x), 1)
    Next x
Next y
firstCharacters = r
End Function

This will also work in the exceptional case that pIn is a single value/variable. You need to use an untyped (Variant) parameter. (You may test, of course each element for its 'Typename'.)

edit flag offensive delete link more


I have to admit, for most use cases getting a scalar or array as parameter will indicate the required return type. Thanks for your example.

Markdv77 gravatar imageMarkdv77 ( 2018-04-11 10:34:07 +0200 )edit

answered 2018-04-09 21:57:15 +0200

erAck gravatar image

Basic macros don't get array/matrix context passed and also don't declare strict parameter type specification, but allow arguments of different types for the same parameter, as Lupp lined out.

UNO Add-In functions also don't get an array/matrix context passed and thus there's no difference in the return type. But as they do declare parameter type specifications, if in array context for parameters that expect a scalar value and are passed a range or array instead, the function is called multiple times iterating over the positional scalars, e.g. for MYFUNC(A1:A2) in array context MYFUNC() is called twice with values for A1 and A2, similar to built-in spreadsheet functions.

edit flag offensive delete link more


Quoting @erAck: "...MYFUNC(A1:A2) in array context MYFUNC() is called twice with values for A1 and A2, similar to built-in spreadsheet functions."
Would you mind to demonstrate that with a very simple MYFUNCF() in a very simple .ods? I never succeded when trying to call a user function defined for a scalar parameter in array context this way.
(Intersection mode doesn't work either with user functions, imo.)

Lupp gravatar imageLupp ( 2018-04-09 22:13:43 +0200 )edit

Try the simple ROT13 sample:

A1: foo

A2: bar

B3: =ROT13(A1:A2) as array formula


B3: sbb

B4: one


UNO declaration for getRot13() is in scaddins/idl/com/sun/star/sheet/addin/XMiscFunctions.idl

Implementation in scaddins/source/datefunc/datefunc.?xx

That datefunc is meant as sample code, hence it's in there; ignore the internal translation stuff.

erAck gravatar imageerAck ( 2018-04-09 22:42:51 +0200 )edit

And yes, implicit intersection works as well. In the previous example just try to enter =ROT13(A1:A2) in each cell of C1, C2 and C3 => sbb, one, #VALUE!

erAck gravatar imageerAck ( 2018-04-09 22:53:33 +0200 )edit

I explicitly and exclusively talked about user functions. You may reduce that to user fuctions coded in LibreOffic BASIC due to the context.
There are informations that are made available to predefined functions but not to user functions. And:there are also informations concering the function's interface the evaluator knows about a standard function but not about a custom function. In this context the most relevant information of this kind is whether a parameter expects a scalar ...

Lupp gravatar imageLupp ( 2018-04-09 23:53:05 +0200 )edit

...or an array. In the first case array-evaluation or range intersection should take place if an array is placed in the respective position. However, the evaluator simply passes the array and leaves everything else to the function's body.

Lupp gravatar imageLupp ( 2018-04-09 23:56:08 +0200 )edit

As said, BASIC does not have all this. In your comment you asked about what I wrote about UNO Add-In functions. Maybe I just shouldn't had mentioned them.

erAck gravatar imageerAck ( 2018-04-10 00:39:15 +0200 )edit

OK. This was a misunderstanding.
But also it isn't just a question what information is passed to the function. I don't know how that is implemented, but the specification is talking of "the evaluator" in this context, being charged with resolving any kind of array-context. Delegating this duty to each function would not only be inefficient due to the need of repeated implementation, but would also require to pass the needed information - about the calling cell e.g.

Lupp gravatar imageLupp ( 2018-04-10 00:43:56 +0200 )edit

Thanks guys, interesting points, and things to try out. Useful! :)

Markdv77 gravatar imageMarkdv77 ( 2018-04-11 11:28:21 +0200 )edit

Rethinking this I came to the (obvious?) conclusion that a treatment of iterative evaluation outside of the bodies of concerned functions cannot be efficient. A predefined variable providing accessible context (IsArrayMode, LockedOutputRange, CalledFrom?, ReturnsTo? ...) should be useful. Intertsection mode should be handled outside the function. Already the parser would, however, need interface information concerning the function. Not simple.

Lupp gravatar imageLupp ( 2018-04-11 11:48:54 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-09 19:41:52 +0200

Seen: 28 times

Last updated: Apr 09 '18