# Determine matrix context in calc basic UDF

Hi,

I can write user defined functions/macro's in basic for use in calc spreadsheets. Most thanks to this contribution https://ask.libreoffice.org/en/questi...

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 close merge delete

Sort by » oldest newest most voted

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'.)

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.

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

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.

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.)

( 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

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

( 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!

( 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 ...

( 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.

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

( 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.

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

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

( 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.

( 2018-04-11 11:48:54 +0200 )edit