I work in LibreOffice 4.0.6.2. 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.?
Do you want to write a macro? In BASIC? Or do you want to make your own build with a function in core?
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.
You can do it using a BASIC macro of type function.
Start with https://wiki.documentfoundation.org/Macros, or for references to tutorials see also Is there a manual specifically about programming using LibreOffice Basic (inside Calc)?
Please consider bug https://issues.apache.org/ooo/show_bug.cgi?id=53615.
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
MYCOLSUM = aColSum
end function
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.
Unfortunetely this my first post and I can not upvote your answer due to lacking in points.