Ask Your Question

How to write my own function to work in matrix formulas? [closed]

asked 2014-04-15 20:37:14 +0200

Mich gravatar image

updated 2015-09-08 20:47:53 +0200

Alex Kemp gravatar image

I work in LibreOffice I have three columns of numeric data (double) as my input. I want to get one numeric column (double) as my function output. I wanted to write my own public function accessible from worksheet which can be used in matrix formulas. Currently I wrote already my 1st version of this function which returns a scalar double and takes 3 scalars as its input, but it needs in each line another different relative addressing references. It works well of course, but I hoped that I would use matrix calculation. Such construction works for standard math operator. I mean for example such syntax {=A1:A3/C1:C3} or even this better looks when you define named range: {=my_a_range/my_c_range}. I hoped that I would use {=my_fun(col_a, col_b, col_c)} in my whole output column where col_a, col_b, col_c are named input column of data. The main reason of this operation is virtually increasing formulas readability in my worksheet. I can check that everywhere is the same formula instead of having the same function with incrementing address referencing. I have read LO documentation, additional documentation about macro. I did not read SDK documentation. Where should I go to achieve this goals in terms of additional reading, spotting some examples etc.?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 21:12:12.922233


Do you want to write a macro? In BASIC? Or do you want to make your own build with a function in core?

Regina gravatar imageRegina ( 2014-04-16 00:34:51 +0200 )edit

As a user I want to write a macro in BASIC. I do not care about performance and calculations are simple. If it is not feasible I would like to know which technology should be used. I know C++ and perhaps writting add-on based on a good example would do. As you pointed adding function in core should work, but I think it is too heavy for me and not easily portable.

Mich gravatar imageMich ( 2014-04-16 08:31:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-04-16 16:51:54 +0200

Regina gravatar image

updated 2014-04-16 19:04:04 +0200

You can do it using a BASIC macro of type function.

Start with , or for references to tutorials see also

Please consider bug .

If you call a function with a cell range, this parameter is interpreted as array in BASIC. It is an array of rows, and each row is an array itself. When you assign an array to the function name you can fill a cell range, if you use the function in matrix context by entering it with Ctrl+Shift+Enter. That is the same as with the build-in matrix functions. Beyond that you need to get familiar with the API.

Example, call it e.g as array function {=MYCOLSUM(A1:C2)}, output is a row

function MYCOLSUM (byval matrixIn as variant) as variant
rem causion, BASIC arrays are zero-based
rem cell range arrays are one-based
dim iRow as integer
dim iCol as integer
dim nColCount as integer: nColCount = UBound(matrixIn,2)
dim nRowCount as integer: nRowCount = UBound(matrixIn,1)
dim aColSum(nColCount-1)
dim fSum as double
for iCol = 1 to nColCount
    fSum = 0.0
    for iRow=1 to nRowCount
        fSum = fSum+matrixIn(iRow,iCol)
    next iRow
    aColSum(iCol-1) = fSum
next iCol
end function
edit flag offensive delete link more


That it is! Thanks for your prompt answer. As simple as you said, just assign an array to the function name as the return.

Thanks for pointing this active bug although I was aware some of it because it was mentioned in the document 0300CG3-CalcGuideLibO3.pdf. In the details the described impact there was slightly different that calc does not recalculate cells unless you edit them. Your pointed error proved what I was suspecting it is impossible to build independent library of Calc functions.

Mich gravatar imageMich ( 2014-04-16 19:02:00 +0200 )edit

Unfortunetely this my first post and I can not upvote your answer due to lacking in points.

Mich gravatar imageMich ( 2014-04-16 19:07:29 +0200 )edit

Question Tools

1 follower


Asked: 2014-04-15 20:37:14 +0200

Seen: 913 times

Last updated: Apr 16 '14